Когда люди говорят о SQL JOIN, они часто используют диаграммы Венна, чтобы проиллюстрировать включение и исключение двух объединенных наборов: 
Хотя эти диаграммы Венна, безусловно, полезны для понимания (и запоминания) синтаксиса SQL JOIN, они не совсем точны, поскольку SQL JOIN — это особый тип декартового произведения, CROSS JOIN.
Иллюстрация пользователя Википедии Quartl
В декартовом произведении между двумя наборами A и B результатом является умножение каждого множества, означающее, что каждый элемент a ∈ A объединяется с каждым элементом b ∈ B образуя набор кортежей (a, b) .
Обычные соединения SQL делают именно это. Когда вы присоединитесь BOOK к AUTHOR, вы, вероятно, получите комбинацию каждого author ∈ AUTHOR с каждой book ∈ BOOK , так что для каждой комбинации (author, book) автор фактически написал книгу.
Истинный смысл диаграмм Венна
Истинный смысл диаграмм Венна гораздо лучше описывается операциями
-
UNION -
INTERSECT -
EXCEPT(илиMINUSв Oracle)
В следующих разделах мы увидим, что эти операции точно соответствуют семантике операций, которая может быть проиллюстрирована диаграммами Венна, даже если вы сможете «злоупотреблять» операциями JOIN для достижения того же результата.
UNION
Операция UNION является наиболее известной среди этих операций с множествами. Это часто также называют «объединением» двух наборов кортежей, где результатом является объединение набора B с набором A
В следующем примере мы увидим, что нас могут заинтересовать разные люди из нашей базы данных, учитывая их имена и фамилии, независимо от того, являются ли они customer или staff :
Исходные диаграммы Венна использовали FULL OUTER JOIN для моделирования «одной и той же» концепции, хотя эти две вещи не являются строго одинаковыми. Рассмотрим следующий запрос, который мы запустим для базы данных Sakila :
|
1
2
3
4
5
6
|
SELECT first_name, last_nameFROM customerUNIONSELECT first_name, last_nameFROM staffORDER BY 1, 2 |
Результат выглядит так:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
first_name last_name------------------------------------AARON SELBYADAM GOOCHADRIAN CLARYAGNES BISHOPALAN KAHNALBERT CROUSEALBERTO HENNINGALEX GRESHAMALEXANDER FENNELLALFRED CASILLASALFREDO MCADAMSALICE STEWARTALICIA MILLS... |
Теперь запустите следующий «эквивалентный» запрос:
|
1
2
3
4
5
|
SELECT first_name, last_nameFROM customerFULL OUTER JOIN staff USING (first_name, last_name)ORDER BY 1, 2 |
Результат снова даст:
|
01
02
03
04
05
06
07
08
09
10
|
first_name last_name------------------------------------AARON SELBYADAM GOOCHADRIAN CLARYAGNES BISHOPALAN KAHNALBERT CROUSEALBERTO HENNING... |
Это работает только потому, что мы используем предложение USING , которое изначально поддерживается не каждой базой данных. Если бы мы сделали JOIN с более часто используемым предложением ON, нам пришлось бы написать более утомительное:
|
1
2
3
4
5
6
7
8
|
SELECT COALESCE(c.first_name, s.first_name) AS first_name, COALESCE(c.last_name, s.last_name) AS last_nameFROM customer cFULL OUTER JOIN staff s ON (c.first_name, c.last_name) = (s.first_name, s.last_name)ORDER BY 1, 2 |
В этом случае большинство людей, вероятно, по умолчанию уже используют UNION , поскольку это гораздо более известная операция, чем FULL OUTER JOIN .
Все поддерживаемые в настоящее время СУБД jOOQ поддерживают UNION и UNION ALL (последняя не удаляет дубликаты).
Далее мы увидим, что эквивалентные сравнения могут быть сделаны с другими операциями над множествами:
ПЕРЕСЕЧЕНИЕ
Операция INTERSECT действительно полезна, когда вы хотите сохранить только те кортежи, которые присутствуют в обоих наборах, которые объединены с использованием INTERSECT :
Как видите, мы можем пожелать сохранить только тех клиентов, которые также являются действующими лицами. Давайте запустим этот запрос:
|
1
2
3
4
5
|
SELECT first_name, last_nameFROM customerINTERSECTSELECT first_name, last_nameFROM actor |
|
1
2
3
|
first_name last_name------------------------------------JENNIFER DAVIS |
Один из наших клиентов также актер. Тот же самый запрос мог быть написан с INNER JOIN как таковой:
|
1
2
3
4
|
SELECT first_name, last_nameFROM customerINNER JOIN actor USING (first_name, last_name) |
… или с синтаксисом ON
|
1
2
3
4
5
|
SELECT c.first_name, c.last_nameFROM customer cINNER JOIN actor a ON (c.first_name, c.last_name) = (a.first_name, a.last_name) |
На этот раз COALESCE не требуется, поскольку INNER JOIN сохраняет только те кортежи из декартового произведения, которые присутствуют на «обеих сторонах» JOIN , поэтому мы можем выбрать любую из таблиц для префикса наших столбцов.
Вы можете даже решить использовать полусоединение вместо этого, что даст те же результаты:
|
1
2
3
4
5
6
|
SELECT first_name, last_nameFROM customerWHERE (first_name, last_name) IN ( SELECT first_name, last_name FROM actor) |
или, используя более подробный, но эквивалентный предикат EXISTS :
|
1
2
3
4
5
6
7
8
|
SELECT first_name, last_nameFROM customer cWHERE EXISTS ( SELECT 1 FROM actor a WHERE (c.first_name, c.last_name) = (a.first_name, a.last_name)) |
Все вышеперечисленное, опять же, дает:
|
1
2
3
|
first_name last_name------------------------------------JENNIFER DAVIS |
КРОМЕ
Операция EXCEPT полезна, когда вы хотите сохранить только те кортежи, которые присутствуют в одном наборе, но не в другом:
Выполнение этого запроса:
|
1
2
3
4
5
6
|
SELECT first_name, last_nameFROM customerEXCEPTSELECT first_name, last_nameFROM staffORDER BY 1, 2 |
… даст:
|
01
02
03
04
05
06
07
08
09
10
|
first_name last_name------------------------------------AARON SELBYADAM GOOCHADRIAN CLARYAGNES BISHOPALAN KAHNALBERT CROUSEALBERTO HENNING... |
Согласно исходным диаграммам Венна, это можно настроить с помощью LEFT JOIN и предиката IS NULL :
|
1
2
3
4
5
6
|
SELECT first_name, last_nameFROM customerLEFT JOIN staff USING (first_name, last_name)WHERE staff_id IS NULLORDER BY 1, 2 |
или с предложением ON :
|
1
2
3
4
5
6
7
|
SELECT c.first_name, c.last_nameFROM customer cLEFT JOIN staff s ON (c.first_name, c.last_name) = (s.first_name, s.last_name)WHERE staff_id IS NULLORDER BY 1, 2 |
Это совершенно нечитаемо и не говорит о том, что мы удаляем кортежи из набора CUSTOMER , учитывая их присутствие в другом наборе STAFF .
Эквивалентная версия, использующая анти-объединение, может быть более читабельной ( хотя NULLs внимание на NULLs в предикатах NOT IN ! ):
|
1
2
3
4
5
6
7
|
SELECT c.first_name, c.last_nameFROM customer cWHERE (first_name, last_name) NOT IN ( SELECT first_name, last_name FROM staff)ORDER BY 1, 2 |
… или, используя NOT EXISTS :
|
1
2
3
4
5
6
7
8
9
|
SELECT c.first_name, c.last_nameFROM customer cWHERE NOT EXISTS ( SELECT 1 FROM staff s WHERE (c.first_name, c.last_name) = (s.first_name, s.last_name))ORDER BY 1, 2 |
Вывод
UNION , INTERSECT и EXCEPT — это очень простые, но очень полезные операции, которые могут время от времени приносить большую пользу в ваших ежедневных задачах SQL. Хотя операции JOIN гораздо более универсальны, они также более сложны для простых задач, которые могут быть решены с помощью UNION , INTERSECT и EXCEPT
| Ссылка: | Возможно, вы не используете SQL INTERSECT или EXCEPT, часто достаточно от нашего партнера по JCG Лукаса Эдера в блоге JAVA, SQL и AND JOOQ . |