EXCEPT: примеры (SQL)

Базовые примеры применения EXCEPT

Пример 1: Сравнение двух простых наборов данных
Поиск числовых значений, которые есть в первом наборе, но отсутствуют во втором.

SELECT 1 AS Num
UNION ALL SELECT 2
UNION ALL SELECT 3
EXCEPT
SELECT 2 AS Number
UNION ALL SELECT 4;

Num
---
1
3

Пример 2: Использование с несколькими столбцами
Сравнение пар значений (например, для выявления отсутствующих комбинаций сотрудник-проект).

SELECT 'Иванов' AS LastName, 'Проект А' AS Project
UNION ALL SELECT 'Петров', 'Проект Б'
EXCEPT
SELECT 'Петров' AS Surname, 'Проект Б' AS Work
UNION ALL SELECT 'Сидоров', 'Проект А';

LastName Project
-------- ---------
Иванов   Проект А

Пример 3: Использование ORDER BY
Результаты оператора EXCEPT можно отсортировать.

SELECT ProductID, Name FROM Production.Product WHERE Color = 'Red'
EXCEPT
SELECT ProductID, Name FROM Production.Product WHERE ListPrice > 1000
ORDER BY Name;

ProductID  Name
---------  ------------------------------
322        Red Bicycle Frame
712        Red Sport Helmet

Похожие операторы и методы в MS SQL

NOT EXISTS / NOT IN с подзапросом: Альтернативный способ найти записи из одной таблицы, отсутствующие в другой. Зачастую демонстрирует схожую производительность, но логика работы отличается (EXCEPT работает с наборами, а NOT EXISTS — с условиями корреляции). NOT IN может вести себя неожиданно при наличии NULL значений в правой части.

OUTER JOIN с проверкой на NULL: Использование LEFT JOIN с условием WHERE ... IS NULL для правой таблицы. Это классический метод эмуляции EXCEPT, особенно гибкий, когда нужно сравнить не все столбцы или добавить дополнительные условия соединения.

Выбор между EXCEPT и альтернативами: Оператор EXCEPT предпочтителен, когда нужно сравнить два сложных набора по всем столбцам, и важна читаемость и лаконичность запроса. NOT EXISTS часто более эффективен, если требуется коррелированный подзапрос или проверка по ключевым полям. OUTER JOIN дает больше контроля над процессом соединения и позволяет легко включать дополнительные столбцы из обоих наборов в результат.

Распространенные ошибки

1. Несовпадение количества или типов столбцов. Оба запроса должны возвращать одинаковое число столбцов с совместимыми типами.
Ошибочный пример:

SELECT ID, Name FROM Table1
EXCEPT
SELECT ID FROM Table2; -- Разное количество столбцов

2. Некорректное использование ORDER BY. Предложение ORDER BY может находиться только в конце всего составного запроса.
Ошибочный пример:

SELECT A FROM T1 ORDER BY A
EXCEPT
SELECT B FROM T2;

3. Игнорирование обработки NULL-значений. При сравнении строк NULL считается равным NULL. Это может повлиять на логику вычитания, если в данных есть NULL.
Пример:

SELECT NULL AS Col
EXCEPT
SELECT NULL AS Column;

Col
---

Изменения в последних версиях

В актуальных версиях Microsoft SQL Server (2012 и новее) значительных изменений в синтаксисе или поведении оператора EXCEPT не было. Основная эволюция связана с улучшением оптимизатора запросов, который стал эффективнее строить планы выполнения для запросов с операторами множеств. Для EXCEPT и INTERSECT были реализованы более совершенные стратегии хеширования и слияния, что улучшило производительность при работе с большими объемами данных. Рекомендуется использовать последние доступные накопительные обновления для получения улучшений в работе компонента обработки запросов.

Расширенные примеры

Пример 1: Поиск устаревших или удаленных записей в истории изменений.
Найти товары, которые присутствовали в каталоге на прошлой неделе, но отсутствуют сейчас.

-- Предполагаем, что есть ежедневные снимки каталога
SELECT ProductID, ProductName FROM dbo.ProductSnapshot WHERE SnapshotDate = '2023-11-10'
EXCEPT
SELECT ProductID, ProductName FROM dbo.ProductSnapshot WHERE SnapshotDate = '2023-11-17'
ORDER BY ProductName;

Пример 2: Проверка целостности данных между связанными таблицами.
Выявить ID подразделений, которые заявлены в таблице сотрудников, но отсутствуют в справочнике подразделений (возможная ошибка ввода данных).

SELECT DISTINCT DepartmentID FROM dbo.Employees
EXCEPT
SELECT DepartmentID FROM dbo.Departments;
-- Если результат не пуст, найдены некорректные ссылки

Пример 3: Каскадное применение EXCEPT для сравнения нескольких версий данных.
Сравнить состояние таблицы на три разных даты, чтобы увидеть поэтапные изменения.

-- Элементы, были в v1, но исчезли к v3
(SELECT KeyCol FROM dbo.Table_v1
 EXCEPT
 SELECT KeyCol FROM dbo.Table_v2)
UNION
(SELECT KeyCol FROM dbo.Table_v2
 EXCEPT
 SELECT KeyCol FROM dbo.Table_v3);

Пример 4: Использование EXCEPT для отладки сложных запросов.
Сравнить результат старой (работающей) и новой (отлаживаемой) версии запроса, чтобы найти расхождения.

-- Результаты новой версии, которых нет в старой (возможная ошибка)
(SELECT * FROM ( ... новый сложный запрос ... ) AS NewQuery
 EXCEPT
 SELECT * FROM ( ... старый проверенный запрос ... ) AS OldQuery)
UNION ALL -- Объединяем с обратным сравнением для полноты картины
(SELECT * FROM OldQuery
 EXCEPT
 SELECT * FROM NewQuery);

Аналоги EXCEPT в других СУБД

PostgreSQL, SQLite: Полностью поддерживают оператор EXCEPT с идентичным SQL Server синтаксисом и поведением.

Oracle: Использует ключевое слово MINUS вместо EXCEPT. Функциональность и синтаксис аналогичны.
Пример:

-- Oracle
SELECT employee_id FROM employees WHERE department_id = 10
MINUS
SELECT employee_id FROM job_history;

MySQL: Не имеет прямого аналога EXCEPT. Используют комбинацию NOT EXISTS или NOT IN.
Пример эмуляции через NOT EXISTS:

-- MySQL
SELECT a.id FROM table_a a
WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE b.id = a.id);

Sybase ASE: Прямого оператора EXCEPT нет. Альтернатива — использование оператора SET DIFFERENCE в некоторых контекстах или тех же NOT EXISTS / OUTER JOIN.