EXCEPT

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

Теория

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

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

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

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

В PostgreSQL есть и вариант 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 берёт из первой части. Поэтому, если вы хотите дать столбцу понятное имя, псевдоним необходимо задавать именно в первом запросе.

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

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. В итоге остаются только те имена, которые есть в users, но отсутствуют в workers.

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

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

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 останутся товары дороже 100000, которые не относятся к смартфонам.

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

• Поменяйте порог цены на 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. Ищем клиентов, которые заказывали в апреле, но не заказывали в мае

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

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

Практика

Проверь себя