Пошук уроків, статей та іншого контенту
Знаходьте перетин і різницю наборів даних за допомогою INTERSECT та EXCEPT.
INTERSECT та EXCEPTINTERSECT і EXCEPT працюють із результатами двох запитів як із множинами:
INTERSECT повертає рядки, які є в обох результатах;
EXCEPT повертає рядки з першого результату, яких немає в другому.
Загальний синтаксис:
SELECT ...
FROM ...
INTERSECT
SELECT ...
FROM ...;SELECT ...
FROM ...
EXCEPT
SELECT ...
FROM ...;На відміну від JOIN, ці оператори порівнюють саме результати запитів, а не поєднують стовпці з таблиць.
INTERSECT: перетин наборівПрипустімо, потрібно знайти клієнтів, які робили замовлення і у 2024, і у 2025 році.
WITH orders_2024(customer_id) AS (
VALUES
(1),
(2),
(3),
(4)
),
orders_2025(customer_id) AS (
VALUES
(2),
(3),
(5),
(6)
)
SELECT customer_id
FROM orders_2024
INTERSECT
SELECT customer_id
FROM orders_2025
ORDER BY customer_id;Результат:
customer_id
-------------
2
3Клієнти з ідентифікаторами 2 і 3 присутні в обох наборах.
За замовчуванням INTERSECT видаляє дублікати. Тобто результат поводиться як множина, а не як звичайний список рядків.
SELECT customer_id
FROM (
VALUES (1), (1), (2)
) AS first_set(customer_id)
INTERSECT
SELECT customer_id
FROM (
VALUES (1), (1), (3)
) AS second_set(customer_id);Результат:
customer_id
-------------
1Значення 1 повертається один раз, хоча воно повторюється в обох наборах.
EXCEPT: різниця наборівEXCEPT повертає рядки, які є в першому запиті, але відсутні в другому.
Знайдемо клієнтів, які робили замовлення у 2024 році, але не робили їх у 2025 році:
WITH orders_2024(customer_id) AS (
VALUES
(1),
(2),
(3),
(4)
),
orders_2025(customer_id) AS (
VALUES
(2),
(3),
(5),
(6)
)
SELECT customer_id
FROM orders_2024
EXCEPT
SELECT customer_id
FROM orders_2025
ORDER BY customer_id;Результат:
customer_id
-------------
1
4Порядок має значення:
A EXCEPT Bне є тим самим, що:
B EXCEPT AНаприклад:
SELECT customer_id
FROM (
VALUES (1), (2), (3)
) AS first_set(customer_id)
EXCEPT
SELECT customer_id
FROM (
VALUES (2), (3), (4)
) AS second_set(customer_id);Результат:
customer_id
-------------
1А якщо поміняти набори місцями:
SELECT customer_id
FROM (
VALUES (2), (3), (4)
) AS first_set(customer_id)
EXCEPT
SELECT customer_id
FROM (
VALUES (1), (2), (3)
) AS second_set(customer_id);Результат:
customer_id
-------------
4Запити по обидва боки від INTERSECT або EXCEPT повинні мати:
однакову кількість стовпців;
сумісні типи даних у відповідних стовпцях;
однаковий порядок стовпців для порівняння.
Наприклад, цей запит є коректним:
SELECT id, email
FROM customers_active
INTERSECT
SELECT id, email
FROM customers_subscribed;В обох запитах повертаються два стовпці: id та email.
А цей запит некоректний:
SELECT id, email
FROM customers_active
INTERSECT
SELECT id
FROM customers_subscribed;Перший запит повертає два стовпці, а другий — лише один.
Порівнюються цілі рядки результату. Якщо хоча б одне значення відрізняється, рядки вважаються різними:
SELECT id, status
FROM (
VALUES
(1, 'active'),
(2, 'active')
) AS first_set(id, status)
INTERSECT
SELECT id, status
FROM (
VALUES
(1, 'active'),
(2, 'blocked')
) AS second_set(id, status);Результат:
id | status
----+--------
1 | activeРядок (2, 'active') не збігається з (2, 'blocked'), тому він не потрапляє до результату.
INTERSECT ALL та EXCEPT ALLЗа замовчуванням INTERSECT та EXCEPT працюють без дублікатів. У PostgreSQL можна явно зберегти повторення за допомогою ALL.
INTERSECT ALLINTERSECT ALL повертає значення стільки разів, скільки воно зустрічається в обох наборах. Кількість повторень дорівнює меншій кількості повторень у кожному наборі.
SELECT value
FROM (
VALUES (1), (1), (1), (2)
) AS first_set(value)
INTERSECT ALL
SELECT value
FROM (
VALUES (1), (1), (3)
) AS second_set(value)
ORDER BY value;Результат:
value
-------
1
1У першому наборі 1 зустрічається тричі, у другому — двічі, тому результат містить два рядки.
EXCEPT ALLEXCEPT ALL віднімає кількість повторень другого набору від кількості повторень першого:
SELECT value
FROM (
VALUES (1), (1), (1), (2)
) AS first_set(value)
EXCEPT ALL
SELECT value
FROM (
VALUES (1), (1), (3)
) AS second_set(value)
ORDER BY value;Результат:
value
-------
1
2Два значення 1 були виключені другим набором, але одне залишилося.
У більшості прикладних запитів достатньо звичайних INTERSECT та EXCEPT. Версії з ALL потрібні, коли кількість повторень має значення.
Оператори порівнюють не окремий стовпець, а весь рядок.
Наприклад, можна знайти пари product_id і warehouse_id, які зустрічаються в обох наборах:
WITH first_inventory(product_id, warehouse_id) AS (
VALUES
(101, 1),
(102, 1),
(103, 2)
),
second_inventory(product_id, warehouse_id) AS (
VALUES
(101, 1),
(102, 2),
(104, 1)
)
SELECT product_id, warehouse_id
FROM first_inventory
INTERSECT
SELECT product_id, warehouse_id
FROM second_inventory
ORDER BY product_id;Результат:
product_id | warehouse_id
------------+--------------
101 | 1Пара (102, 1) не збігається з (102, 2), оскільки значення warehouse_id різне.
ORDER BY у запитах із INTERSECT та EXCEPTЯкщо потрібно впорядкувати весь результат, ORDER BY зазвичай розміщують у кінці:
SELECT id
FROM customers_active
INTERSECT
SELECT id
FROM customers_subscribed
ORDER BY id;ORDER BY не змінює логіку перетину або різниці — він лише впорядковує вже отриманий результат.
Якщо потрібно спочатку обмежити або впорядкувати окрему частину складнішого виразу, використовуйте дужки:
(
SELECT id
FROM customers_active
ORDER BY id
LIMIT 10
)
EXCEPT
SELECT id
FROM blocked_customers;Для простих запитів достатньо розміщувати ORDER BY в кінці всього виразу.
INTERSECT та EXCEPT можна поєднувати з іншими операторами множин, наприклад із UNION.
Щоб однозначно задати логіку, використовуйте дужки:
(
SELECT id
FROM customers_2024
INTERSECT
SELECT id
FROM customers_2025
)
EXCEPT
SELECT id
FROM blocked_customers;Цей запит спочатку знаходить клієнтів, присутніх у 2024 і 2025 роках, а потім виключає заблокованих клієнтів.
Задачу перетину можна розв'язати і через JOIN, але INTERSECT часто краще передає намір, коли потрібно порівняти два готові набори:
SELECT customer_id
FROM orders_2024
INTERSECT
SELECT customer_id
FROM orders_2025;Це прямо читається як: «повернути клієнтів, які є в обох наборах».
EXCEPT так само зручно використовувати для задачі «є в першому наборі, але немає в другому»:
SELECT customer_id
FROM all_customers
EXCEPT
SELECT customer_id
FROM unsubscribed_customers;Конкретний вибір між операторами множин та іншими конструкціями залежить від структури запиту, але INTERSECT і EXCEPT особливо корисні для порівняння результатів двох SELECT.
SELECT id, email
FROM customers
EXCEPT
SELECT id
FROM blocked_customers;Запити мають повертати однакову кількість стовпців.
EXCEPTSELECT id
FROM blocked_customers
EXCEPT
SELECT id
FROM customers;Це означає «заблоковані клієнти, яких немає серед усіх клієнтів», а не «всі клієнти, крім заблокованих».
Для останньої задачі порядок має бути таким:
SELECT id
FROM customers
EXCEPT
SELECT id
FROM blocked_customers;Звичайні INTERSECT та EXCEPT видаляють дублікати. Якщо повторення потрібно зберегти, використовуйте INTERSECT ALL або EXCEPT ALL.
Якщо запит повертає кілька стовпців, порівнюється вся комбінація значень:
SELECT id, status
FROM first_set
INTERSECT
SELECT id, status
FROM second_set;Однаковий id ще не гарантує збіг рядків — status також має бути однаковим.
ORDER BY не в тому місціДля сортування загального результату використовуйте один ORDER BY у кінці виразу з INTERSECT або EXCEPT.
INTERSECT повертає рядки, спільні для двох запитів.
EXCEPT повертає рядки першого запиту, відсутні в другому.
Порядок запитів має значення для EXCEPT.
Запити повинні повертати однакову кількість стовпців сумісних типів.
Порівнюються всі стовпці результату.
Звичайні оператори видаляють дублікати.
INTERSECT ALL та EXCEPT ALL зберігають повторення відповідно до кількості входжень.
Для сортування всього результату використовуйте ORDER BY наприкінці.