Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как LEFT JOIN работает в SQL и как мы можем использовать его для связывания строк, принадлежащих разным таблицам, и построения составных наборов результатов.
В отличие от СОЕДИНЕНИЯ или ВНУТРЕННЕГО СОЕДИНЕНИЯ , ЛЕВОЕ СОЕДИНЕНИЕ является ВНЕШНИМ СОЕДИНЕНИЕМ. Таким образом, ЛЕВОЕ СОЕДИНЕНИЕ и ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ эквивалентны. Другие типы ВНЕШНИХ СОЕДИНЕНИЙ-это ПРАВИЛЬНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ и ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Однако, поскольку ключевое слово SQL OUTER является излишним, его обычно опускают.
Таблицы базы данных
Чтобы продемонстрировать, как работает LEFT JOIN, мы собираемся использовать следующие две таблицы post и post_comment , которые образуют связь один ко многим таблицам через post_id Столбец внешнего ключа в post_comment таблице, которая ссылается на идентификатор столбец первичного ключа в post таблице:
Таблица post содержит следующие 3 строки:
| id | title | |----|-----------| | 1 | Java | | 2 | Hibernate | | 3 | JPA |
и в таблице post_comment есть следующие 3 записи:
| id | review | post_id | |----|-----------|---------| | 1 | Good | 1 | | 2 | Excellent | 1 | | 3 | Awesome | 2 |
Итак, первая запись в таблице post имеет две связанные дочерние записи в post_comment . Вторая запись в таблице post имеет одну дочернюю запись в post_comment , в то время как третья запись в таблице post не имеет связанного дочернего элемента.
SQL-СОЕДИНЕНИЕ СЛЕВА
В SQL предложение JOIN позволяет нам связывать строки, принадлежащие разным таблицам. Например, ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ создает декартово произведение, содержащее все возможные комбинации строк между двумя соединяющимися таблицами.
Даже если ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ полезно в определенных ситуациях, мы обычно хотим связать таблицы на основе определенного условия. Точно так же , как JOIN или ВНУТРЕННЕЕ СОЕДИНЕНИЕ, LEFT JOIN предоставляет предложение ON, которое определяет, как должны быть связаны две таблицы.
Например, если мы выполним следующий SQL-запрос НА ЛЕВОЕ СОЕДИНЕНИЕ:
SELECT p.id AS post_id, p.title AS post_title, pc.review AS review FROM post p LEFT JOIN post_comment pc ON pc.post_id = p.id ORDER BY p.id, pc.id
Мы получаем следующий набор результатов:
| post_id | post_title | review | |---------|------------|-----------| | 1 | Java | Good | | 1 | Java | Excellent | | 2 | Hibernate | Awesome | | 3 | JPA | |
Предложение LEFT JOIN принимает все строки с левой стороны условия СОЕДИНЕНИЯ (например, post таблица в нашем случае) и пытается найти любую соответствующую запись с правой стороны условия СОЕДИНЕНИЯ.
Первая строка таблицы post содержит две связанные строки post_comment , поэтому предложение LEFT JOIN объединит первую запись post с двумя связанными дочерними строками post_comment , как показано выделенными записями в наборе результатов запроса:
| post_id | post_title | review | |---------|------------|-----------| | 1 | Java | Good | | 1 | Java | Excellent | | 2 | Hibernate | Awesome | | 3 | JPA | |
Вторая строка таблицы post содержит только одну связанную строку post_comment , поэтому предложение LEFT JOIN объединит вторую запись post со связанной дочерней строкой post_comment , как показано выделенной записью в наборе результатов запроса:
| post_id | post_title | review | |---------|------------|-----------| | 1 | Java | Good | | 1 | Java | Excellent | | 2 | Hibernate | Awesome | | 3 | JPA | |
Третья строка таблицы post не имеет связанной строки post_comment , поэтому предложение LEFT JOIN объединит третью запись post с виртуальной строкой , в которой все столбцы post_comment являются ПУСТЫМИ , как показано выделенной записью в наборе результатов запроса:
| post_id | post_title | review | |---------|------------|-----------| | 1 | Java | Good | | 1 | Java | Excellent | | 2 | Hibernate | Awesome | | 3 | JPA | |
SQL АНТИ-СОЕДИНЕНИЕ
В реляционной алгебре ПОЛУСОЕДИНЕНИЕ (⋉) между двумя отношениями, L и R, определяется как множество всех кортежей в L, для которых в R есть кортеж, равный на основе общих атрибутов двух отношений.
И АНТИСОЕДИНЕНИЕ между двумя отношениями, L и R, определяется следующим образом:
L ▷ − (L ⋉ R)
Правильный способ реализации ПОЛУСОЕДИНЕНИЯ-через предложения EXISTS или IN, в то время как АНТИСОЕДИНЕНИЕ может быть реализовано с использованием НЕ СУЩЕСТВУЕТ или НЕ В.
Итак, чтобы получить все строки post , с которыми не связана запись post_comment , мы можем использовать следующий SQL-запрос:
SELECT p.id AS post_id, p.title AS post_title FROM post p WHERE NOT EXISTS ( SELECT 1 FROM post_comment WHERE post_id = p.id ) ORDER BY p.id
который отображает ожидаемый набор результатов:
| post_id | post_title | |---------|------------| | 3 | JPA |
Для получения более подробной информации о положениях “СУЩЕСТВУЕТ” и “НЕ СУЩЕСТВУЕТ” ознакомьтесь с этой статьей .
Тем не менее, есть много разработчиков, которые пытаются эмулировать оператор ANTI JOIN с помощью LEFT JOIN, например:
SELECT p.id AS post_id, p.title AS post_title FROM post p LEFT JOIN post_comment pc ON pc.post_id = p.id WHERE pc.id IS NULL ORDER BY p.id
Однако, даже если два запроса генерируют один и тот же набор результатов, в зависимости от компонента database engine, альтернатива ЛЕВОМУ СОЕДИНЕНИЮ может быть менее эффективной, чем запрос “НЕ СУЩЕСТВУЕТ”.
ВНУТРЕННЕЕ СОЕДИНЕНИЕ строк, ВНЕШНЕЕ СОЕДИНЕНИЕ полезно, когда проекция запроса строится из столбцов, принадлежащих обеим соединяемым таблицам. С другой стороны, ПОЛУСОЕДИНЕНИЕ и АНТИСОЕДИНЕНИЕ возвращают только левую таблицу, поэтому нет необходимости объединять записи левой и правой таблиц.
Вывод
ЛЕВОЕ СОЕДИНЕНИЕ позволяет нам извлекать записи из левой боковой таблицы, даже если в правой боковой таблице нет связанной записи, соответствующей условию соединения.
Однако, когда есть совпадение между левой и правой таблицами, СОЕДИНЕНИЕ СЛЕВА позволяет нам построить составную проекцию, включающую столбцы из двух соединяющихся таблиц.