re:re:actable
Урок 063 минуты чтения

Что такое INTERSECT и EXCEPT

На этой странице
Кратко

INTERSECT возвращает строки, которые есть в обоих результатах (пересечение множеств), EXCEPT — строки первого результата, которых нет во втором (разность; в Oracle — MINUS). По умолчанию обе операции удаляют дубли, варианты INTERSECT ALL / EXCEPT ALL учитывают количество повторов. Сравнение идёт по всем столбцам строки, причём NULL считаются равными (в отличие от =). Требования те же, что у UNION: одинаковое число столбцов и совместимые типы. Частые применения — сверка данных (что не доехало при миграции), «есть в А, но нет в Б» по набору столбцов. Для связи по ключу с выводом других столбцов обычно удобнее EXISTS / NOT EXISTS.

Вопрос

Что такое INTERSECT и EXCEPT и когда их использовать?

INTERSECT

-- пользователи, которые и покупали, и оставляли отзывы
SELECT user_id FROM orders
INTERSECT
SELECT user_id FROM reviews;

EXCEPT

-- зарегистрировались, но ни разу не покупали
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

Порядок важен: A EXCEPT B ≠ B EXCEPT A.

Сверка данных после миграции

-- строки, которые есть в старой таблице, но отличаются или отсутствуют в новой
SELECT id, email, status FROM legacy_users
EXCEPT
SELECT id, email, status FROM users;
 
-- и в обратную сторону
SELECT id, email, status FROM users
EXCEPT
SELECT id, email, status FROM legacy_users;

Пустые результаты обеих проверок — наборы совпадают. Сравнение по всем столбцам и с учётом NULL делает EXCEPT удобнее, чем JOIN с длинным условием IS DISTINCT FROM.

ALL-варианты

A: 1, 1, 2        B: 1
A EXCEPT B     → 2
A EXCEPT ALL B → 1, 2      (один из двух 1 «вычтен»)

Сравнение с EXISTS

ЗадачаУдобнее
Сравнить наборы по всем столбцамINTERSECT / EXCEPT
Вывести другие столбцы строки (email, name)EXISTS / NOT EXISTS
Учесть NULL как равныеEXCEPT (или IS NOT DISTINCT FROM)
-- нужны email и имя — EXCEPT по одному id не подойдёт
SELECT u.id, u.email FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Приоритет операций

INTERSECT связывает сильнее, чем UNION и EXCEPT: A UNION B INTERSECT C = A UNION (B INTERSECT C). Используйте скобки.

Частые ошибки

  • Перепутать порядок операндов в EXCEPT.
  • Ожидать сохранения дублей без ALL.
  • Использовать EXCEPT, когда нужны столбцы, не участвующие в сравнении.
  • Забыть, что в MySQL INTERSECT/EXCEPT появились только в 8.0.31.

Что важно помнить

  • INTERSECT — общее, EXCEPT — разность.
  • Сравниваются все столбцы, NULL равны друг другу.
  • Отличный инструмент сверки двух наборов.

Мини-шпаргалка

  • q1 INTERSECT q2.
  • q1 EXCEPT q2.
  • INTERSECT ALL, EXCEPT ALL.
  • Сверка: A EXCEPT B и B EXCEPT A.

Документация

PostgreSQL: Combining Queries.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам