Что такое 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.