Эти «хитрые» внешние соединения

Пусть требуется для каждого класса определить все корабли с известным годом спуска на воду. Когда говорится «для каждого класса», мы уже знаем, что нужно использовать внешнее соединение, например, левое:

Решение 8.6.1

SELECT Classes.class, name, launched
FROM Classes 
    LEFT JOIN Ships ON Classes.class = Ships.class 
                   AND launched IS NOT NULL;

Тем самым мы соединяем таблицу Classes с таблицей Ships по столбцу class и отбираем корабли с известным годом спуска на воду. Вот что, помимо прочего, мы имеем в результате:

Как же так? Мы же указывали в предикате соединения launched IS NOT NULL? В словах «в предикате соединения» как раз и кроется ответ на наш вопрос. Вернемся к определению внешнего левого соединения:

Соединяются все строки из левой таблицы с теми строками из правой, для которых значение предиката истинно. Если для какой-либо строки из левой таблицы нет ни одной соответствующей строки из правой таблицы, то значения столбцов правой таблицы получают значение NULL.

В таблице Ships нет ни одного корабля класса Bismarck. Потому мы и получили эту строку, так как класс Bismarck есть в таблице Classes. А если бы такой корабль был? Давайте добавим в таблицу Ships два корабля класса Bismarck — один с известным годом спуска на воду, а другой — с неизвестным:

SELECT *
FROM Ships
UNION ALL
SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
UNION ALL
SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched;

Перепишем решение 8.6.1 с учетом этих новых кораблей:

Решение 8.6.2

SELECT Classes.class, name, launched
FROM Classes 
    LEFT JOIN (SELECT *
               FROM Ships
               UNION ALL
               SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
               UNION ALL
               SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched
              ) Ships ON Classes.class = Ships.class AND
launched IS NOT NULL;

Теперь получаем ожидаемый результат, а именно, в результирующем наборе будет присутствовать только один корабль класса Bismarck:

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

Решение 8.6.3

SELECT Classes.class, name, launched
FROM Classes 
    LEFT JOIN Ships ON Classes.class = Ships.class
WHERE launched IS NOT NULL;

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

В заключении замечу, что данный пример не является вполне показательным, так как для решения поставленной задачи вполне подошло бы внутреннее соединение (INNER JOIN), несмотря на слова «для каждого класса». Однако гибкость языка SQL позволяет решить задачу разными способами, и использование стереотипов вполне оправдано.

Как правило, в приводимых примерах используются эквисоединения, т.е. соединения по равенству значений (=). Это обусловлено тем, что на практике зачастую используется соединение по внешнему ключу. Подобные примеры можно увидеть выше. Однако предикатом соединения может быть любое логическое выражение. Для иллюстрации рассмотрим следующую задачу.

Решение.

select Income_o.* 
from Outcome_o 
    right join Income_o on Outcome_o.out >= Income_o.inc
where Outcome_o.out IS NULL;

В вышеприведенном решении внешнее соединение выполняется по неравенству Outcome_o.out >= Income_o.inc, которому отвечают строки из таблицы Income_o, для которых приход не превышает расхода для каких-либо строк в таблице Outcome_o. Кроме того, во внешнем соединении (в данном случае в правом) будут присутствовать и строки из таблицы Income_o, для которых не нашлось ни одной строки в таблице Outcome_o, делающей истинным значение предиката.

Это и есть строки, являющиеся решением нашей задачи. Чтобы их выбрать, используем тот факт, что отсутствующие значения столбцов из соединяемой таблицы (у нас левой) заполняются NULL-значениями. Соответствующий критерий помещаем в предложение WHERE.

Разумеется, эту задачу можно решить и другими способами, например:

max + подзапрос

select Income_o.* 
from Income_o
where Income_o.inc > (select max(Outcome_o.out) 
                      from Outcome_o);

all + подзапрос

select Income_o.* 
from Income_o
where Income_o.inc > all(select Outcome_o.out 
                         from Outcome_o);