EXCEPT


📖 Описание

EXCEPT вычисляет разность результатов двух запросов. Обычный EXCEPT удаляет повторы. Направление важно: «план минус факт» отличается от «факт минус план».

Oracle: в версии 21c EXCEPT является синонимом MINUS. Для Oracle 19c используйте MINUS. Операция над результатами SELECT отличается от MULTISET EXCEPT над коллекциями PL/SQL.


🧩 Синтаксис

SELECT column_1, column_2 FROM first_source
EXCEPT
SELECT column_1, column_2 FROM second_source;

Количество столбцов должно совпадать; сравнение идёт по позициям и по всей выбранной строке. Типы соответствующих столбцов должны быть совместимы. Итоговый ORDER BY ставится после составного запроса.


💻 Кто записался, но не пришёл

В план попали посетители 71, 72, 72, 73 и неизвестный ID (NULL); фактически пришли 72, 74 и неизвестный ID. Повтор 72 намеренно оставлен для сравнения вариантов.

WITH planned (attendee_id) AS (
  SELECT 71 FROM dual
  UNION ALL SELECT 72 FROM dual
  UNION ALL SELECT 72 FROM dual
  UNION ALL SELECT 73 FROM dual
  UNION ALL SELECT NULL FROM dual
),
arrived (attendee_id) AS (
  SELECT 72 FROM dual
  UNION ALL SELECT 74 FROM dual
  UNION ALL SELECT NULL FROM dual
)
SELECT attendee_id FROM planned
EXCEPT
SELECT attendee_id FROM arrived
ORDER BY attendee_id;
ATTENDEE_ID
71
73

Это самостоятельный пример для Oracle 21c+. В Oracle 19c замените EXCEPT на MINUS. При операциях над множествами NULL в обеих выборках считается совпадающим для устранения строк. Это отличается от предиката NULL = NULL.


EXCEPT ALL и повторы

Oracle 21c поддерживает EXCEPT ALL. Для каждого значения он вычитает количество вхождений справа из количества слева, оставляя не меньше нуля. После того же WITH:

SELECT attendee_id FROM planned
EXCEPT ALL
SELECT attendee_id FROM arrived
ORDER BY attendee_id;
ATTENDEE_ID
71
72
73

Одна из двух записей 72 осталась. Не переносите суффикс ALL в Oracle 19c: там этот вариант не поддерживается.


Сравнение с NOT EXISTS

Вариант ниже сохраняет повторные строки левого источника. Чтобы сравнение NULL соответствовало предыдущему примеру, равенство дополнено явной проверкой обоих NULL:

WITH planned (attendee_id) AS (
  SELECT 71 FROM dual
  UNION ALL SELECT 72 FROM dual
  UNION ALL SELECT 72 FROM dual
  UNION ALL SELECT 73 FROM dual
  UNION ALL SELECT NULL FROM dual
),
arrived (attendee_id) AS (
  SELECT 72 FROM dual
  UNION ALL SELECT 74 FROM dual
  UNION ALL SELECT NULL FROM dual
)
SELECT p.attendee_id
FROM planned p
WHERE NOT EXISTS (
  SELECT 1 FROM arrived a
  WHERE a.attendee_id = p.attendee_id
     OR (a.attendee_id IS NULL AND p.attendee_id IS NULL)
)
ORDER BY p.attendee_id;
ATTENDEE_ID
71
73

На этих данных набор совпал с EXCEPT, но это не общее тождество: два отсутствующих справа значения 71 были бы сохранены дважды. NOT IN с NULL в подзапросе также не является безопасной механической заменой.


Практическое упражнение

Найдите посетителей, которые пришли, но отсутствовали в плане. Используйте MINUS для Oracle 19c и тот же блок WITH.

Решение

SELECT attendee_id FROM arrived
MINUS
SELECT attendee_id FROM planned
ORDER BY attendee_id;
ATTENDEE_ID
74

Читайте также

MINUS · INTERSECT · UNION · EXISTS · FULL JOIN


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

Oracle 21c: операции над множествами · Oracle 19c: MINUS