loading..
Русский    English
21:53
листать

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

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

Решение 8.6.1

Консоль
Выполнить
  1. SELECT Classes.class, name, launched
  2. FROM Classes LEFT JOIN
  3. Ships ON Classes.class = Ships.class AND
  4. launched IS NOT NULL;

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

Class Name launched
Bismarck NULL NULL

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

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

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

Консоль
Выполнить
  1. SELECT *
  2. FROM Ships
  3. UNION ALL
  4. SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
  5. UNION ALL
  6. SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched;

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

Решение 8.6.2

Консоль
Выполнить
  1. SELECT Classes.class, name, launched
  2. FROM Classes LEFT JOIN
  3. (SELECT *
  4. FROM Ships
  5. UNION ALL
  6. SELECT 'B_1' AS name, 'Bismarck' AS class, 1941 AS launched
  7. UNION ALL
  8. SELECT 'B_2' AS name, 'Bismarck' AS class, NULL AS launched
  9. ) Ships ON Classes.class = Ships.class AND
  10. launched IS NOT NULL;

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

Class Name launched
Bismarck B_1 1941

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

Решение 8.6.3

Консоль
Выполнить
  1. SELECT Classes.class, name, launched
  2. FROM Classes LEFT JOIN
  3. Ships ON Classes.class = Ships.class
  4. WHERE launched IS NOT NULL;

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

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100