Готовимся ко второму этапу тестирования

Типичные проблемы

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

Решение 8.6.1

Консоль

Выполнить

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

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;

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;

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;

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

    Схема

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

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