Типы соединений SQL
| Тип соединения | Описание |
| INNER JOIN (ВНУТРЕННЕЕ СОЕДИНЕНИЕ) | Возвращает те строки, для которых в обеих таблицах выполняется условие соединения. |
| LEFT JOIN (ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ или просто ЛЕВОЕ СОЕДИНЕНИЕ) | Возвращает строки, содержащие данные из левой таблицы (указанной слева от ключевого слова JOIN), даже если в правой таблице нет совпадающих строк.
В строках, где нет совпадения справа, все столбцы справа будут заполнены значением NULL. |
| RIGHT JOIN(ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ или просто ПРАВОЕ СОЕДИНЕНИЕ) | Возвращает строки, содержащие данные из правой таблицы (указанной справа от ключевого слова JOIN), даже если в левой таблице нет совпадающих строк.
В строках, где нет совпадения слева, все столбцы слева будут заполнены значением NULL. |
| FULL JOIN (ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ или просто ПОЛНОЕ СОЕДИНЕНИЕ) | Возвращает строки, содержащие данные из обеих таблиц.
В строках, где нет совпадения слева или справа, все столбцы без совпадения будут заполнены значением NULL. |
| CROSS JOIN (ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ) | Возвращает строки, содержащие все возможные комбинации левой и правой частей соединения. Такое соединение называют «декартовым произведением» (прим. перев.) |
Есть еще несколько терминов, которые используются для операций соединения:
| Термин | Описание |
| Self-join (самосоединение) | Соединение данных в таблице с данными в этой же таблице (с самой собой) |
| Natural join (естественное соединение) | Неявное соединение двух таблиц за счет совпадения имен столбцов в этих таблицах |
| Equi-join (равнозначное соединение) | Соединение двух таблиц, в условии которого используется только операция равенства (=) |
Синтаксис соединений
Внутреннее соединение (inner join) может быть описано с помощью предложений FROM или WHERE в операторе SELECT или других операторах DML (data manipulation language, языка манипулирования данными).
Чтобы создать соединение с помощью предложения FROM, нужно поступить следующим образом:
SELECT *
FROM Table1 <JoinType> Table2 [ON (JoinCondition)]
Здесь конструкция <JoinType>JoinCondition – это логическое выражение (предикат), которое должно быть вычислено для каждой пары соединяемых строк.
Чтобы создать соединение с помощью предложения WHERE, напишите так:
SELECT *
FROM Table1, Table2 [WHERE (JoinCondition)]
В этом случае JoinCondition – это тоже логическое выражение, которое должно быть вычислено для каждой пары соединяемых строк.
Любая часть предложения, заключенная в квадратные скобки [ ]
Таблицы данных для наших примеров
Для демонстрации примеров соединений мы будем использовать две таблицы с данными:
- Таблицу PetTypes
+-------------+-----------+
| PetTypeId | PetType |
|-------------+-----------|
| 1 | Bird |
| 2 | Cat |
| 3 | Dog |
| 4 | Rabbit |
+-------------+-----------+
(4 rows affected)
- Таблицу Pets
+---------+-------------+-----------+-----------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB |
|---------+-------------+-----------+-----------+------------|
| 1 | 2 | 3 | Fluffy | 2020-11-20 |
| 2 | 3 | 3 | Fetch | 2019-08-16 |
| 3 | 2 | 2 | Scratch | 2018-10-01 |
| 4 | 3 | 3 | Wag | 2020-03-15 |
| 5 | 1 | 1 | Tweet | 2020-11-28 |
| 6 | 3 | 4 | Fluffy | 2020-09-17 |
| 7 | 3 | 2 | Bark | NULL |
| 8 | 2 | 4 | Meow | NULL |
+---------+-------------+-----------+-----------+------------+
(8 rows affected)
Внутреннее соединение (INNER JOIN)
Внутреннее соединение в SQL возвращает строки, для которых в обеих таблицах выполняется условие соединения:
SELECT
Pets.PetName,
PetTypes.PetType
FROM Pets
INNER JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId;
Результат:
----------+-----------+
| PetName | PetType |
|---------+-----------|
| Fluffy | Cat |
| Fetch | Dog |
| Scratch | Cat |
| Wag | Dog |
| Tweet | Bird |
| Fluffy | Dog |
| Bark | Dog |
| Meow | Cat |
+---------+-----------+
(8 rows affected)
Чтобы указать внутренние соединения в предложении FROM, нужно использовать ключевые слова INNER JOIN. Далее после слова ON мы должны описать предикат, соединяющий части строк из наших двух таблиц.
При любом типе соединения нам желательно указывать имена столбцов вместе с именами таблиц, в которых они содержатся. Такой способ записи позволяет избежать неоднозначностей из-за того, что в разных таблицах могут быть столбцы с одинаковыми именами (как в нашем примере). СУБД просто не поймет, из какой таблицы нужно взять столбец. Использование имени таблицы как префикса решит эту проблему. В нашем примере в обеих таблицах есть столбец PetTypeId. В таблице Pets этот столбец используется в качестве внешнего ключа, а в таблице PetTypes – в качестве первичного.
В результате мы получили не все виды питомцев, так как в таблице Pets у нас нет кроликов (rabbit), хотя в таблице PetTypes они есть. Причина в том, что внутреннее соединение возвращает только соединенные строки, для которых условие соединения выполняется в обеих таблицах.
Обратите внимание, что для внутреннего соединения слово INNER часто не является обязательным. Многие (но не все) СУБД позволяют сокращать тип соединения до слова JOIN, что предполагает именно внутреннее соединение таблиц.
Поэтому наш пример мог бы быть записан короче:
SELECT
Pets.PetName,
PetTypes.PetType
FROM Pets
JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId;
Для простоты всё предложение FROM можно записать в одну строку:
SELECT
Pets.PetName,
PetTypes.PetType
FROM Pets JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId;
Псевдонимы
Часто для сокращения записи соединений используют псевдонимы таблиц. Такую запись становится проще читать.
Например, можно переписать предыдущий запрос так:
SELECT
p.PetName,
pt.PetType
FROM Pets p INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;
Результат:
+----------+-----------+
| PetName | PetType |
|----------+-----------|
| Fluffy | Cat |
| Fetch | Dog |
| Scratch | Cat |
| Wag | Dog |
| Tweet | Bird |
| Fluffy | Dog |
| Bark | Dog |
| Meow | Cat |
+----------+-----------+
(8 rows affected)
Равнозначное соединение
Соединение, показанное выше, называется еще равнозначным соединением. Такое соединение содержит внутри предиката только операции «равно» (=).
Есть и другой способ описать такое соединение:
SELECT
p.PetName,
pt.PetType
FROM
Pets p,
PetTypes pt
WHERE p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+-----------+
| PetName | PetType |
|-----------+-----------|
| Fluffy | Cat |
| Fetch | Dog |
| Scratch | Cat |
| Wag | Dog |
| Tweet | Bird |
| Fluffy | Dog |
| Bark | Dog |
| Meow | Cat |
+-----------+-----------+
Это пример описания внутреннего соединения с помощью предложения WHERE. Мы указали список таблиц, просто перечислив их через запятую, а условие соединения перенесли в предложение WHERE. Если бы мы не указали такое условие, то мы получили бы перекрестное соединение (cross join).
Многие начинающие считают такой способ записи внутреннего соединения более простым, чем с использованием слов INNER JOIN. Вы вправе выбрать любой из двух вариантов на свой вкус, но учтите, что способ записи с использованием INNER JOIN считается более профессиональным.
Правое соединение (RIGHT JOIN)
Правое соединение, которое еще называют правым внешним соединением, возвращает строки, в которых всегда есть правая часть (полученная из таблицы, стоящей справа от слова JOIN). Строка будет получена, даже если в левой части не нашлось значений, которые описаны предикатом.
SELECT
p.PetName,
pt.PetType
FROM Pets p
RIGHT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+-----------+
| PetName | PetType |
|-----------+-----------|
| Tweet | Bird |
| Fluffy | Cat |
| Scratch | Cat |
| Meow | Cat |
| Fetch | Dog |
| Wag | Dog |
| Fluffy | Dog |
| Bark | Dog |
| NULL | Rabbit |
+-----------+-----------+
(9 rows affected)
В этом результате мы видим строки с видом «кролик» (rabbit), которых не было в выборке после внутреннего соединения. Напротив слова «Rabbit» в колонке PetName (имя питомца) стоит значение NULL.
Левое соединение (LEFT JOIN)
Как и правое соединение, левое соединение называют левым внешним соединением. Оно возвращает строки, в которых всегда есть левая часть (полученная из таблицы, стоящей слева от слова JOIN). Строка будет получена, даже если в правой части не нашлось значений, которые описаны предикатом. Левое соединение по своему смыслу противоположно правому соединению.
Если немного изменить предыдущий пример с использованием левого соединения, то он будет выглядеть так:
SELECT
p.PetName,
pt.PetType
FROM Pets p
LEFT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+-----------+
| PetName | PetType |
|-----------+-----------|
| Fluffy | Cat |
| Fetch | Dog |
| Scratch | Cat |
| Wag | Dog |
| Tweet | Bird |
| Fluffy | Dog |
| Bark | Dog |
| Meow | Cat |
+-----------+-----------+
(8 rows affected)
В этом конкретном случае наш результат совпадает с результатом внутреннего соединения. Однако если мы поменяем местами таблицы в предложении FROM, тогда мы получим такую же выборку, как и при правом соединении.
SELECT
p.PetName,
pt.PetType
FROM PetTypes pt
LEFT JOIN Pets p
ON p.PetTypeId = pt.PetTypeId;
Результат:
+-----------+-----------+
| PetName | PetType |
|-----------+-----------|
| Tweet | Bird |
| Fluffy | Cat |
| Scratch | Cat |
| Meow | Cat |
| Fetch | Dog |
| Wag | Dog |
| Fluffy | Dog |
| Bark | Dog |
| NULL | Rabbit |
+-----------+-----------+
(9 rows affected)
Как видите, разница в результатах между левым и правым соединениями зависит исключительно от того, как вы располагаете таблицы в предложении FROM
Комментарии