Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 06 December 2019 09:33:43


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

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

Моисеенко С.И.

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

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.

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.