Эквисоединения
Соединения, которые мы рассмотрели ранее и которые преобладают в примерах данного учебника, называются соединениями по предикату.
Синтаксис этого вида соединения такой:
Таблица_1 <тип соединения> JOIN Таблица_2 ON <предикат>
где
тип соединения := [INNER] | [OUTER]{LEFT | RIGHT | FULL}
Эти соединения являются наиболее общими, т.к. в качестве предиката может быть использовано любое логическое выражение. Именно по этой причине все диалекты поддерживают этот вид соединения.
Частным, но часто используемым соединением является эквисоединение - случай, когда предикат представляет собой равенство значений в столбцах соединяемых таблиц.
При этом соединяемые столбцы зачастую имеют одинаковые имена, поскольку в соединении участвуют таблицы, связанные внешним ключом. Впрочем, последнее не суть важно, т.к. мы можем переименовать столбцы, если это нам потребуется.
Так вот для этого частного случая соединения - эквисоединения по столбцам с одинаковыми именами - имеются отдельные синтаксические формы соединения: естественное соединение и соединение, использующее имена столбцов.
Естественное соединение
Таблица_1 NATURAL <тип соединения> JOIN Таблица_2
Предикат здесь не нужен, т.к. он подразумевается, а именно попарное равенство всех столбцов с одинаковыми именами в обеих таблицах. Например, если у обеих соединяемых таблиц есть столбцы a и b, то естественное соединение
Таблица_1 NATURAL INNER JOIN Таблица_2
будет эквивалентно такому соединению по предикату:
Таблица_1 INNER JOIN Таблица_2 ON Таблица_1.a = Таблица_2.a AND Таблица_1.b = Таблица_2.b
Кроме того, при естественном соединении одноименные столбцы будут присутствовать в выборке в одном экземпляре. Сравните, например, результаты таких запросов (база данных Аэрофлот)
select *
from Pass_in_trip
join Passenger on Pass_in_trip.id_psg = Passenger.id_psg
where trip_no=1123;
[[ column ]] |
---|
[[ value ]] |
trip_no | date | id_psg | place | id_psg | name |
---|---|---|---|---|---|
1123 | 2003-04-05 00:00:00 | 3 | 2a | 3 | Kevin Costner |
1123 | 2003-04-08 00:00:00 | 1 | 4c | 1 | Bruce Willis |
1123 | 2003-04-08 00:00:00 | 6 | 4b | 6 | Ray Liotta |
select *
from Pass_in_trip
NATURAL join Passenger
where trip_no=1123;
[[ column ]] |
---|
[[ value ]] |
id_psg | trip_no | date | place | name |
---|---|---|---|---|
6 | 1123 | 2003-04-08 00:00:00 | 4b | Ray Liotta |
3 | 1123 | 2003-04-05 00:00:00 | 2a | Kevin Costner |
1 | 1123 | 2003-04-08 00:00:00 | 4c | Bruce Willis |
Как видно из представленных результатов, столбец id_psg, по которому выполняется соединение, не повторяется для естественного соединения.
Из СУБД, доступных на сайте sql-ex.ru, только SQL Server не поддерживает естественное соединение. Если вы хотите поработать с естественным соединением практически, выберите в консоли PostgreSQL или MySQL.
Если требуется выполнить эквисоединение не по всем столбцам с совпадающими именами, а только по их части, тогда мы можем использовать соединение USING:
Таблица_1 <тип соединения> JOIN Таблица_2 USING(<список столбцов>)
Список столбцов содержит те столбцы, по которым выполняется эквисоединение. Соответственно, в этом списке могут присутствовать только те из столбцов, имена которых совпадают в обеих соединяемых таблицах.
Сравните результаты следующих запросов (предикат в предложении WHERE использован лишь для сокращения размера выборки).
Соединение строк из таблиц Income и Outcome по равенству значений в столбце date (база данных Фирма вторсырья)
select *
from Income
join Outcome USING(date)
where MONTH(date) >= 4;
[[ column ]] |
---|
[[ value ]] |
date | code | point | inc | code | point | out |
---|---|---|---|---|---|---|
2001-04-13 00:00:00 | 6 | 1 | 5000 | 7 | 1 | 4490 |
2001-04-13 00:00:00 | 10 | 1 | 5000 | 7 | 1 | 4490 |
2001-05-11 00:00:00 | 7 | 1 | 4500 | 9 | 1 | 2530 |
2001-09-13 00:00:00 | 12 | 3 | 1350 | 16 | 3 | 1200 |
2001-09-13 00:00:00 | 13 | 3 | 1750 | 16 | 3 | 1200 |
2001-09-13 00:00:00 | 12 | 3 | 1350 | 17 | 3 | 1500 |
2001-09-13 00:00:00 | 13 | 3 | 1750 | 17 | 3 | 1500 |
Соединение строк из таблиц Income и Outcome по равенству значений в столбцах date и point
select *
from Income
join Outcome USING(date, point)
where MONTH(date) >= 4;
[[ column ]] |
---|
[[ value ]] |
point | date | code | inc | code | out |
---|---|---|---|---|---|
1 | 2001-04-13 00:00:00 | 6 | 5000 | 7 | 4490 |
1 | 2001-04-13 00:00:00 | 10 | 5000 | 7 | 4490 |
1 | 2001-05-11 00:00:00 | 7 | 4500 | 9 | 2530 |
3 | 2001-09-13 00:00:00 | 12 | 1350 | 16 | 1200 |
3 | 2001-09-13 00:00:00 | 13 | 1750 | 16 | 1200 |
3 | 2001-09-13 00:00:00 | 12 | 1350 | 17 | 1500 |
3 | 2001-09-13 00:00:00 | 13 | 1750 | 17 | 1500 |
Соединение строк из таблиц Income и Outcome по равенству значений в столбцах date, point и code
select *
from Income
join Outcome USING(date, point, code);
[[ column ]] |
---|
[[ value ]] |
Это соединение по всем столбцам с совпадающими именами эквивалентно естественному соединению
select *
from Income
NATURAL join Outcome;
[[ column ]] |
---|
[[ value ]] |
Чтобы пояснить вывод в последнем варианте, воспользуемся левым соединением
select *
from Income
left join Outcome USING(date, point, code)
where MONTH(date) >= 4;
[[ column ]] |
---|
[[ value ]] |
code | point | date | inc | out |
---|---|---|---|---|
6 | 1 | 2001-04-13 00:00:00 | 5000 | NULL |
7 | 1 | 2001-05-11 00:00:00 | 4500 | NULL |
10 | 1 | 2001-04-13 00:00:00 | 5000 | NULL |
12 | 3 | 2001-09-13 00:00:00 | 1350 | NULL |
13 | 3 | 2001-09-13 00:00:00 | 1750 | NULL |