INTERSECT

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

Теория

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

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

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

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

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

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 у одного из столбцов и проверьте, выполнится ли запрос

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 и 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 нужен внутри каждой части, используем скобки

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

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

Практика

Проверь себя