EXCEPT

Поиск строк, которые есть в первом SELECT-запросе, но отсутствуют во втором

Теория

Оператор EXCEPT нужен, когда из результата первого SELECT-запроса нужно убрать все строки, которые встречаются во втором запросе. Каждый запрос сначала возвращает свой набор строк, а потом PostgreSQL берёт строки из первого результата и вычитает из него строки второго, то что осталось возвращает. Эту операцию часто называют операцией разности запросов, а сам оператор EXCEPT называют оператором исключения.

EXCEPT отвечает на вопрос: что есть в первой выборке, но отсутствует во второй. Или так: исключим из результата первого запроса то, что есть и во втором. Если вспомнить про INTERSECT, который выводит совпадающие строки из первого и второго запроса, то EXCEPT делает противоположную операцию.

Это удобно, когда нужно найти разницу между двумя наборами данных. Например, можно взять список городов из таблицы users, затем список городов из таблицы departments и оставить только те города, которые есть в таблице users, но не встречаются в departments. Или можно сравнить две выборки из одной таблицы и посмотреть, какие строки подходят под первое условие, но уже не подходят под второе. Например, определить пользователей, которые совершали покупки в апреле 2023 года, но не совершали в мае 2023 года.

Обычный EXCEPT убирает полные дубликаты строк. Поэтому если одна и та же строка несколько раз встречается в первой выборке, а потом не попадает во вторую, в результате через EXCEPT она всё равно останется один раз.

В PostgreSQL есть и вариант EXCEPT ALL. Он тоже вычитает строки второй выборки из первой, но делает это с учётом количества повторов. Для базового уровня обычно достаточно понимать обычный EXCEPT, но знать про вариант с ALL полезно.

У этой операции те же базовые ограничения, что и у UNION и INTERSECT:
• количество столбцов в обоих запросах должно совпадать;
• типы данных в соответствующих столбцах должны совпадать или быть совместимыми;
• PostgreSQL сопоставляет столбцы по позиции, а не по имени.

Синтаксис

SELECT column1, column2
FROM table_1
EXCEPT
SELECT column1, column2
FROM table_2;

SELECT column1
FROM table_1
EXCEPT ALL
SELECT column1
FROM table_2
ORDER BY column1;

SQL

Каждая часть конструкции EXCEPT — это обычный SELECT-запрос. Сначала пишется первый запрос, затем оператор EXCEPT, потом второй запрос.

PostgreSQL сравнивает столбцы по позиции:
• первый столбец первого запроса — с первым столбцом второго;
• второй — со вторым;
• третий — с третьим.

В обоих запросах должно быть одинаковое количество столбцов, в одинаковом порядке и с совместимыми типами данных.

Итоговые имена столбцов PostgreSQL берёт из первой части. Поэтому если вы хотите дать столбцу понятное имя или потом использовать внешний ORDER BY, псевдоним необходимо задавать именно в первом запросе.

Если типы данных не совпадают, их можно привести явно. Например, запись worker_id::text позволяет сравнивать числовой идентификатор с текстовым значением во второй части запроса.

Если количество столбцов не совпадает, запрос не выполнится. При необходимости структуру можно выровнять вручную: добавить NULL AS some_column или константу в ту часть, где столбца не хватает.

WHERE, вычисления, функции и псевдонимы пишутся отдельно внутри каждого SELECT. А вот ORDER BY и LIMIT обычно ставят в конце всей конструкции, когда разность между выборками уже построена.

Если сортировка или LIMIT нужны именно внутри отдельной части запроса до вычитания, эту часть нужно обернуть в скобки. Иначе PostgreSQL вернёт ошибку.

Примеры

1. Ищем имена сотрудников, которые отсутствуют у пользователей

SELECT first_name AS person_name
FROM workers
EXCEPT
SELECT first_name
FROM users
ORDER BY person_name;

Сначала PostgreSQL собирает все имена из users, затем убирает из этого списка те имена, которые встречаются в workers. В результате получаем все имена, которые есть в первом запросе, но нет во втором.

🔄 Попробуйте изменить запрос:

• Замените EXCEPT на INTERSECT и сравните, как полностью меняется смысл результата
• Теперь попробуйте через INTERSECT. Объясните разницу поведений между всеми операторами

2. Оставляем дорогие товары, но убираем смартфоны

SELECT product_name AS item_name
FROM products
WHERE price >= 100000
EXCEPT
SELECT product_name
FROM products
WHERE category = 'Смартфоны'
ORDER BY item_name;

Первая выборка возвращает дорогие товары. Вторая — товары категории 'Смартфоны'. После EXCEPT останутся дорогие товары, которые не относятся к смартфонам.

🔄 Попробуйте изменить запрос:

• Поменяйте порог цены на price >= 80000 и проверьте, как изменится результат
• Замените категорию 'Смартфоны' на другую и сравните разность

3. Ищем сотрудников на менеджерских должностях, которые не оформляли заказы

SELECT worker_id
FROM workers
WHERE position ILIKE '%менеджер%'
EXCEPT
SELECT worker_id
FROM orders
ORDER BY worker_id;

Первая выборка возвращает worker_id сотрудников из таблицы workers, у которых в названии должности есть слово 'менеджер'. Вторая — worker_id сотрудников, которые встречаются в таблице orders как оформлявшие заказы. После EXCEPT останутся только те менеджеры, не оформляли заказы, то есть менеджеры, отсутствующие в таблице orders.

🔄 Попробуйте изменить запрос:

• Замените фильтр ILIKE '%менеджер%' на ILIKE '%продаж%' и сравните, как изменится список worker_id
• Замените EXCEPT на INTERSECT и посмотрите, какие менеджеры, наоборот, оформляли заказы

4. Ищем клиентов, которые заказывали в апреле, но не заказывали в мае

SELECT user_id
FROM orders
WHERE order_date >= '2023-04-01 00:00:00'::timestamp
  AND order_date < '2023-05-01 00:00:00'::timestamp
EXCEPT
SELECT user_id
FROM orders
WHERE order_date >= '2023-05-01 00:00:00'::timestamp
  AND order_date < '2023-06-01 00:00:00'::timestamp
ORDER BY user_id;

Первая выборка выводит клиентов, которые совершали заказы в апреле 2023 года. Вторая — клиентов, которые совершали заказы в мае 2023 года. После EXCEPT останутся только те user_id, которые совершали заказы в апреле, а в мае не совершали.

🔄 Попробуйте изменить запрос:

• Сдвиньте первый период на март 2023 года и сравните новый список user_id с апрельским результатом
• Замените EXCEPT на INTERSECT и посмотрите, какой user_id встречался и в апреле, и в мае 2023 года

Типичные ошибки

Путают EXCEPT и INTERSECT
EXCEPT не ищет общее между двумя выборками. Он оставляет строки первой выборки и убирает из неё всё, что встретилось во второй.

Думают, что порядок запросов не важен
В EXCEPT это критично. Сначала идёт набор, из которого вычитаем, потом набор, который вычитается. Если поменять их местами, смысл результата станет другим.

Ждут, что совпадения только по одному столбцу достаточно
Если в запросе участвуют два или три столбца, PostgreSQL сравнивает всю строку целиком по позициям. Совпадения только по одному полю недостаточно.

Возвращают разное количество столбцов
Если в одной части запроса два столбца, а в другой один, EXCEPT не выполнится. Структура результата должна совпадать.

Ставят ORDER BY внутри части без скобок
По умолчанию сортировка ставится в конце всей конструкции. Если нужно сортировать и ограничивать отдельную часть до вычитания, её нужно обернуть в скобки.

Путают EXCEPT и EXCEPT ALL
Обычный EXCEPT убирает полные дубликаты строк. EXCEPT ALL тоже вычитает вторую выборку из первой, но работает с повторами иначе.

EXCEPT

Теория

Синтаксис

Примеры

1. Ищем имена сотрудников, которые отсутствуют у пользователей

🔄 Попробуйте изменить запрос:

2. Оставляем дорогие товары, но убираем смартфоны

🔄 Попробуйте изменить запрос:

3. Ищем сотрудников на менеджерских должностях, которые не оформляли заказы

🔄 Попробуйте изменить запрос:

4. Ищем клиентов, которые заказывали в апреле, но не заказывали в мае

🔄 Попробуйте изменить запрос:

Типичные ошибки

Практика

Проверь себя