Явные операции соединения

В предложении FROM может быть указана явная операция соединения двух и более таблиц. Среди ряда операций соединения, описанных в стандарте языка SQL, многими серверами баз данных поддерживается только операция соединения по предикату. Синтаксис соединения по предикату имеет вид:

FROM <таблица 1>
[INNER]
{{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2>
[ON <предикат>]

Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом — LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.

Предикат определяет условие соединения строк из разных таблиц. При этом INNER JOIN означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Как правило, предикат определяет эквисоединение по внешнему и первичному ключам соединяемых таблиц, хотя это не обязательно.

Пример 5.6.3

SELECT maker, 
       Product.model AS model_1,
       PC.model AS model_2, 
       price
FROM Product 
    INNER JOIN PC ON PC.model = Product.model
ORDER BY maker, model_2;

В данном примере в результирующем наборе будут соединяться только те строки из таблиц РС и Product, у которых совпадают номера моделей.

Для визуального контроля в результирующий набор включен как номер модели из таблицы PC, так и из таблицы Product:

Рекомендуемые упражнения: 6, 7, 9, 13, 14, 18, 19, 21, 23, 25, 26, 27, 28, 32, 36, 37, 39, 43, 48, 49, 50, 51, 52, 54, 57, 58, 66, 72, 76, 77, 79, 87, 88, 93, 94, 102, 114, 127

Внешнее соединение LEFT JOIN означает, что помимо строк, для которых выполняется условие предиката, в результирующий набор попадут все остальные строки из первой таблицы (левой). При этом отсутствующие значения столбцов из правой таблицы будут заменены NULL-значениями.

Пример 5.6.4

SELECT maker, Product.model AS model_1, pc.model AS model_2, price
FROM Product 
    LEFT JOIN PC ON PC.model = Product.model
WHERE type = 'pc'
ORDER BY maker, PC.model;

Обратите внимание на то, что по сравнению с предыдущим примером пришлось использовать предложение WHERE для отбора только производителей ПК. В противном случае в результирующий набор попали бы также и модели ноутбуков, и принтеров. В рассмотренном ранее примере это условие было бы излишним, так как соединялись только те строки, у которых совпадали номера моделей, и одной из таблиц была таблица PC, содержащая только модели ПК. В результате выполнения запроса получим:

Поскольку моделей 2111 и 2112 из таблицы Product нет в таблице PС, в столбцах из таблицы PС содержится NULL.

Соединение RIGHT JOIN обратно соединению LEFT JOIN, то есть в результирующий набор попадут все строки из второй таблицы, которые будут соединяться только с теми строками из первой таблицы, для которых выполняется условие соединения. В нашем случае левое соединение

Product LEFT JOIN PC ON PC.model = Product.model

будет эквивалентно правому соединению

PC RIGHT JOIN Product ON PC.model = Product.model

Запрос же

SELECT maker, Product.model AS model_1, PC.model AS model_2, price
FROM Product 
    RIGHT JOIN PC ON PC.model = Product.model
ORDER BY maker, PC.model;

Наконец, при полном соединении (FULL JOIN) в результирующую таблицу попадут не только те строки, которые имеют одинаковые значения в сопоставляемых столбцах, но и все остальные строки исходных таблиц, не имеющие соответствующих значений в другой таблице. В этих строках все столбцы той таблицы, в которой не было найдено соответствия, заполняются NULL-значениями. То есть полное соединение представляет собой комбинацию левого и правого внешних соединений. Так, запрос для таблиц A и B, приведенных в начале главы,

SELECT A.*, B.*
FROM A 
    FULL JOIN B ON A.a = B.c;

даст следующий результат:

Заметим, что это соединение симметрично, то есть A FULL JOIN B эквивалентно B FULL JOIN A. Обратите также внимание на обозначение A.*, что означает вывести все столбцы таблицы А.

Рекомендуемые упражнения: 16, 29, 30, 34, 46, 55, 56, 59, 60, 64, 69, 70, 74, 109, 113, 128

UNION JOIN

Этот тип соединения был введен в стандарте SQL-92, но в более поздних версиях стандарта отсутствует. В частности, его уже нет в стандарте SQL2003 (ANSI и ISO). Как и многие другие конструкции языка SQL, соединение UNION JOIN является избыточным, поскольку его можно выразить через разность полного и внутреннего соединений. Формально это можно записать следующим образом:

A UNION JOIN B :=
(A FULL JOIN B)
EXCEPT
(A INNER JOIN B)

Ну, а если полное соединение не поддерживается (MySQL), то его можно получить объединением левого и правого внешних соединений. Тогда наша формула примет вид

A UNION JOIN B :=
((A LEFT JOIN B)
UNION
(A RIGHT JOIN B))
EXCEPT
(A INNER JOIN B)

Давайте в качестве примера, где мог бы пригодиться этот тип соединения, рассмотрим следующую задачу.

Будь у нас возможность использовать UNION JOIN, мы бы решили задачу так:

select * 
from
    (select distinct maker from Product where type='pc') m_pc
        UNION JOIN
    (select distinct maker from Product where type='printer') m_printer
        on m_pc.maker = m_printer.maker;

Воспользуемся формулой. Полное соединение производителей ПК и производителей принтеров даст нам как тех, кто производит что-то одно, так и тех, кто производит и то, и другое.

select * 
from
    (select distinct maker from Product where type='pc') m_pc
        FULL JOIN
    (select distinct maker from Product where type='printer') m_printer
        on m_pc.maker = m_printer.maker;

Теперь вычтем из результата тех, кто производит и то, и другое (внутренее соединение):

select m_pc.maker m1, m_printer.maker m2 
from
    (select maker from Product where type='pc') m_pc
        FULL JOIN
    (select maker from Product where type='printer') m_printer
        on m_pc.maker = m_printer.maker
EXCEPT
select * 
from
    (select maker from Product where type='pc') m_pc
        INNER JOIN
    (select maker from Product where type='printer') m_printer
        on m_pc.maker = m_printer.maker;

Попутно я убрал из этого решения избыточные DISTINCT, поскольку EXCEPT выполнит исключение дубликатов. Это тут единственный полезный урок, т.к. операцию взятия разности (EXCEPT) можно заменить простым предикатом:

where m_pc.maker IS NULL or m_printer.maker IS NULL

или даже так

m_pc.maker + m_printer.maker IS NULL

ввиду того, что конкатенация с NULL-значением дает NULL.

select * 
from
    (select distinct maker from Product where type='pc') m_pc
        FULL JOIN
    (select distinct maker from Product where type='printer') m_printer
        on m_pc.maker = m_printer.maker
where m_pc.maker IS NULL 
    or m_printer.maker IS NULL;

Наконец, чтобы представить результат в один столбец, воспользуемся функцией COALESCE:

select COALESCE(m_pc.maker, m_printer.maker) 
from
    (select distinct maker from Product where type='pc') m_pc
        FULL JOIN
    (select distinct maker from Product where type='printer') m_printer
        on m_pc.maker = m_printer.maker
where m_pc.maker IS NULL 
    or m_printer.maker IS NULL;

Разумеется, это не единственный способ решения данной задачи. Он лишь демонстрирует замену репрессированного типа соединений.

Мне неизвестны СУБД, в которых было бы реализовано соединение UNION JOIN.

Ассоциативность и коммутативность соединений

Внутреннее и полное внешнее соединения являются как коммутативными, так и ассоциативными, т.е. для них справедливо следующее:

A [FULL | INNER] JOIN B = B [FULL | INNER] JOIN A

и

(A [FULL | INNER] JOIN B) [FULL | INNER] JOIN С =
A [FULL | INNER] JOIN (B [FULL | INNER] JOIN С)

Очевидно, что левое/правое соединения не коммутативны, т.к.

A LEFT JOIN B = B RIGHT JOIN A

но ассоциативны, например:

(A LEFT JOIN B) LEFT JOIN C = A LEFT JOIN (B LEFT JOIN C)

С практической точки зрения ассоциативность означает, что мы можем не расставлять скобки, определяющие прядок выполнения соединений.

Однако закон ассоциативности, справедливый для однотипных соединений, нарушается, если в одном запросе используются соединения разных типов. Покажем это на примере.

with 
a(a_id) as
    (select * 
     from (values('1'),('2'),('3')) x(y)
    ),
b(b_id) as
    (select * 
     from (values('1'),('2'),('4')) x(y)
    ),
c(c_id) as
    (select * 
     from (values('5'),('2'),('3')) x(y)
    )
select a_id, b_id, c_id 
from (a left join b on a_id=b_id) 
    inner join c on b_id=c_id
union all
select '','',''
union all
select a_id, b_id, c_id 
from a 
    left join (b inner join c on b_id=c_id) on a_id=b_id;

Результаты двух запросов отделены друг от друга пробельной строкой для удобства.

Заметим, что при отсутствии скобок мы получим результат, совпадающий с результатом первого запроса, поскольку соединения будут выполняться в том порядке, в каком они записаны.