INTERSECT: примеры (SQL)
INTERSECT(query1 INTERSECT query2): Depends on queriesОператор INTERSECT в MS SQL Server
Оператор INTERSECT в Microsoft SQL Server используется для возврата строк, которые являются общими (пересекающимися) для двух результирующих наборов запросов. Он выполняет операцию пересечения множеств.
Оператор применяется в случаях, когда необходимо найти общие записи, удовлетворяющие условиям нескольких запросов. Например, для определения товаров, которые заказывали одновременно два клиента, или сотрудников, работающих в одинаковых отделах в разных филиалах.
Синтаксис оператора INTERSECT не требует явных аргументов, но имеет определенные правила:
- Количество и порядок столбцов в списках SELECT всех участвующих запросов должны совпадать.
- Типы данных соответствующих столбцов должны быть совместимыми.
- Оператор по умолчанию удаляет дубликаты строк из конечного результата, возвращая только уникальные записи.
- INTERSECT имеет более высокий приоритет, чем оператор UNION и EXCEPT, но ниже, чем операторы WHERE и GROUP BY.
Результатом выполнения является результирующий набор, содержащий строки, присутствующие в каждом из исходных наборов. Для сравнения строк используется семантика сравнения на равенство с удалением дубликатов.
Базовые примеры использования INTERSECT
Простейший пример нахождения пересечения двух наборов чисел.
SELECT 1 AS Number
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
INTERSECT
SELECT 3 AS Value
UNION ALL SELECT 4
UNION ALL SELECT 3;Number ------- 3
Пример с таблицами. Создадим тестовые данные.
CREATE TABLE #DepartmentA (EmployeeID int, EmployeeName varchar(50));
CREATE TABLE #DepartmentB (StaffID int, StaffName varchar(50));
INSERT INTO #DepartmentA VALUES (1, 'Иван'), (2, 'Мария'), (3, 'Петр');
INSERT INTO #DepartmentB VALUES (2, 'Мария'), (3, 'Петр'), (4, 'Анна');
-- Найдем сотрудников, которые числятся в обоих отделах
SELECT EmployeeID, EmployeeName FROM #DepartmentA
INTERSECT
SELECT StaffID, StaffName FROM #DepartmentB;EmployeeID EmployeeName ---------- ------------ 2 Мария 3 Петр
Пример с использованием ORDER BY. Сортировка применяется к итоговому результату.
SELECT EmployeeName FROM #DepartmentA WHERE EmployeeID > 1
INTERSECT
SELECT StaffName FROM #DepartmentB
ORDER BY EmployeeName DESC;EmployeeName ------------ Петр Мария
Похожие операции и альтернативы в MS SQL
В MS SQL Server для работы с наборами данных существуют другие операторы и методы, которые могут решать схожие задачи.
INNER JOIN
INNER JOIN может эмулировать INTERSECT по ключевым полям. Основное отличие: JOIN соединяет таблицы по условию, а INTERSECT сравнивает целые строки на полное равенство. JOIN не удаляет дубликаты строк из результата автоматически, если не используется DISTINCT, и позволяет выводить столбцы из обеих таблиц.
Оператор EXCEPT возвращает разность множеств: строки из первого набора, которых нет во втором. Является противоположной по смыслу операцией, но имеет идентичные синтаксические правила.
Оператор UNION возвращает объединение множеств (все уникальные строки из обоих наборов). Вместо поиска общих записей соединяет их.
Подзапросы с EXISTS и IN
Конструкции WHERE EXISTS (SELECT ...) и WHERE column IN (SELECT ...) могут найти пересечение по одному или нескольким столбцам. Они часто менее читаемы для операции пересечения целых строк, но более гибки при сложных условиях сравнения не всех столбцов.
Выбор инструмента зависит от задачи. INTERSECT идеален для простого и четкого нахождения полностью идентичных строк из двух выборок. INNER JOIN предпочтительнее, когда нужно сравнить по ключам и вывести комбинированный результат. Подзапросы подходят для сложных условий отбора.
Распространенные ошибки при работе с INTERSECT
Несовпадение числа столбцов
Самая частая ошибка - разное количество столбцов в выборках.
SELECT ID, Name FROM TableA
INTERSECT
SELECT ID FROM TableB; -- Ошибка!Msg 205, Level 16, State 1 Все запросы в объединении, пересечении или разности должны иметь одинаковое количество выражений в целевом списке.
Несовместимые типы данных
Типы данных в соответствующих позициях должны допускать неявное преобразование.
SELECT '123' AS Col
INTERSECT
SELECT 123 AS Col; -- Неявное преобразование возможно, но может привести к неожиданностям.Путаница с логикой при использовании NULL
Значение NULL не равно другому NULL по стандарту SQL. Строка, содержащая NULL в одном из столбцов, не будет считаться равной такой же строке из другого набора. Это может привести к неинтуитивным результатам.
SELECT NULL AS Col1
INTERSECT
SELECT NULL AS Col1;-- Результат пуст, так как NULL = NULL не является истиной.
Игнорирование приоритета операторов
Без скобок порядок выполнения может быть неочевидным.
SELECT 1 UNION SELECT 2 INTERSECT SELECT 2; -- INTERSECT выполнится до UNIONИзменения в INTERSECT в последних версиях SQL Server
Сам оператор INTERSECT не претерпел значительных синтаксических изменений в последних основных версиях SQL Server (2005-2022). Его поддержка была введена в SQL Server 2005 вместе с оператором EXCEPT.
Основные улучшения связаны с оптимизацией выполнения запросов планировщиком SQL Server. В более новых версиях (например, SQL Server 2014 и выше) оптимизатор может использовать более эффективные стратегии для выполнения операций над множествами, особенно в сочетании с индексами columnstore и улучшениями обработки временных таблиц.
Рекомендуется использовать последние доступные накопительные обновления для вашей версии SQL Server, чтобы получить все улучшения в работе планировщика запросов, что может положительно сказаться на производительности запросов с INTERSECT на больших объемах данных.
Расширенные и специализированные примеры
Использование INTERSECT с агрегатными функциями и GROUP BY
-- Находим категории товаров, в которых и в 2022, и в 2023 году было продано более 100 единиц.
SELECT CategoryID FROM Sales WHERE Year=2022 GROUP BY CategoryID HAVING SUM(Quantity) > 100
INTERSECT
SELECT CategoryID FROM Sales WHERE Year=2023 GROUP BY CategoryID HAVING SUM(Quantity) > 100;Вложенные INTERSECT и комбинации с другими операторами
-- Находим товары, которые были в заказах клиента 1 и клиента 2, но не в заказах клиента 3.
(SELECT ProductID FROM Orders WHERE CustomerID = 1
INTERSECT
SELECT ProductID FROM Orders WHERE CustomerID = 2)
EXCEPT
SELECT ProductID FROM Orders WHERE CustomerID = 3;INTERSECT для проверки целостности данных или совпадения схем
-- Проверка, что все города из таблицы Поставщиков есть в таблице Клиентов.
-- Если результат равен набору городов поставщиков, то целостность соблюдается.
SELECT City FROM Suppliers
EXCEPT
SELECT City FROM Customers; -- Должно быть пусто-- Сравнение схем двух таблиц (списков столбцов) через метаданные.
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table1'
INTERSECT
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Table2';INTERSECT с CTE (Common Table Expression)
WITH ActiveProducts2022 AS (
SELECT DISTINCT ProductID FROM Sales WHERE Year=2022 AND Quantity > 0
),
ActiveProducts2023 AS (
SELECT DISTINCT ProductID FROM Sales WHERE Year=2023 AND Quantity > 0
)
SELECT p.Name
FROM Products p
INNER JOIN (
SELECT ProductID FROM ActiveProducts2022
INTERSECT
SELECT ProductID FROM ActiveProducts2023
) ap ON p.ProductID = ap.ProductID;Имитация INTERSECT ALL (с дубликатами)
Стандартный INTERSECT удаляет дубликаты. Чтобы сохранить их количество (как в INTERSECT ALL, который в T-SQL не поддерживается), можно использовать оконную функцию ROW_NUMBER().
WITH SetA AS (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as rn FROM (VALUES (1), (1), (2)) t(col)),
SetB AS (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as rn FROM (VALUES (1), (1), (3)) t(col))
SELECT a.col
FROM SetA a
WHERE EXISTS (SELECT 1 FROM SetB b WHERE b.col = a.col AND b.rn = a.rn);
-- Это сложная эмуляция, на практике часто достаточно DISTINCT.Аналоги INTERSECT в других СУБД и языках
Концепция пересечения множеств поддерживается большинством реляционных СУБД, но с нюансами.
PostgreSQL
Поддерживает оператор INTERSECT идентично SQL Server. Пример:
SELECT generate_series(1,5)
INTERSECT
SELECT generate_series(3,7);generate_series ---------------- 3 4 5
Oracle Database
Синтаксис INTERSECT в Oracle также совпадает. Важное отличие: в Oracle часто требуется использование FROM DUAL для скалярных запросов.
SELECT 1 FROM DUAL
UNION ALL SELECT 2 FROM DUAL
UNION ALL SELECT 3 FROM DUAL
INTERSECT
SELECT 3 FROM DUAL
UNION ALL SELECT 4 FROM DUAL; 1
----------
3MySQL
MySQL не имеет оператора INTERSECT до версии 8.0. В более ранних версиях используют эмуляцию через INNER JOIN или подзапрос с EXISTS/IN.
-- Эмуляция через INNER JOIN (по всем столбцам)
SELECT DISTINCT a.* FROM DepartmentA a
INNER JOIN DepartmentB b
ON a.EmployeeID = b.StaffID AND a.EmployeeName = b.StaffName;
-- Эмуляция через EXISTS
SELECT * FROM DepartmentA a
WHERE EXISTS (
SELECT 1 FROM DepartmentB b
WHERE b.StaffID = a.EmployeeID AND b.StaffName = a.EmployeeName
);SQLite
SQLite поддерживает INTERSECT, начиная с версии 3.22.0 (2018 год). В более ранних версиях используют JOIN.
Языки программирования
В императивных языках операцию пересечения выполняют методы коллекций. В Python это оператор & для set или метод intersection(). В JavaScript используют методы filter и includes для массивов или Set.