Перевод второй части статьи «SQL Joins Tutorial: Cross Join, Full Outer Join, Inner Join, Left Join, and Right Join».
Продолжаем разбираться с видами JOIN в SQL.
В первой части статьи мы рассмотрели:
- что такое JOIN?
- настройку базы данных
- CROSS JOIN
- настройку наших экспериментальных данных
- FULL OUTER JOIN
- INNER JOIN
В этой части:
- LEFT JOIN / RIGHT JOIN
- Фильтрация с использованием LEFT JOIN
- Множественные объединения
- Операции объединения с дополнительными условиями
- Правда жизни о написании запросов с объединениями.
LEFT JOIN / RIGHT JOIN (левое и правое объединение)
Следующие два вида объединений используют модификатор (LEFT или RIGHT), который определяет, какие данные таблицы включаются в результирующий набор.
Примечание: LEFT JOIN и RIGHT JOIN также называются LEFT OUTER JOIN и RIGHT OUTER JOIN (соответственно левое и правое внешнее объединение).
Эти виды объединений используются в запросах, когда мы хотим вернуть все данные из одной таблицы и добавить к ним связанные данные из другой таблицы (если таковые есть).
Если связанных данных во второй таблице нет, мы получаем в выводе все данные только из «первичной» таблицы.
То есть, это запрос информации о конкретной вещи с дополнительными сведениями, если они есть.
Понять это будет проще на примере. Давайте найдем все фильмы с их режиссерами, но не будем зацикливаться на последних: если режиссер есть, это дополнительная информация, а нет — так нет.
SELECT * FROM movies LEFT JOIN directors ON directors.id = movies.director_id;
Этот запрос практически идентичен тому, что мы писали ранее. Единственная разница в том, что мы указали тип объединения LEFT JOIN.
В нашем примере таблица movies является «левой».
Если написать запрос в одну строчку, «левизну» будет проще заметить:
... FROM movies LEFT JOIN directors ...
LEFT JOIN возвращает все записи из «левой» таблицы. Из «правой» возвращаются только те, что соответствуют условию объединения. Строки «правой» таблицы, не соответствующие условию объединения, возвращаются как NULL.
id | name | director_id | id | name
----+---------+-------------+------+------------
1 | Movie 1 | 1 | 1 | John Smith
2 | Movie 2 | 1 | 1 | John Smith
3 | Movie 3 | 2 | 2 | Jane Doe
4 | Movie 4 | NULL | NULL | NULL
5 | Movie 5 | NULL | NULL | NULL
(5 rows)На этом примере мы видим, что запрос LEFT JOIN полезен для случаев, когда нам нужно получить «все из этого и, если есть, что-нибудь из того».
RIGHT JOIN
Правое объединение работает точно так же, как и левое, за исключением того, что таблицы как бы меняются местами.
При правом объединении возвращаются все строки «правой» таблицы. А строки «левой» добавляются, только если отвечают заданному условию.
Давайте используем предыдущий запрос, в котором заменим LEFT JOIN на RIGHT JOIN:
SELECT * FROM movies RIGHT JOIN directors ON directors.id = movies.director_id;
id | name | director_id | id | name
------+---------+-------------+----+--------------
1 | Movie 1 | 1 | 1 | John Smith
2 | Movie 2 | 1 | 1 | John Smith
3 | Movie 3 | 2 | 2 | Jane Doe
NULL | NULL | NULL | 5 | Bree Jensen
NULL | NULL | NULL | 4 | Bev Scott
NULL | NULL | NULL | 3 | Xavier Wills
(6 rows)В результирующем наборе мы видим строки со всеми режиссерами, к которым добавлены строки с их фильмами (если есть).
Все, что мы сделали, это изменили то, какая таблица считается «первичной». Именно из нее мы получаем все данные независимо от того, есть ли для них дополнительные данные в другой таблице.
LEFT JOIN / RIGHT JOIN в реальной жизни
В приложениях, поступающих в производство, я всегда использую только LEFT JOIN и никогда RIGHT JOIN.
Я так делаю, потому что с LEFT JOIN запрос легче читать и проще понять.
Составляя запросы, я предпочитаю начинать с «базового» набора результатов, в нашем примере это все фильмы, а затем добавлять что-то в этот набор (или убирать из него).
Для такого подхода лучше при менять LEFT JOIN. Я хочу получать все строки из «левой» таблицы, а из правой — только соответствующие условиям.
На практике я, кажется, ни разу не видел использование RIGHT JOIN в приложении, идущем в производство. В правом объединении нет ничего плохого, просто лично я считаю, что такой запрос хуже читается.
Переписывание RIGHT JOIN
Если мы хотим «перевернуть» наш сценарий и вернуть строки со всеми режиссерами, добавив к ним фильмы (если есть), мы с легкостью можем превратить RIGHT JOIN в LEFT JOIN.
Все что нужно сделать — изменить порядок таблиц в запросе и поменять RIGHT на LEFT:
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id;
Примечание: я предпочитаю в условии сначала указывать таблицу, данные из которой являются добавочными («правую» таблицу, в примере — movies) — (ON movies.director_id = …), но это мои личные предпочтения.
Фильтрация с использованием LEFT JOIN
LEFT JOIN (или RIGHT JOIN) имеют два сценария использования.
Первый мы уже рассмотрели: вывод всех строк из одной таблицы и вывод некоторых (соответствующих условию) из второй.
Второй сценарий использования — вывод тех строк из первой таблицы, для которых во второй таблице нет соответствий.
Если говорить о нашем примере, то этот сценарий звучит так: «найти всех режиссеров, которые не связаны с имеющимися фильмами».
Чтобы это сделать, мы начинаем с LEFT JOIN, а наша таблица directors будет первичной («левой»):
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id;
Для режиссеров, не имеющих фильмов, столбцы из таблицы movies будут заполнены значениями NULL:
id | name | id | name | director_id
----+--------------+------+---------+-------------
1 | John Smith | 1 | Movie 1 | 1
1 | John Smith | 2 | Movie 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2
5 | Bree Jensen | NULL | NULL | NULL
4 | Bev Scott | NULL | NULL | NULL
3 | Xavier Wills | NULL | NULL | NULL
(6 rows)В нашем примере режиссеры с ID 3, 4 и 5 не имеют фильмов.
Чтобы отфильтровать результаты и получить только эти строки, мы можем добавить оборот WHERE. Благодаря ему будут возвращены только те строки, где данные о фильмах — NULL:
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id WHERE movies.id IS NULL;
id | name | id | name | director_id
----+--------------+------+------+-------------
5 | Bree Jensen | NULL | NULL | NULL
4 | Bev Scott | NULL | NULL | NULL
3 | Xavier Wills | NULL | NULL | NULL
(3 rows)А это как раз три наших режиссера без фильмов!
Для фильтрации часто используется именно столбец id (WHERE movies.id IS NULL), но на самом деле в нашем случае все столбцы из таблицы movies имеют значение NULL, так что в обороте WHERE мог быть указан любой из них.
(Поскольку мы знаем, что все столбцы из таблицы movies будут NULL, то чтобы вернуть всю информацию о режиссерах, в запросе мы могли бы написать SELECT directors.*, а не SELECT *).
Использование LEFT JOIN для поиска соответствий
В нашем предыдущем примере мы искали режиссеров, у которых нет фильмов.
Используя ту же структуру, мы могли бы найти режиссеров, у которых фильмы есть. Для этого нужно изменить условие WHERE и искать строки, где данные о фильмах не NULL:
SELECT * FROM directors LEFT JOIN movies ON movies.director_id = directors.id WHERE movies.id IS NOT NULL;
id | name | id | name | director_id
----+------------+----+---------+-------------
1 | John Smith | 1 | Movie 1 | 1
1 | John Smith | 2 | Movie 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2
(3 rows)Это может казаться удобным, но по факту мы всего лишь заново реализовали INNER JOIN!
Множественные объединения
Мы рассмотрели, как можно объединить строки двух таблиц, а как насчет множественных объединений в одной строке?
Собственно, тут все достаточно просто, но чтобы это проиллюстрировать, нам понадобится третья таблица: tickets (билеты).
Эта таблица будет представлять проданные билеты по каждому фильму:
CREATE TABLE tickets( id SERIAL PRIMARY KEY, movie_id INTEGER REFERENCES movies NOT NULL ); INSERT INTO tickets(movie_id) VALUES (1), (1), (3);
Таблица tickets содержит только id и ссылку на фильм: movie_id.
Мы также добавили сведения о том, что на фильм с ID 1 было продано два билета, а на фильм с ID 3 — один билет.
Теперь давайте объединим directors с movies, а movies — с tickets!
SELECT * FROM directors INNER JOIN movies ON movies.director_id = directors.id INNER JOIN tickets ON tickets.movie_id = movies.id;
Поскольку это внутренние объединения, порядок написания JOIN-ов не имеет значения. Мы могли бы начать с tickets, добавить movies, а потом добавить directors.
Опять же, все зависит от того, что именно вы хотите запросить. На основе этого вы и формируете свой запрос так, чтобы он был максимально понятным.
В нашем результирующем наборе мы увидим, что мы еще больше сузили поиск и получили меньше строк:
id | name | id | name | director_id | id | movie_id
----+------------+----+---------+-------------+----+----------
1 | John Smith | 1 | Movie 1 | 1 | 1 | 1
1 | John Smith | 1 | Movie 1 | 1 | 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2 | 3 | 3
(3 rows)Это логично, потому что мы добавили еще одно внутреннее объединение. В результате в запросе появился дополнительное условие «AND».
В нашем запросе говорится: «Верни всех режиссеров, связанных с фильмами, по которым были продажи билетов».
Если бы мы хотели найти режиссеров, у которых есть фильмы, но по этим фильмам не обязательно уже были проданы билеты, мы могли бы заменить наш последний INNER JOIN на LEFT JOIN:
SELECT * FROM directors JOIN movies ON movies.director_id = directors.id LEFT JOIN tickets ON tickets.movie_id = movies.id;
Мы видим, что теперь в результаты попал и Movie 2:
id | name | id | name | director_id | id | movie_id
----+------------+----+---------+-------------+------+----------
1 | John Smith | 1 | Movie 1 | 1 | 1 | 1
1 | John Smith | 1 | Movie 1 | 1 | 2 | 1
2 | Jane Doe | 3 | Movie 3 | 2 | 3 | 3
1 | John Smith | 2 | Movie 2 | 1 | NULL | NULL
(4 rows)По этому фильму ни одного билета не продано, поэтому в предыдущей выборке, когда применялся INNER JOIN, его не было.
Теперь вопрос: как нам найти режиссеров, связанных с фильмами, по которыми продаж не было?
Порядок выполнения объединений
Наконец, нам совершенно безразлично, в каком порядке выполняются операции объединения.
Одно из ключевых отличий SQL от современных языков программирования состоит в том, что SQL это декларативный язык.
Это означает, что мы определяем, какой итог мы хотим получить, но не определяем детали выполнения. Этими деталями занимается планировщик запросов базы данных. Мы указываем нужные нам объединения и их условия, а планировщик запросов занимается всем остальным.
Но на самом деле база данных не объединяет все три таблицы одновременно. Она скорее объединяет две первые таблицы в промежуточный результат, а затем объединяет этот промежуточный результат и третьей таблицей.
(Примечание: это несколько упрощенное объяснение).
Поэтому, работая с несколькими объединениями в запросах, мы можем представлять их в виде серии объединений двух таблиц (просто одна из этих таблиц может оказаться очень большой).
Операции объединения с дополнительными условиями
Последняя тема, которую мы рассмотрим, это объединение с дополнительными условиями.
Так же, как раньше мы добавляли оборот WHERE, мы можем добавлять сколько угодно условий.
Например, если мы хотим найти фильмы с режиссерами, которых зовут не John Smith, мы можем добавить дополнительное условие при помощи AND:
SELECT * FROM movies INNER JOIN directors ON directors.id = movies.director_id AND directors.name <> 'John Smith';
Мы можем использовать любые операторы, которые могли бы поставить в оборот WHERE.
Собственно, мы могли бы получить такой же результат, если бы поместили условия внутрь оборота WHERE:
SELECT * FROM movies INNER JOIN directors ON directors.id = movies.director_id WHERE directors.name <> 'John Smith';
Тут есть некоторая разница в происходящем под капотом, но для целей нашей статьи результат можем считать одинаковым.
Правда жизни о написании запросов с объединениями
В реальной жизни я сталкивался с операциями объединения в трех разных видах:
INNER JOIN
Первый случай использования — записи с существующими отношениями между таблицами. Здесь применяется INNER JOIN.
Это ситуации вроде поиска «фильмов, для которых указаны режиссеры» или «пользователей, у которых есть посты».
LEFT JOIN
Второй случай использования — получение записей из одной таблицы и, если отношения с записями в другой таблице существуют, — записей из второй таблицы. Здесь применяется LEFT JOIN.
Это ситуации вроде «вывести фильмы и имена режиссеров, если для этих фильмов режиссеры указаны» или «вывести пользователей, а если у них есть посты, то и посты тоже вывести».
LEFT JOIN для исключения
Третий самый распространенный случай использования — это еще один случай для использования LEFT JOIN: поиск в первой таблице записей, для которых нет отношений во второй таблице.
Это ситуации вроде «фильмы без режиссеров» или «пользователи без постов».
Два очень полезных вида JOIN-ов
Мне кажется, я вообще ни разу не использовал FULL OUTER JOIN или RIGHT JOIN в приложениях, идущих в продакшен. Либо подходящие случаи встречаются редко, либо такой запрос можно написать более понятно (это касается, в частности, RIGHT JOIN).
Иногда мне случается использовать CROSS JOIN для таких вещей как распределение записей по диапазону дат (мы рассматривали пример в первой части статьи), но этот сценарий возникает не слишком часто.
В общем, хорошие новости! На самом деле есть только два вида JOIN-ов, в которых нужно разобраться и которые покрывают 99,9% use cases: INNER JOIN и LEFT JOIN!
[customscript]techrocks_custom_after_post_html[/customscript]
[customscript]techrocks_custom_script[/customscript]