Операции над множествами и сложные соединения

Школа хочет разослать одну и ту же рассылку и покупателям, и ученикам — нужен единый список адресов из двух таблиц. Директор спрашивает: «В каких городах у нас есть и ученики, и покупатели?», а отдел продаж ищет «покупателей, которые ни разу не брали электронику». На такие вопросы проще всего отвечать на языке множеств из математики: объединение, пересечение, разность. Для них в SQL есть специальные операторы, а несколько «необычных» видов соединений дополняют картину.

UNION и UNION ALL

UNION записывает результаты двух запросов друг под другом и убирает повторы, а UNION ALL их сохраняет. Правила: в обоих запросах должно быть одинаковое число столбцов совместимых типов; имена столбцов берутся из первого запроса; ORDER BY пишется один раз, в самом конце, и относится ко всему результату.

SELECT
  (SELECT COUNT(*) FROM (SELECT city FROM students
                         UNION
                         SELECT city FROM customers)) AS with_union,
  (SELECT COUNT(*) FROM (SELECT city FROM students
                         UNION ALL
                         SELECT city FROM customers)) AS with_union_all;

with_union | with_union_all
11 | 19

SELECT 'student' AS type, first_name AS name, city
FROM students
WHERE city = 'Bakı'
UNION ALL
SELECT 'customer', name, city
FROM customers
WHERE city = 'Bakı'
ORDER BY type, name;

type | name | city
customer | Anar Mustafayev | Bakı
customer | Zəhra Hüseynli | Bakı
student | Aysel | Bakı
student | Kamran | Bakı
student | Leyla | Bakı
student | Rəşad | Bakı

INTERSECT и EXCEPT

INTERSECT возвращает строки, которые есть в обоих результатах (пересечение), а EXCEPT — строки, которые есть в первом результате, но отсутствуют во втором (разность). Оба убирают повторы. Ниже мы находим города, где есть и ученики, и покупатели, а затем города, где живут только ученики.

SELECT city FROM students
INTERSECT
SELECT city FROM customers
ORDER BY city;

city
Bakı
Gəncə

SELECT city FROM students
EXCEPT
SELECT city FROM customers
ORDER BY city;

city
Lənkəran
Naxçıvan
Quba
Sumqayıt
Şəki

Самосоединение и CROSS JOIN

При самосоединении одна и та же таблица используется дважды под двумя разными псевдонимами — как будто у неё есть две копии. Например, мы хотим познакомить учеников из одного города попарно. Условие a.city = b.city сопоставляет города, а a.id < b.id оставляет каждую пару только один раз.

SELECT a.first_name AS student_1,
       b.first_name AS student_2,
       a.city
FROM students AS a
JOIN students AS b ON b.city = a.city AND a.id < b.id
ORDER BY a.city, a.id, b.id;

student_1 | student_2 | city
Aysel | Leyla | Bakı
Aysel | Rəşad | Bakı
Aysel | Kamran | Bakı
Leyla | Rəşad | Bakı
Leyla | Kamran | Bakı
Rəşad | Kamran | Bakı
Murad | Tural | Gəncə
Elvin | Orxan | Sumqayıt

CROSS JOIN намеренно строит все комбинации. Обычно это ошибка, но иногда именно то, что нужно: построив сетку «каждый ученик × каждый курс» и присоединив к ней через LEFT JOIN реальные зачисления, мы видим, кто куда не записан. Ниже такая матрица для восьмиклассников и курсов математики: NULL — зачисления нет.

SELECT s.first_name, c.title, e.score
FROM students AS s
CROSS JOIN courses AS c
LEFT JOIN enrollments AS e
       ON e.student_id = s.id AND e.course_id = c.id
WHERE s.grade = 8 AND c.subject = 'Math'
ORDER BY s.id, c.id;

first_name | title | score
Leyla | Algebra | NULL
Leyla | Geometry | 95
Günay | Algebra | NULL
Günay | Geometry | NULL
Orxan | Algebra | NULL
Orxan | Geometry | 70

Антисоединение: NOT EXISTS

«Покупатели, которые ни разу не брали электронику» — это вопрос для антисоединения: оставляем строки левой таблицы, у которых нет пары в правой. Надёжнее всего записать его через NOT EXISTS: подзапрос для каждого покупателя спрашивает «есть ли заказ электроники?», и если ответ «нет», покупатель попадает в результат.

SELECT c.name, c.country
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Electronics'
)
ORDER BY c.name;

name | country
John Carter | United Kingdom
Mehmet Kaya | Türkiye
Olga Ivanova | Russia

Обратный вопрос — «кто хотя бы раз купил электронику» — записывают через EXISTS (полусоединение). Если написать его обычным JOIN, Анар, у которого два заказа электроники, появится дважды; EXISTS возвращает каждого покупателя один раз, потому что прекращает поиск, как только нашёл пару.

SELECT c.title
FROM courses AS c
JOIN enrollments AS e ON e.course_id = c.id
JOIN students AS s ON s.id = e.student_id
WHERE s.city = 'Bakı'
-- INTERSECT the same query for 'Sumqayıt'
ORDER BY title;

title
Geometry
Mechanics

SELECT s.first_name
FROM students AS s
WHERE NOT EXISTS (
  -- an enrollment of this student in a Math course
)
ORDER BY s.first_name;

first_name
Elvin
Günay
Kamran
Rəşad
Tural