Для чего нужен EXCEPT/MINUS в SQL?

Для чего нужен EXCEPT/MINUS в SQL?

EXCEPT (или MINUS в Oracle и некоторых других СУБД) — это оператор теории множеств в SQL, который возвращает строки, которые присутствуют в первом запросе, но отсутствуют во втором запросе. Это операция вычитания множеств.

Синтаксис

SELECT column1, column2, ... FROM table1
EXCEPT
SELECT column1, column2, ... FROM table2;

В Oracle и некоторых других СУБД используется ключевое слово MINUS:

SELECT column1, column2, ... FROM table1
MINUS
SELECT column1, column2, ... FROM table2;

Как это работает

Оператор EXCEPT работает на уровне множеств:

Первый запрос:  {1, 2, 3, 4, 5}
Второй запрос:  {3, 4, 5, 6, 7}
Результат:      {1, 2}

Важные свойства:

• Удаляет дубликаты — если в первом запросе есть одинаковые строки, они будут дедублицированы
• Строгое совпадение — строки сравниваются полностью
• Порядок — результаты отсортированы, как в теории множеств

Практические примеры

Пример 1: Найти пользователей без заказов

SELECT id, email FROM users
EXCEPT
SELECT DISTINCT user_id, NULL FROM orders;

Найти всех пользователей, которые когда-либо размещали заказы:

SELECT id FROM users
EXCEPT
SELECT user_id FROM orders WHERE status = cancelled;

Пример 2: Найти данные, отличающиеся между двумя версиями таблицы

-- Данные, которые есть в текущей версии, но не было в старой
SELECT id, name, email FROM users_current
EXCEPT
SELECT id, name, email FROM users_backup;

Пример 3: Найти продукты, которые есть в наличии, но не продавались

SELECT product_id FROM products WHERE in_stock = true
EXCEPT
SELECT DISTINCT product_id FROM sales;

Использование в Java с использованием ORM

// С использованием Hibernate
public List<User> getUsersWithoutOrders() {
    String hql = "SELECT DISTINCT u FROM User u " +
                 "WHERE u.id NOT IN (SELECT o.userId FROM Order o)";
    return session.createQuery(hql, User.class).list();
}

// С использованием Spring Data JPA
@Query(value = "SELECT * FROM users " +
               "EXCEPT " +
               "SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id",
       nativeQuery = true)
List<User> getUsersWithoutOrders();

EXCEPT vs NOT IN vs LEFT JOIN

Есть несколько способов достичь похожего результата:

-- Способ 1: EXCEPT (чистый, читаемый)
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

-- Способ 2: NOT IN (может быть медленным с NULL)
SELECT id FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

-- Способ 3: LEFT JOIN (обычно самый быстрый)
SELECT u.id FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

Различия:

• EXCEPT — явно выражает логику вычитания множеств, очень читаемо
• NOT IN — может пропустить результаты если есть NULL значения
• LEFT JOIN — обычно лучше оптимизируется планировщиком запросов

Полезные операции с множествами

-- EXCEPT — только в первом (A - B)
SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;

-- INTERSECT — общие элементы (A ∩ B)
SELECT id FROM table_a
INTERSECT
SELECT id FROM table_b;

-- UNION — все элементы (A ∪ B)
SELECT id FROM table_a
UNION
SELECT id FROM table_b;

-- UNION ALL — все элементы с дубликатами
SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b;

Когда использовать EXCEPT

1. Аналитические запросы — сравнение наборов данных
2. Data reconciliation — проверка различий между таблицами
3. Поиск отсутствующих данных — более читаемо чем NOT IN
4. Миграции данных — валидация переноса

Производительность

Оператор EXCEPT требует:

• Сортировки обоих наборов результатов
• Дедубликации
• Сравнения

Для больших датасетов может быть медленнее LEFT JOIN. Всегда проверяйте план выполнения (EXPLAIN).