На главную страницу
Эти "хитрые" внешние соединения
Моисеенко С.И.
Пусть требуется для каждого класса определить все корабли с известным годом
спуска на воду. Когда говорится "для каждого класса", мы уже знаем, что нужно
использовать внешнее соединение, например, левое:
SELECT Classes.class, name, launched FROM Classes
LEFT JOIN Ships ON Classes.class=Ships.class
AND launched IS NOT NULL
Т.е. мы соединяем таблицу Classes с таблицей Ships по столбцу class и отбираем
корабли с известным годом спуска на воду. Вот что, помимо прочего, мы имеем в
результате:
class |
name
|
launched
|
Bismarck |
NULL |
NULL |
Как же так? Мы же указывали в предикате соединения 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 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:
class |
name
|
launched
|
Bismarck |
B_1 |
1941 |
Вывод. Если вам нужно ограничить результирующий набор внешнего соединения,
используйте предложение WHERE, которое и служит для этой цели:
SELECT Classes.class, name, launched FROM Classes
LEFT JOIN Ships ON Classes.class=Ships.class
WHERE launched IS NOT NULL
Предикат же соединения определяет лишь то, какие строки из разных таблиц будут
конкатенированы в результирующем наборе.
В заключении замечу, что данный пример не является вполне показательным, т.к.
для решения поставленной задачи вполне подошло бы внутреннее соединение (INNER
JOIN), несмотря на слова "для каждого класса". Однако гибкость языка SQL
позволяет решить задачу разными способами, и использование стереотипов вполне
оправдано.
Приведенные здесь примеры можно выполнить непосредственно на сайте, установив
флажок "Без проверки" на странице с упражнениями
на SELECT.
На главную страницу