Привет! На связи Merion Academy - платформа доступного IT образования.
Соединение (JOIN) — одна из самых важных операций, выполняемых реляционными системами управления базами данных. В этой статье мы рассмотрим разные типы соединений: перекрестное соединение (Cross Join), полное внешнее соединение (Full Outer Join), внутреннее соединение (Inner Join), левое (Left Join) и правое соединение (Right Join).
Что такое соединение?
Соединение - это операция, которая объединяет две строки в одну.
Обычно эти строки берутся из двух разных таблиц, но это не обязательно так.
Прежде чем мы рассмотрим, как написать саму операцию объединения, давайте посмотрим, как будет выглядеть результат объединения.
Возьмем, к примеру, систему, хранящую информацию о пользователях и их адресах.
Строки из таблицы, хранящей информацию о пользователях, могут выглядеть следующим образом:
Строки из таблицы, хранящей адресную информацию, могут выглядеть следующим образом:
Мы могли бы написать отдельные запросы для получения информации о пользователях и адресах, но в идеале мы могли бы написать один запрос и получить всех пользователей и их адреса в одном наборе результатов.
Именно это и позволяет сделать объединение!
Мы скоро рассмотрим, как писать такие соединения, но если бы мы соединили информацию о пользователях с информацией об адресах, то получили бы такой результат:
Здесь мы видим всех наших пользователей и их адреса в одном красивом наборе результатов.
Помимо получения объединенного набора результатов, еще одно важное применение объединений - это добавление в запрос дополнительной информации, которую можно отфильтровать.
Например, если бы мы хотели отправить физическую почту всем пользователям, живущим в Оклахома-Сити, мы могли бы использовать этот объединенный набор результатов и отфильтровать его на основе столбца «Город».
Теперь, когда мы знаем назначение соединений, давайте приступим к их написанию!
Настройка базы данных
Прежде чем писать запросы, необходимо настроить базу данных.
Для этих примеров мы будем использовать PostgreSQL, но запросы и концепции, показанные здесь, можно легко перенести на любую другую современную систему баз данных (например, MySQL, SQL Server и т. д.).
Для работы с базой данных PostgreSQL мы можем использовать psql - интерактивную программу командной строки PostgreSQL. Если у вас есть другой клиент базы данных, с которым вам нравится работать, это тоже хорошо.
Для начала давайте создадим нашу базу данных. Если PostgreSQL уже установлен, мы можем выполнить команду createdb в терминале, чтобы создать новую базу данных. Я назвал свою базу данных fcc:
Далее запустим интерактивную консоль командой psql и подключимся к базе данных, которую мы только что создали, используя \c :
Примечание: я очистил вывод psql в этих примерах, чтобы его было легче читать, поэтому не беспокойтесь, если вывод, показанный здесь, не совсем такой, как в вашем терминале.
Я рекомендую вам последовать за этими примерами и выполнить эти запросы самостоятельно. Вы узнаете и запомните гораздо больше, если будете работать с этими примерами, а не просто читать их.
Теперь перейдем к соединениям!
CROSS JOIN — перекрестное соединение
Самый простой вид соединения — это CROSS JOIN или «декартово произведение».
Это соединение берет каждую строку из одной таблицы и соединяет ее с каждой строкой другой таблицы.
Если бы у нас было два списка, один из которых содержал 1, 2, 3, а другой - A, B, C, то декартово произведение этих двух списков было бы таким:
Каждое значение из первого списка сопоставляется с каждым значением из второго списка.
Давайте запишем этот же пример в виде SQL-запроса.
Сначала создадим две очень простые таблицы и вставим в них некоторые данные:
Наши две таблицы, буквы и цифры, имеют только один столбец: простое текстовое поле.
Теперь давайте соединим их вместе с помощью CROSS JOIN:
Это самый простой тип соединения, но даже в этом простом примере мы видим, что соединение работает: две отдельные строки (одна из букв, другая из цифр) были объединены в одну строку.
Хотя этот тип соединения часто обсуждается как просто академический пример, у него есть, по крайней мере, один хороший вариант использования: покрытие диапазонов дат.
CROSS JOIN с диапазонами дат
Один из хороших вариантов использования CROSS JOIN - взять каждую строку из таблицы и применить ее к каждому дню в диапазоне дат.
Допустим, вы создаете приложение, которое отслеживает ежедневные задачи - например, чистку зубов, завтрак или принятие душа.
Если бы вам нужно было создать запись для каждой задачи и для каждого дня прошедшей недели, вы могли бы использовать CROSS JOIN в диапазоне дат.
Чтобы создать этот диапазон дат, мы можем использовать функцию [generate_series](https://www.postgresql.org/docs/current/functions-srf.html):
Функция generate_series принимает три параметра.
Первый параметр - это начальное значение. В этом примере мы используем CURRENT_DATE - INTERVAL '5 day'. Это возвращает текущую дату минус пять дней - или «пять дней назад».
Второй параметр - текущая дата (CURRENT_DATE).
Третий параметр - это «интервал шага», то есть то, на сколько мы хотим увеличивать значение каждый раз. Поскольку это ежедневные задачи, мы будем использовать интервал в один день (INTERVAL '1 day').
Если сложить все вместе, то получится серия дат, начинающаяся пять дней назад, заканчивающаяся сегодня и увеличивающаяся на один день за раз.
Наконец, мы удаляем временную часть, приводя выходные значения к дате с помощью ::DATE, и присваиваем этому столбцу псевдоним AS day, чтобы сделать вывод немного красивее.
Результатом этого запроса являются последние пять дней плюс сегодняшний:
Возвращаясь к примеру с задачами на день, давайте создадим простую таблицу для хранения задач, которые мы хотим выполнить, и вставим в нее несколько задач:
В нашей таблице задач есть только один столбец, имя, и мы вставили в эту таблицу четыре задачи.
Теперь давайте сделаем CROSS JOIN наших задач с запросом, чтобы сгенерировать даты:
(Поскольку наш запрос на генерацию даты не является таблицей, мы просто записываем его в виде подзапроса).
Из этого запроса мы возвращаем название задачи и день, а набор результатов выглядит следующим образом:
Как мы и ожидали, мы получим строку для каждой задачи за каждый день в нашем диапазоне дат.
CROSS JOIN — это простейшее соединение, но для рассмотрения следующих нескольких типов нам понадобится более реалистичная настройка таблицы.
Создание режиссеров и фильмов
Чтобы проиллюстрировать следующие типы соединений, мы воспользуемся примером фильмов и режиссеров.
В этой ситуации у фильма есть один режиссер, но фильм не обязан иметь режиссера - представьте, что анонсирован новый фильм, но выбор режиссера еще не подтвержден.
В нашей таблице directors будет храниться имя каждого режиссера, а в таблице movies - название фильма и ссылка на режиссера фильма (если он есть).
Давайте создадим эти две таблицы и вставим в них данные:
У нас есть пять режиссеров, пять фильмов, и у трех из этих фильмов есть назначенные режиссеры. У режиссера ID 1 есть два фильма, а у режиссера ID 2 - один.
FULL OUTER JOIN — полное внешнее соединение
Теперь, когда у нас есть данные для работы, давайте рассмотрим полное внешнее соединение (FULL OUTER JOIN).
FULL OUTER JOIN имеет некоторые сходства с CROSS JOIN, но у него есть и пара ключевых отличий.
Первое отличие заключается в том, что для FULL OUTER JOIN требуется условие присоединения.
Условие соединения определяет, как строки двух таблиц связаны друг с другом и по каким критериям они должны быть объединены.
В нашем примере таблица «Фильмы» имеет ссылку на режиссера через столбец director_id, и этот столбец совпадает со столбцом id таблицы «Режиссеры». Именно эти два столбца мы будем использовать в качестве условия объединения.
Вот как мы напишем это соединение между нашими двумя таблицами:
Обратите внимание на указанное нами условие объединения, которое сопоставляет фильм с его режиссером: ON movies.director_id = directors.id.
Наш набор результатов выглядит как нечетное декартово произведение:
Первыми мы видим строки, в которых у фильма есть режиссер, и наше условие объединения оценивается как true.
Однако после этих строк мы видим все оставшиеся строки из каждой таблицы - но с NULL-значениями, поскольку в другой таблице не было совпадений.
Примечание: если вы не знакомы со значениями NULL, посмотрите мое объяснение в этом учебнике по операторам SQL.
Здесь мы также видим еще одно различие между CROSS JOIN и FULL OUTER JOIN. FULL OUTER JOIN возвращает одну отдельную строку из каждой таблицы - в отличие от CROSS JOIN, который имеет несколько строк.
INNER JOIN — внутреннее соединение
Следующий тип соединения, INNER JOIN, является одним из наиболее часто используемых типов соединения.
Внутреннее соединение возвращает только те строки, для которых условие соединения истинно.
В нашем примере внутреннее соединение между таблицами «Фильмы» и «Режиссеры» вернет только те записи, в которых фильму назначен режиссер.
Синтаксис в основном такой же, как и раньше:
Наш результат показывает три фильма, у которых есть режиссер:
Поскольку внутреннее соединение включает только строки, соответствующие условию соединения, порядок двух таблиц в соединении не имеет значения.
Если мы изменим порядок таблиц в запросе, то получим тот же результат:
Поскольку в этом запросе мы сначала перечислили таблицу «Режиссеры» и выбрали все столбцы (SELECT *), мы видим сначала данные столбца «Режиссеры», а затем столбцы «Фильмы», но результирующие данные одинаковы.
Это полезное свойство внутренних объединений, но оно верно не для всех типов объединений - например, для следующего типа.
LEFT JOIN / RIGHT JOIN
Следующие два типа соединений используют модификатор (LEFT или RIGHT), который влияет на то, данные какой таблицы будут включены в результирующий набор.
Примечание: LEFT JOIN и RIGHT JOIN также могут называться LEFT OUTER JOIN и RIGHT OUTER JOIN.
Эти соединения используются в запросах, где мы хотим вернуть все данные определенной таблицы и, если она существует, данные связанной таблицы.
Если связанной таблицы не существует, мы все равно получим все данные «основной» таблицы.
Это запрос информации о конкретной вещи и бонусной информации, если такая информация существует.
Это легко понять на примере. Давайте найдем все фильмы и их режиссеров, но нам неважно, есть у них режиссер или нет - это бонус:
Запрос выполняется по той же схеме, что и раньше - мы только указали объединение как LEFT JOIN.
В этом примере «левой» таблицей является таблица «Фильмы».
Если мы напишем запрос в одну строку, это будет немного проще увидеть:
Левое соединение возвращает все записи из «левой» таблицы.
Левое соединение возвращает все строки из «правой» таблицы, которые соответствуют условию соединения.
Строки из «правой» таблицы, не соответствующие условию соединения, возвращаются как NULL.
Глядя на этот набор результатов, мы можем понять, почему этот тип объединения полезен для запросов типа «все это и, если оно существует, кое-что из этого».
RIGHT JOIN работает точно так же, как LEFT JOIN, за исключением того, что правила работы с двумя таблицами меняются местами.
В правом соединении возвращаются все строки из «правой» таблицы. Данные из «левой» таблицы возвращаются условно, на основании условия соединения.
Давайте используем тот же запрос, что и выше, но заменим LEFT JOIN на RIGHT JOIN:
Теперь наш набор результатов возвращает каждую строку директора и, если она существует, данные о фильмах.
LEFT JOIN / RIGHT JOIN в производственных приложениях
В производственных приложениях я всегда использую только LEFT JOIN и никогда не использую RIGHT JOIN.
Я делаю это потому, что, по моему мнению, LEFT JOIN делает запрос более легким для чтения и понимания.
Когда я пишу запросы, мне нравится думать о том, чтобы начинать с «базового» набора результатов, скажем, всех фильмов, а затем добавлять (или вычитать) группы вещей из этой базы.
Поскольку мне нравится начинать с базы, LEFT JOIN подходит для этой линии мышления. Мне нужны все строки из моей базовой таблицы («левая» таблица), и я условно хочу получить строки из «правой» таблицы.
На практике я не думаю, что когда-либо видел RIGHT JOIN в производственном приложении. Нет ничего плохого в RIGHT JOIN - я просто думаю, что это делает запрос более сложным для понимания.
Переписывание RIGHT JOIN
Если бы мы хотели перевернуть наш сценарий выше и вместо этого вернуть всех режиссеров и, условно, их фильмы, мы можем легко переписать RIGHT JOIN в LEFT JOIN.
Все, что нам нужно сделать, — это изменить порядок таблиц в запросе и заменить RIGHT на LEFT:
Примечание: мне нравится ставить таблицу, к которой происходит присоединение («правая» таблица - в примере с фильмами), первой в условии присоединения (ON movies.director_id = ...) - но это только мои личные предпочтения.
Фильтрация с помощью LEFT JOIN
Существует два варианта использования LEFT JOIN (или RIGHT JOIN).
Первый случай мы уже рассмотрели: возврат всех строк из одной таблицы и условно из другой.
Второй вариант использования - возврат строк из первой таблицы, когда данные из второй таблицы отсутствуют.
Сценарий будет выглядеть следующим образом: найти режиссеров, которые не принадлежат к фильму.
Для этого мы начнем с LEFT JOIN, и наша таблица режиссеров будет первичной или «левой» таблицей:
Для режиссера, не относящегося к фильму, столбцы таблицы «Фильмы» имеют значение NULL:
В нашем примере идентификаторы режиссера 3, 4 и 5 не принадлежат ни одному фильму.
Чтобы отфильтровать наш набор результатов только по этим строкам, мы можем добавить предложение WHERE, чтобы возвращать только строки, в которых данные о фильме равны NULL:
И вот наши три режиссера без фильмов!
Обычно для фильтрации используется столбец id таблицы (WHERE movies.id IS NULL), но все столбцы таблицы movies имеют NULL, поэтому подойдет любой из них.
(Поскольку мы знаем, что все столбцы из таблицы movies будут NULL, в приведенном выше запросе мы могли бы просто написать SELECT directors.* вместо SELECT *, чтобы просто вернуть всю информацию о режиссерах).
Использование LEFT JOIN для поиска совпадений
В нашем предыдущем запросе мы нашли режиссеров, которые не принадлежали фильмам.
Используя ту же структуру, мы можем найти режиссеров, которые действительно принадлежат фильмам, изменив условие WHERE, чтобы искать строки, в которых данные о фильме не являются NULL:
Это может показаться удобным, но на самом деле мы просто повторно реализовали INNER JOIN!
Множественные соединения
Мы уже видели, как объединить две таблицы, но как насчет нескольких объединений подряд?
На самом деле это довольно просто, но чтобы проиллюстрировать это, нам понадобится третья таблица: билеты.
В этой таблице будут представлены билеты, проданные на фильм:
В таблице билетов есть только идентификатор и ссылка на фильм: movie_id.
Мы также вставили два билета, проданных на фильм ID 1, и один билет, проданный на фильм ID 3.
Теперь давайте соединим режиссеров с фильмами, а затем фильмы с билетами!
Поскольку это внутренние соединения, порядок, в котором мы записываем соединения, не имеет значения. Мы могли бы начать с билетов, затем присоединиться к фильмам, а потом присоединиться к режиссерам.
Все снова сводится к тому, что вы пытаетесь запросить и что делает запрос наиболее понятным.
В нашем наборе результатов можно заметить, что мы еще больше сузили возвращаемые строки:
Это имеет смысл, потому что мы добавили еще один INNER JOIN. По сути, это добавляет еще одно условие «И» к нашему запросу.
Наш запрос, по сути, говорит: «Вернуть всех режиссеров, которые принадлежат к фильмам, на которые также есть продажи билетов».
Если бы вместо этого мы хотели найти режиссеров, которые принадлежат к фильмам, на которые еще не проданы билеты, мы могли бы заменить наш последний INNER JOIN на LEFT JOIN:
Мы видим, что фильм №2 снова появился в наборе результатов:
У этого фильма не было продаж билетов, поэтому он был ранее исключен из набора результатов благодаря INNER JOIN.
Я оставлю это упражнение для читателей, но как найти режиссеров, которые принадлежат к фильмам, на которые не было продано ни одного билета?
Порядок выполнения соединения
В конце концов, нам неважно, в каком порядке выполняются соединения.
Одно из ключевых отличий SQL от других современных языков программирования заключается в том, что SQL - это декларативный язык.
Это означает, что мы указываем желаемый результат, но не указываем детали выполнения - эти детали остаются на усмотрение планировщика запросов к базе данных. Мы указываем нужные нам соединения и условия для них, а планировщик запросов делает все остальное.
Но в реальности база данных не будет соединять три таблицы одновременно. Вместо этого она, скорее всего, объединит первые две таблицы в один промежуточный результат, а затем присоединит этот промежуточный набор результатов к третьей таблице.
(Примечание: это несколько упрощенное объяснение).
Поэтому, работая с множественными соединениями в запросах, мы можем рассматривать их как серию соединений между двумя таблицами, хотя одна из этих таблиц может быть довольно большой.
Объединения с дополнительными условиями
Последняя тема, которую мы рассмотрим, — это объединение с дополнительными условиями.
Подобно предложению WHERE, мы можем добавить столько условий, сколько захотим.
Например, если мы хотим найти фильмы с режиссерами, которых не зовут «Джон Смит», мы можем добавить это дополнительное условие к нашему соединению с помощью AND:
Мы можем использовать любые операторы, которые мы бы поместили в предложение WHERE в этом условии присоединения.
Мы также получим тот же результат, если поместим условие в предложение WHERE:
Здесь есть несколько тонких различий, но для целей этой статьи набор результатов одинаков.
(Если вы не знакомы со всеми способами фильтрации SQL-запросов, ознакомьтесь с ранее упомянутой статьей здесь).
Реальность написания запросов с соединениями
На самом деле я использую соединения только в трех различных вариантах:
INNER JOIN
Первый вариант использования - это записи, в которых существует связь между двумя таблицами. Для этого используется соединение INNER JOIN.
Это такие ситуации, как поиск «фильмов, у которых есть режиссеры» или «пользователей с постами».
LEFT JOIN
Второй вариант использования - это записи из одной таблицы и, если существует связь, записи из второй таблицы. Для этого используется LEFT JOIN.
Это такие ситуации, как «фильмы с режиссерами, если у них есть режиссер» или «пользователи с постами, если у них есть посты».
Исключение LEFT JOIN
Третий наиболее распространенный случай использования - это наш второй случай использования LEFT JOIN: поиск записей в одной таблице, которые не имеют отношения ко второй таблице.
Это такие ситуации, как «фильмы без режиссеров» или «пользователи без сообщений».
Два самых полезных типа объединений
Не думаю, что я когда-либо использовал FULL OUTER JOIN или RIGHT JOIN в производственном приложении. Просто такие случаи не так часто встречаются, или запрос можно написать более понятным способом (в случае RIGHT JOIN).
Итак, хорошие новости! На самом деле существует только два типа объединений, которые необходимо понимать для 99,9% случаев использования: INNER JOIN и LEFT JOIN!