INTERSECT

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

Теория

Оператор INTERSECT нужен, когда из двух или нескольких SELECT-запросов необходимо оставить только те строки, которые совпали во всех этих наборах. Каждый запрос сначала возвращает некоторый набор строк, а потом PostgreSQL ищет пересечение этих наборов.

INTERSECT отвечает на вопрос: какие строки есть и в первом результате SELECT запроса, и во втором. Запросов может быть и больше, тогда пересечение по совпадающим строкам определяется по всем запросам.

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

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

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

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

Синтаксис

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

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

SQL

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

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

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

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

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

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

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

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

Примеры

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

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

Обе части запроса возвращают по одному текстовому столбцу. В результате останутся только те имена, которые встречаются и в таблице users, и в таблице workers.

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

• Замените INTERSECT на UNION и сравните, насколько изменится сам смысл результата
• Добавьте в первый запрос фильтр WHERE registration_date >= '2023-01-01' и посмотрите, как изменится пересечение

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

SELECT user_id
FROM orders
WHERE status = 'доставлен'
INTERSECT
SELECT user_id
FROM orders
WHERE status = 'отменён'
ORDER BY user_id;

Первый запрос возвращает пользователей с доставленными заказами, второй — с отменёнными. После INTERSECT останутся только те user_id, которые встретились в обоих наборах. Используя логический оператор AND в WHERE такое условие уже не выразить, потому что один заказ не может иметь оба статуса сразу.

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

• Выполните сначала первый запрос отдельно, потом второй отдельно, а затем оба через INTERSECT как в примере. Объясните результат
• Замените во втором запросе статус 'отменён' на 'доставлен' и сравните новый результат

3. Ищем пользователей, которые делали заказы как в апреле, так и в мае

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
INTERSECT
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 года, вторая — с майскими. После INTERSECT останутся только те user_id, которые встретились в обоих месяцах.

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

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

4. Найдем одноименцев и однофамильцев среди клиентов и сотрудников

SELECT first_name,
       last_name
FROM users
INTERSECT
SELECT first_name,
       last_name
FROM workers
ORDER BY first_name, last_name;

Ищем совпадающие имя и фамилию среди клиентов и сотрудников. Первая часть возвращает пары first_name + last_name из таблицы users, вторая — такие же пары из таблицы workers. После INTERSECT останутся только те ФИ, которые встретились в обеих таблицах. В этом примере мы нашли совпадающие строки, состоящие из двух столбцов.

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

• Уберите второй столбец last_name из одного из запросов и посмотрите на ошибку
• Оставьте в запросах только first_name и сравните, как изменится результат

5. Приводим типы, если сравниваем значения разной природы

SELECT worker_id::text AS entity_value
FROM workers
WHERE department_id = 1
INTERSECT
SELECT user_id::text
FROM users
WHERE city IS NOT NULL
ORDER BY entity_value;

В этом примере обе части приводят идентификаторы к text. Такой приём необходим, когда без явного приведения PostgreSQL не сможет сопоставить разные типы и выполнить пересечение.

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

• Уберите приведение ::text у одного из столбцов и проверьте, выполнится ли запрос
• Поменяйте фильтр в таблице users, чтобы сравнить другой набор значений

6. Если LIMIT нужен внутри каждой части, используем скобки

(SELECT first_name AS person_name
FROM users
WHERE city IS NOT NULL
ORDER BY registration_date
LIMIT 10)
INTERSECT
(SELECT first_name AS person_name
FROM workers
ORDER BY hire_date
LIMIT 10)
ORDER BY person_name;

Здесь ORDER BY и LIMIT относятся не ко всему пересечению сразу, а к каждой части отдельно. Без скобок такая запись приведёт к ошибке.

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

• Уберите скобки и выполните запрос, чтобы увидеть ошибку PostgreSQL
• Оставьте скобки, но уберите внутренние LIMIT и сравните результат

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

Путают INTERSECT и UNION
INTERSECT не складывает все строки вместе, а оставляет только общее пересечение. Если нужен общий список всех строк, это уже задача для UNION или UNION ALL.

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

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

Не приводят типы, когда это нужно
Если одна часть возвращает число, а другая текст, PostgreSQL не всегда сможет автоматически сопоставить разные типы. В таких случаях помогает явное приведение, например worker_id::text.

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

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

INTERSECT

Теория

Синтаксис

Примеры

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

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

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

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

3. Ищем пользователей, которые делали заказы как в апреле, так и в мае

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

4. Найдем одноименцев и однофамильцев среди клиентов и сотрудников

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

5. Приводим типы, если сравниваем значения разной природы

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

6. Если LIMIT нужен внутри каждой части, используем скобки

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

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

Практика

Проверь себя