EXCEPT и INTERSECT для решения специфических задач

Когда речь заходит о работе с данными в SQL, многие сразу вспоминают о JOIN'ах, подзапросах и агрегатных функциях. Однако два оператора часто остаются в тени, хотя они способны значительно упростить сложную логику и оптимизировать производительность запросов. Это EXCEPT и INTERSECT.

Давайте рассмотрим несколько разных задач в которых использование данных операторов может быть эффективным.

EXCEPT — возвращает уникальные строки из первого запроса, которые отсутствуют во втором. Это аналог разности множеств в математике.
INTERSECT — возвращает уникальные строки, которые присутствуют в результатах обоих запросов. Это аналог пересечения множеств.

Синтаксис

sql

-- Базовый синтаксис
SELECT column1, column2, ...
FROM table1
EXCEPT | INTERSECT
SELECT column1, column2, ...
FROM table2;

-- С сортировкой
(SELECT ... ORDER BY ...)
EXCEPT | INTERSECT
(SELECT ... ORDER BY ...)
ORDER BY ...;

Важные правила:

количество и порядок столбцов в обоих SELECT должны совпадать
типы данных соответствующих столбцов должны быть совместимы
по умолчанию удаляются дубликаты (работает DISTINCT)
для включения дубликатов используйте EXCEPT ALL / INTERSECT ALL

Принцип работы на простых примерах

sql

select *
from (
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2)
except 
select 1 as col1, 1 as col2;

--результат - пустая таблица. Из верхнего запроса исключили все строки.

select *
from (
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2)
except all
select 1 as col1, 1 as col2;
--из верхнего запроса исключили только одну строку

text

col1|col2|
----+----+
   1|   1|
   1|   1|

sql

select *
from (
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2)
intersect
select *
from (
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2)
--получили пересечение уникальных строк

text

col1|col2|
----+----+
   1|   1|

sql

select *
from (
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2)
intersect all
select *
from (
	select 1 as col1, 1 as col2
	union all
	select 1 as col1, 1 as col2)
--получили пересечение всех строк

text

col1|col2|
----+----+
   1|   1|
   1|   1|

Обработка NULL

В стандарте SQL значение NULL не равно значению NULL и во множестве случаев будут считаться разными значениями. В PostgreSQL в операторах EXCEPT и INTERSECT значение NULL считается равным значению NULL при сравнении строк.

sql

select null 
intersect 
select null

text

?column?|
--------+
	null|

Задачи на множества и комбинаторику

Задача: Генерация и проверка комбинаций
Нужно в виде строки получить для каждого пользователя идентификаторы фильмов, которые он не брал в аренду.

sql

-- получим все комбинации пользователя - фильма
with all_combinations as (
    select *
    from (select customer_id from customer) t1
    cross join (select film_id from film) t2
),
-- получим фильмы, которые арендовали пользователи
tested_combinations as (
    select distinct r.customer_id, i.film_id
	from rental r
	join inventory i on r.inventory_id = i.inventory_id
),
-- получаем фильмы, которые пользователи не арендовали
diff_combinations as (
	select customer_id, film_id
	from all_combinations
	except
	select customer_id, film_id
	from tested_combinations)
select customer_id, string_agg(film_id::text, ', ')
from diff_combinations
group by customer_id
limit 5;

text

customer_id|string_agg                              |
-----------+----------------------------------------+
          1|1, 2, 4, 5, 6, 7, ... 66, 67, 68, 69 ...|
          2|1, 2, 3, 4, 5, 6, ... 64, 65, 66, 67 ...|
          3|1, 2, 3, 4, 5, 6, ... 64, 65, 66, 67 ...|
          4|1, 2, 3, 4, 5, 6, ... 69, 70, 71, 72 ...|
          5|1, 2, 3, 4, 5, 7, ... 69, 70, 71, 72 ...|

Задача: Реляционное деление.
Нужно понять, соответствуют ли первые буквы названий фильмов всему алфавиту или есть буквы на которые названия фильмов не начинаются.

sql

-- генерируем алфавит через ASCII
select chr(code) as letter
from generate_series(ascii('a'), ascii('z')) as code 
except
-- исключаем первые буквы фильмов из базы
select lower(left(title, 1))
from film;

text

letter|
------+
x     |

--делаем вывод, что в базе нет фильмов, начинающихся на "x"

Задачи на временные ряды и события

Задача: Поиск событий, происходящих одновременно
Нужно найти пользователей, у которых платеж по аренде был после взятия в аренду и один из предпочитаемых жанров является Music.

sql

-- находим пользователей, у которых платеж по аренде был после взятия в аренду
select customer_id
from payment p
where exists(
	select 1 from rental r where p.rental_id = r.rental_id and p.payment_date > r.rental_date)
intersect all
-- получаем пользователей с предпочитаемым жанром Music
select c.customer_id
from customer c, jsonb_array_elements_text(c.preferences->'profile'->'favorite_genres') genres
where genres = 'Music';

text

customer_id|rental_id|
-----------+---------+
        577|      291|

--Результат explain analyze:
HashSetOp Intersect All  (cost=510.99..1751.83 rows=599 width=8) (actual time=4.742..4.744 rows=1 loops=1)

Можно ли написать иначе? Да.

sql

select customer_id
from payment p
where exists(
	select 1 from rental r where p.rental_id = r.rental_id and p.payment_date > r.rental_date and 
	r.customer_id in (
		select c.customer_id
		from customer c, jsonb_array_elements_text(c.preferences->'profile'->'favorite_genres') genres
		where genres = 'Music'));

text

--Результат explain analyze:
Hash Semi Join  (cost=1539.82..1935.33 rows=5350 width=2) (actual time=4.164..4.235 rows=1 loops=1)

Важно: Производительность зависит от объема данных, статистики и выбранного плана выполнения. EXCEPT/INTERSECT не являются автоматически быстрее или медленнее JOIN/EXISTS.

Задача: Анализ пересекающихся интервалов
Найти все случаи, когда один и тот же диск был арендован в пересекающиеся периоды времени.

sql

-- сначала найдем все возможные пары аренд одного диска
with all_pairs as (
    select 
        r1.rental_id as rental_id_1,
        r2.rental_id as rental_id_2,
        r1.inventory_id,
        r1.rental_date as start_1,
        r1.return_date as end_1,
        r2.rental_date as start_2,
        r2.return_date as end_2
    from rental r1
    join rental r2 on r1.inventory_id = r2.inventory_id
        and r1.rental_id < r2.rental_id
),
-- найдем пары, которые точно не пересекаются
non_overlapping as (
    select *
    from all_pairs
    where end_1 <= start_2  -- первая аренда закончилась до начала второй
       or end_2 <= start_1  -- или вторая закончилась до начала первой
)
-- теперь из всех пар вычтем непересекающиеся
select *
from all_pairs
except
select *
from non_overlapping;

-- результат - пустая таблица
-- делаем вывод, что проблем в учете нет и диски выдаются корректно

Задачи на целостность данных и валидацию

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

sql

with recursive valid_tree as (
	-- начинаем с корневых узлов
	select *, 0 as level
	from "structure" s
	where s.parent_id = 0
	union all 
	select s.*, vt.level + 1 as level
	from valid_tree vt
	join "structure" s on vt.unit_id = s.parent_id)
-- находим узлы, которые не попали в валидное дерево
select unit_id, parent_id
from "structure" 
except 
select unit_id, parent_id
from valid_tree

-- результат - пустая таблица
-- делаем вывод, что все подразделения валидны структуре

Задача: Проверка ссылочной целостности
Нужно проверить корректность ссылочной целостности между разными сущностями и магазинами.

sql

-- получаем идентификаторы магазинов по каждой сущности
select *
from (
	select 'сотрудник', store_id
	from staff
	union all
	select 'пользователь', store_id
	from customer 
	union all
	select 'диск', store_id
	from inventory)
except 
--получаем совокупность типа сущности к идентификаторам магазинов
select id_type, store_id 
from store, unnest(array['сотрудник', 'пользователь', 'диск']) id_type

-- результат - пустая таблица
-- данные по ссылкам согласованы

Практические рекомендации и ограничения

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

сравнение наборов данных — вместо сложных JOIN с проверками на NULL
поиск расхождений — между эталонными и текущими данными
валидация целостности — между связанными таблицами
анализ изменений — во временных рядах данных
поиск общих элементов — в нескольких наборах

Когда НЕ использовать:

при разных структурах данных — столбцы должны совпадать
когда нужна частичное сравнение — только некоторые столбцы
при работе с очень большими наборами без индексов
когда важна производительность в реальном времени — иногда JOIN быстрее

Оптимизационные советы:

используйте покрывающие индексы на сравниваемых столбцах
ограничивайте объем данных WHERE условиями
анализируйте план выполнения для каждого конкретного случая
тестируйте с реальными объемами данных

Заключение

Это далеко не полный список специфических задач, где можно и нужно применять операторы EXCEPT и INTERSECT.

Ключевые преимущества:

упрощение сложной логики — вместо многоуровневых подзапросов и JOIN'ов
встроенная обработка NULL и дубликатов
потенциально лучшая производительность при правильном использовании индексов
более понятный и поддерживаемый код

EXCEPT и INTERSECT для решения специфических задач ​

Синтаксис ​

Принцип работы на простых примерах ​

Обработка NULL ​

Задачи на множества и комбинаторику ​

Задачи на временные ряды и события ​

Задачи на целостность данных и валидацию ​

Практические рекомендации и ограничения ​

Заключение ​

EXCEPT и INTERSECT для решения специфических задач

Синтаксис

Принцип работы на простых примерах

Обработка NULL

Задачи на множества и комбинаторику

Задачи на временные ряды и события

Задачи на целостность данных и валидацию

Практические рекомендации и ограничения

Заключение