Одна из первых и наиболее распространенных проблем, с которой сталкиваются новички в SQL, заключается в том, чем каждое JOIN отличается от другого. В этой статье я объясню разницу между внутренним и внешним JOIN и левым и правым JOIN в SQL, используя примеры каждого из них.
Введение в объединение таблиц
В SQL мы используем JOIN в наших запросах для создания наборов результатов, состоящих из нескольких таблиц. Например, при сборе адресной информации обычно используются таблицы countries и states, поэтому запись адреса может выглядеть следующим образом:
street_address city state_or_province country_id
123 Main St Houston Texas 1
558 Maple AveToronto Ontario 2
Здесь числа в столбце country_id таблицы addresses ссылаются на идентификаторы соответствующей страны в таблице countries, которые могут выглядеть следующим образом:
id country_name
1 Соединенные Штаты
2 Канада
Теперь, допустим, мы хотим написать запрос, который выводит полный адрес, что-то вроде 123 Main St, Houston, TX United States. Поскольку названия улиц, городов и штатов записаны в таблице addresses, а название страны записано в таблице countries, мы должны соединить эти две таблицы, чтобы получить нужный нам набор результатов. Соответствующий SQL, вероятно, будет выглядеть примерно так:
SELECT
a.street_address,
a.city,
a.state_or_province,
c.country_name
FROM
addresses a
JOIN
countries c
ON
a.country_id = c.id
Приведенный выше код SQL показывает нам примерно следующие результаты:
street_address city state_or_province country_name
123 Main St HoustonTexas Соединенные Штаты Америки
558 Maple Ave Торонто Онтарио Канада
Обратите внимание, что мы имеем значения из столбцов street_address, city и state_or_province таблицы addresses, а также значения из столбца country_name таблицы countries. Мы смогли соединить эти две таблицы вместе, сопоставив значение country_id в addresses со столбцом id в countries.
В SQL существует несколько видов join, каждый из которых ведет себя немного по-разному. Цель этой статьи — помочь вам понять разницу между различными видами объединений в SQL.
Загрузка и создание базы данных-образца
Если вы хотите следовать этой статье, перейдите к клонированию кода для создания базы данных примера. Чтобы создать базу данных в pgAdmin, сначала выполните команду в create_database.sql, затем убедитесь, что изменили соединения с только что созданной базой данных employee_database и выполните команды в build_and_seed_tables.sql.
Теперь у вас должна быть база данных с четырьмя таблицами: addresses, countries, departments, и employees. Давайте быстро обсудим модель данных, прежде чем перейдем к сути статьи.
Таблицы addresses и countries точно такие же, как в примерах выше; каждая запись адреса ссылается на запись страны. Затем у нас есть таблица employees, каждая запись которой ссылается на запись адреса, а также на запись отдела. Записи об отделах довольно просты; таблица departments просто содержит столбцы id и name и указывает, в каком отделе работает сотрудник.
Хорошо, теперь перейдем к фактическим соединениям!
Правые и левые соединения
Для начала давайте поговорим о разнице между левыми и правыми соединениями. Полезно представить себе все таблицы в SQL-запросе в виде горизонтального списка таблиц. Самая левая таблица — это таблица в предложении FROM, следующая таблица справа от самой левой таблицы — это таблица, к которой мы присоединяемся. Так, например, если мы напишем запрос
SELECT * FROM employees e JOIN addresses a ON e.address_id = a.id
Тогда таблица employees будет самой левой таблицей, а таблица addresses — следующей справа, примерно так:
Если мы сделаем еще один шаг и присоединим таблицу countries к addresses, то следующей справа будет таблица countries. Другими словами, следующий SQL-запрос:
SELECT *
FROM employees e
JOIN addresses a ON e.address_id = a.id
JOIN countries c ON a.country_id = c.id
может быть визуализирован следующим образом:
Заметьте еще раз, что всякий раз, когда таблица присоединяется к другой, уже существующая таблица (за неимением лучшего слова) является левой таблицей, а присоединяемая таблица — правой. Так, в нашем примере выше базовой таблицей является employees, поэтому она находится слева; затем мы присоединили к ней addresses, поэтому она находится справа от employees. Наконец, мы присоединили countries к addresses, поместив addresses справа от countries и, таким образом, в самую правую таблицу.
Эта визуализация помогает понять, как работают левые и правые соединения. Например, запросите все записи employees и LEFT JOIN таблицы addresses и посмотрите, что произойдет. Запрос:
SELECT e.first_name, a.street_address, a.city
FROM employees e
LEFT JOIN addresses a ON e.address_id = a.id
Результаты:
"Bob" "123 Main St" "Houston"
"Jordan" "220 C 30" "Izamal"
"Nicole" "3030 Burgos" "Xalapa"
"Erik" "1010 2nd St" "Omaha"
"Lina" "1010 2nd St" "Omaha"
"Sandra", NULL, NULL
Обратите внимание, что все записи для employees, самой левой таблицы, появляются как результаты в этом запросе, включая запись без связанной записи addresses. Записи в самой правой таблице addresses появляются только в том случае, если их можно присоединить к самой левой таблице. В addresses есть записи, которые нельзя присоединить к employees, потому что ни одна запись employees не имеет соответствующего address_id.
И наоборот, если мы используем RIGHT JOIN для присоединения к таблице addresses, мы получим результаты, включающие всю таблицу addresses — самую правую таблицу — несмотря на невозможность присоединения к таблице employees. Например, запрос:
SELECT e.first_name, a.street_address, a.city
FROM employees e
RIGHT JOIN addresses a ON e.address_id = a.id
Результаты:
"Bob" "123 Main St" "Houston"
"Jordan" "220 C 30" "Izamal"
"Nicole" "3030 Burgos" "Xalapa"
"Erik" "1010 2nd St" "Omaha"
"Lina" "1010 2nd St" "Omaha"
null "558 Maple Ave" "Toronto"
null "99 Jackson Rd" "Flin Flon"
null "821 Carol" "Nuuk"
Обратите внимание, что на этот раз в наборе результатов есть три записи addresses, которые имеют значения null в соответствующем столбце addresses. Также обратите внимание, что запись о сотруднике без связанной записи об адресе отсутствует в этом наборе результатов. Это потому, что, выполняя RIGHT JOIN, мы, по сути, говорим базе данных дать нам все результаты самой правой таблицы, и присоединить только записи самой левой таблицы, если они связаны с самой правой.
И наоборот, мы можем поменять местами правую и левую таблицу, обратившись к таблице addresses в предложении FROM, а затем присоединившись к таблице employees. Например, давайте SELECT from addresses и LEFT JOIN к таблице employees. Запрос:
SELECT e.first_name, a.street_address, a.city
FROM addresses a
LEFT JOIN employees e ON e.address_id = a.id
Результат:
"Bob" "123 Main St" "Houston"
"Jordan" "220 C 30" "Izamal"
"Nicole" "3030 Burgos" "Xalapa"
"Erik" "1010 2nd St" "Omaha"
"Lina" "1010 2nd St" "Omaha"
null "558 Maple Ave" "Toronto"
null "99 Jackson Rd" "Flin Flon"
null "821 Carol" "Nuuk"
Результаты при LEFT JOIN таблицы employees к таблице addresses такие же, как и при RIGHT JOIN таблицы addresses к таблице employees. Это потому, что в данном случае самой левой таблицей является addresses, поэтому, присоединяя таблицу employees к ней, мы говорим базе данных дать нам все записи addresses, а затем присоединить записи таблицы employees, если они существуют.
Теперь давайте сделаем еще один шаг вперед и посмотрим, что произойдет, если мы соединим команды JOIN вместе. Сначала давайте SELECT FROM таблицы addresses и RIGHT JOIN таблицы countries, чтобы увидеть, сколько записей countries не связаны с addresses. Запрос:
SELECT
a.street_address,
a.city,
a.state_or_province,
c.country_name
FROM addresses a
RIGHT JOIN countries c
ON a.country_id = c.id
Результаты:
"1010 2nd St" "Omaha" "NE" "United States"
"123 Main St" "Houston" "TX" "United States"
"3030 Burgos" "Xalapa" "Ver" "Mexico"
"220 C 30" "Izamal" "Yuc" "Mexico"
"99 Jackson Rd" "Flin Flon" "MB" "Canada"
"558 Maple Ave" "Toronto" "ON" "Canada"
"821 Carol" "Nuuk" "SQ" "Greenland"
NULL NULL NULL "Iceland"
Как видно из набора результатов нашего предыдущего запроса, единственной страной в нашей базе данных, не связанной с адресной записью, является Исландия.
Зная это, давайте посмотрим, что произойдет, если мы SELECT FROM из таблицы employees, LEFT JOIN из таблицы addresses, а затем RIGHT JOIN из таблицы countries. Как вы думаете, увидим ли мы все страны? Давайте узнаем! Запрос:
SELECT
e.first_name,
e.last_name,
a.street_address,
a.city,
a.state_or_province,
c.country_name
FROM employees e
LEFT JOIN addresses a
ON e.address_id = a.id
RIGHT JOIN countries c
ON a.country_id = c.id
Результаты:
"Lina" "Mazin" "1010 2nd St" "Omaha" "NE" "United States"
"Erik" "Whiting" "1010 2nd St" "Omaha" "NE" "United States"
"Bob" "Robertson" "123 Main St" "Houston" "TX" "United States"
"Nicole" "Nicholson" "3030 Burgos" "Xalapa" "Ver" "Mexico"
"Jordan" "Mays" "220 C 30" "Izamal" "Yuc" "Mexico"
NULL NULL NULL NULL NULL "Canada"
NULL NULL NULL NULL NULL "Greenland"
NULL NULL NULL NULL NULL "Iceland"
Это интересные результаты, которые показывают нам кое-что о поведении объединений RIGHT и LEFT. Обратите внимание, что мы получаем только 5 строк результатов, включающих записи из таблицы addresses, хотя в этой таблице 7 записей. Это потому, что существует только 5 addresses, которые связаны с записями employees, и когда мы LEFT JOIN соединили таблицу addresses, мы сказали базе данных, что нам нужны записи из таблицы addresses, только если они связаны с записями сотрудников. Однако, когда мы RIGHT JOIN таблицу countries, мы говорим базе данных, что нам нужны все записи из таблицы countries, даже если в наборе результатов нет связанной записи addresses.
Теперь, когда мы рассказали о разнице между RIGHT и LEFT, давайте поговорим о INNER и OUTER соединениях.
Внутренние и внешние соединения
Разница между объединениями INNER и OUTER очень похожа на разницу между объединениями LEFT и RIGHT. Однако в данном случае полезнее думать о таблицах как о диаграммах Венна. Знаете, такие, где два круга пересекаются в середине, а перекрывающаяся часть является каким-то общим атрибутом между кругами?
Мы по-прежнему хотим думать о том, что таблицы соединяются слева направо, но при INNER соединениях мы должны думать о том, как записи могут перекрываться.
ПРИМЕЧАНИЕ: Операция JOIN по умолчанию в Postgres (и во всех других РСУБД, с которыми я когда-либо работал) — это INNER JOIN. Это означает, что если вы пишете что-то вроде SELECT * FROM A JOIN B ON A.b_id = B.id, подразумевается, что вы хотите сделать внутреннее соединение, и SQL SELECT * FROM A INNER JOIN B ON A.b_id = B.id — это точно то же самое.
Давайте посмотрим, как работает INNER JOIN, когда мы присоединяем addresses к employees. Запрос:
SELECT e.first_name, a.street_address
FROM employees e
INNER JOIN addresses a
ON e.address_id = a.id
Результаты:
"Bob" "123 Main St"
"Jordan" "220 C 30"
"Nicole" "3030 Burgos"
"Erik" "1010 2nd St"
"Lina" "1010 2nd St"
Обратите внимание, что на этот раз у нас пять результатов, несмотря на то, что в базе данных есть шесть записей employees. Это отличается от LEFT JOIN из предыдущего раздела, где запись о сотруднике без связанной записи об адресе все еще находилась в наборе результатов. Таким образом, INNER JOIN означает, что нам нужны результаты из самой левой таблицы, только если к ним можно присоединить самую правую таблицу. Вот почему вы часто видите диаграммы Венна, используемые для объяснения объединений. В данном случае INNER JOIN является такой диаграммой Венна:
Итак, основное различие между LEFT и INNER соединениями, которое мы видели до сих пор, заключается в том, что если вам нужны все записи из самой левой таблицы — независимо от того, есть ли у них связанные записи в самой правой таблице или нет — вы хотите использовать LEFT соединение. Если вам нужны записи из самой левой таблицы только в том случае, если к ней можно присоединить запись из самой правой таблицы, вы хотите использовать INNER JOIN.
А как насчет OUTER? OUTER JOIN — это особый случай, потому что, хотя он кажется противоположным INNER JOIN, соединения OUTER должны быть указаны либо RIGHT, LEFT, либо FULL. Например, следующий SQL приведет к синтаксической ошибке:
-- Doesn't work!
SELECT e.first_name
FROM employees e
OUTER JOIN addresses a
ON e.address_id = a.id
Мы должны указать, хотим ли мы, чтобы самая левая или самая правая таблица была соединена внешним образом. Давайте посмотрим, как ведет себя LEFT OUTER JOIN. SQL:
SELECT
e.first_name,
a.street_address
FROM employees e
LEFT OUTER JOIN addresses a
ON e.address_id = a.id
Результаты:
"Bob" "123 Main St"
"Jordan" "220 C 30"
"Nicole" "3030 Burgos"
"Erik" "1010 2nd St"
"Lina" "1010 2nd St"
"Sandra" NULL
Здесь мы указали, что хотим, чтобы самая левая таблица была присоединена снаружи, и поэтому получили все результаты из employees независимо от того, может ли запись из таблицы addresses быть присоединена к ней или нет.
С другой стороны, RIGHT OUTER JOIN будет включать все записи из самой правой таблицы, независимо от того, может ли к ней быть присоединена запись из самой левой таблицы. SQL:
SELECT
e.first_name,
a.street_address
FROM employees e
RIGHT OUTER JOIN addresses a
ON e.address_id = a.id
Результаты:
"Bob" "123 Main St"
"Jordan" "220 C 30"
"Nicole" "3030 Burgos"
"Erik" "1010 2nd St"
"Lina" "1010 2nd St"
NULL "558 Maple Ave"
NULL "99 Jackson Rd"
NULL "821 Carol"
Здесь видно, что все записи addresses были включены в набор результатов, даже если не было записи employees, которую можно было бы к ней присоединить. Обратите внимание, что значение из employees, которое не имеет связанной записи addresses, не включено в набор результатов.
Заметили ли вы, что результаты для LEFT OUTER JOIN и RIGHT OUTER JOIN — это точно такие же результаты, которые мы получаем от LEFT JOIN и RIGHT JOIN соответственно? Если да, то молодец! LEFT JOIN и LEFT OUTER JOIN — это абсолютно одно и то же; то же самое с RIGHT JOIN и RIGHT OUTER JOIN. Когда вы используете RIGHT или LEFT в JOIN, OUTER подразумевается, и на самом деле вам не нужно его писать (хотя некоторые люди делают это, потому что говорят, что это добавляет ясности).
Существует еще один вид OUTER присоединения: FULL. В FULL OUTER JOIN будут включены все результаты как левой, так и правой таблиц, независимо от того, могут ли они быть соединены друг с другом. Проверьте, что это за SQL:
SELECT
e.first_name,
a.street_address
FROM employees e
FULL OUTER JOIN addresses a
ON e.address_id = a.id
Результаты:
"Bob" "123 Main St"
"Jordan" "220 C 30"
"Nicole" "3030 Burgos"
"Erik" "1010 2nd St"
"Lina" "1010 2nd St"
"Sandra" NULL
NULL "558 Maple Ave"
NULL "99 Jackson Rd"
NULL "821 Carol"
Посмотрите, как на этот раз у нас есть все записи из каждой таблицы. Самая левая таблица, employees, включает даже запись без связанной записи addresses. Аналогично, набор результатов включает три записи addresses без связанной записи employees. Это потому, что соединение FULL представляет собой соединение LEFT и RIGHT вместе взятые.
Заключение
Почти все сталкиваются с различными видами присоединений, когда впервые изучают SQL, поэтому давайте рассмотрим то, что мы узнали:
-
-
- Используйте это, когда вам нужны только полные результаты; записи из одной таблицы без связанных записей в другой таблице не будут отображаться в наборе результатов.
Теперь, когда вы знаете, как использовать соединения, попробуйте применить некоторые из них в базе данных примера, используя колонку departments. Как всегда, не стесняйтесь писать мне в твиттере @erikwhiting4 или по электронной почте erik@erikwhiting.com, если у вас есть вопросы. Удачи!