Intersect sql: SQL оператор INTERSECT — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

EXCEPT и INTERSECT (Transact-SQL) — SQL Server


  • Статья


  • Чтение занимает 4 мин


Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics AnalyticsPlatform System (PDW)

Эти операторы возвращают различные строки, сравнивая результаты двух запросов.

Оператор EXCEPT возвращает уникальные строки из левого входного запроса, которые не выводятся правым входным запросом.

Оператор INTERSECT возвращает уникальные строки, выводимые левым и правым входными запросами.

Основные правила объединения результирующих наборов двух запросов с оператором EXCEPT или INTERSECT таковы:

Соглашения о синтаксисе Transact-SQL

Синтаксис

{ <query_specification> | ( <query_expression> ) }   
{ EXCEPT | INTERSECT }  
{ <query_specification> | ( <query_expression> ) }

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

<query_specification> | ( <query_expression> )
Спецификация запроса или выражение запроса, возвращающее данные для сравнения с данными, возвращенными другой спецификацией запроса или выражением запроса. Определения столбцов, обрабатываемых при операции EXCEPT или INTERSECT, могут быть разными. Тем не менее они должны поддерживать возможность сравнения путем неявного преобразования типов. Если типы данных различаются, то тип данных для сравнения определяется на основе правил очередности типов данных.

Если типы одинаковы, но различаются по точности, масштабу или длине, результат определяется на основе тех же самых правил, которые действуют при объединении выражений. Дополнительные сведения см. в разделе Точность, масштаб и длина (Transact-SQL).

Спецификация или выражение запроса не может возвращать столбцы типа xml, text, ntext, image или недвоичного пользовательского типа данных CLR, потому что эти типы данных не поддерживают сравнение.

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

INTERSECT
Возвращает все различные значения, входящие в результаты выполнения запросов, указанных как слева, так и справа от оператора INTERSECT.

Типы данных сравниваемых столбцов возвращаются запросами слева и справа от операторов EXCEPT или INTERSECT. Эти типы данных могут содержать символьные типы данных с различными параметрами сортировки. При этом необходимое сравнение выполняется в соответствии с правилами очередности параметров сортировки. Если нужное преобразование выполнить не удается, Компонент SQL Server Database Engine возвращает ошибку.

Если сравниваются значения столбцов с целью определения различных строк, два значения NULL считаются равными.

Операторы EXCEPT и INTERSECT возвращают имена столбцов результирующего набора, совпадающие с именами столбцов, которые возвращает запрос слева от оператора.

Имена столбцов или псевдонимы в предложениях ORDER BY должны ссылаться на имена столбцов, возвращаемых запросом, указанным слева от оператора.

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

Если оператор EXCEPT или INTERSECT используется в выражении вместе с другими операторами, оно обрабатывается в следующем порядке:

  1. Выражения в скобках

  2. Оператор INTERSECT

  3. Операторы EXCEPT и UNION обрабатываются слева направо в соответствии с их позицией в выражении.

Оператор EXCEPT или INTERSECT можно использовать для сравнения более двух наборов запросов. При этом преобразование типов данных выполняется на основе сравнения двух запросов сразу с соблюдением вышеупомянутых правил обработки выражений.

Операторы EXCEPT и INTERSECT нельзя использовать в определениях распределенных секционированных представлений и уведомлениях о запросах.

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

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

При выводе данных об операции EXCEPT с помощью средства графического отображения плана в среде SQL Server Management Studio операция представляется как left anti semi join, а операция INTERSECT — как left semi join.

Примеры

В следующих примерах демонстрируется использование операторов INTERSECT и EXCEPT. Первый запрос возвращает все значения из таблицы Production. Product для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT.

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product ;  
--Result: 504 Rows

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

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product  
INTERSECT  
SELECT ProductID   
FROM Production.WorkOrder ;  
--Result: 238 Rows (products that have work orders)

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT, но отсутствующие в результатах выполнения правого запроса.

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.Product  
EXCEPT  
SELECT ProductID   
FROM Production.WorkOrder ;  
--Result: 266 Rows (products without work orders)

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

-- Uses AdventureWorks  
  
SELECT ProductID   
FROM Production.WorkOrder  
EXCEPT  
SELECT ProductID   
FROM Production.Product ;  
--Result: 0 Rows (work orders without products)

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Следующий пример демонстрирует использование операторов INTERSECT и EXCEPT. Первый запрос возвращает все значения из таблицы FactInternetSales для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales;  
--Result: 60398 Rows

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

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
INTERSECT   
SELECT CustomerKey   
FROM DimCustomer   
WHERE DimCustomer. Gender = 'F'  
ORDER BY CustomerKey;  
--Result: 9133 Rows (Sales to customers that are female.)

Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT, но отсутствующие в результатах выполнения правого запроса.

-- Uses AdventureWorks  
  
SELECT CustomerKey   
FROM FactInternetSales    
EXCEPT   
SELECT CustomerKey   
FROM DimCustomer   
WHERE DimCustomer.Gender = 'F'  
ORDER BY CustomerKey;  
--Result: 9351 Rows (Sales to customers that are not female.)

SQL INTERSECT и EXCEPT — операторы для пересечения и разности результатов запросов

Навигация по уроку

  • Операции пересечения и разности множеств в SQL
  • Пересечение множеств: оператор SQL INTERSECT и его альтернативы
  • Разность множеств: оператор SQL EXCEPT и его альтернативы

Связанные темы

  • Оператор SELECT
  • Реляционная алгебра и её операции
НазадСодержаниеВперёд>>>

Оператор SQL INTERSECT реализует операцию реляционной алгебры пересечение множеств, оператор SQL EXCEPT —
разность множеств. В виде множеств выступают результаты единичных запросов.

Таким образом, оператор SQL INTERSECT возвращает те и только те строки, которые возвращает и первый, и второй
запросы. В свою очередь, оператор SQL EXCEPT возвращает те строки, которые возвращает первый запрос, и
которых нет среди строк, возвращаемых вторым запросом.

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

Оператор INTERSECT имеет следующий синтаксис:

SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ
INTERSECT
SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ

Оператор EXCEPT имеет следующий синтаксис:

SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ
EXCEPT
SELECT ИМЕНА_СТОЛБЦОВ (1..N)
FROM ИМЯ_ТАБЛИЦЫ

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

В примерах работаем с базой данных сети магазинов и таблицами SOLNYSHKO и VETEROK, содержащими данные о продуктах,
которые имеются в магазинах с соответствующими названиями. Таблица SOLNYSHKO:

Prod_IDProdNameMakerQuantity
1хлебAB100
2молокоCD65
3мясоEF75
4рыбаGH60
5сахарIJ45

Таблица VETEROK:

Prod_IDProdNameMakerQuantity
1хлебQW85
2молокоLD70
3сырMV45
4маслоDG62
5рыбаLN55

Пересечением множеств A и B называется множество, состоящее их всех тех или только тех
элементов, которые принадлежат каждому из множеств A и B. Больше об операциях над
множествами как над математическими объектами можно узнать из урока Множества и операции над множествами.
Пересечениями множеств могут служить носители одних и тех же имен в двух студенческих группах, овощи одних
и тех же наименований в двух корзинах и другие. Пересечением множеств является, наконец, набор товаров, которые имеются и в одном, и в другом магазинах.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД
не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Скрипт для создания базы данных магазинов, её таблиц и заполения таблиц данными —
в файле по этой ссылке.

Пример 1. Вывести список продуктов, которые имеются и в мазазине
Solnyshko, и в магазине Veterok. Пишем следующий запрос с использованием оператора SQL INTERSECT:

SELECT ProdName
FROM Solnyshko
INTERSECT
SELECT ProdName
FROM Veterok

Результатом выполнения запроса будет следующая таблица:

ProdName
хлеб
молоко
рыба

Во многих диалектах SQL, например, MySQL, оператор INTERSECT отсутствует. Но реализация операции
пересечения множеств возможна другими способами. Наиболее простой способ связан с использованием
предиката EXISTS. В качестве альтернативы им можно
пользоваться и в MS SQL Server.

Пример 2. Вывести список продуктов, которые имеются и в мазазине
Solnyshko, и в магазине Veterok. Использовать предикат SQL EXISTS. Пишем следующий запрос:

SELECT ProdName
FROM Solnyshko
AS name_soln
WHERE EXISTS (SELECT
ProdName FROM VETEROK WHERE
ProdName=name_soln.ProdName)

Результатом выполнения запроса будет та же таблица, что и в примере 1:

ProdName
хлеб
молоко
рыба
  • Аналогии между INTERSECT и EXISTS, EXCEPT и NOT EXISTS: более сложные примеры

Разностью множеств A и B называется множество состоящее из всех тех и только тех элементов
множества A, которые не являются элементами множества B. В частности, такое множество
может состоять из продуктов, которые имеются в одном из магазинов, но отсутствуют в другом магазине.

Пример 3. Вывести список продуктов, которые имеются в мазазине
Solnyshko, и отсутствуют в магазине Veterok. Пишем следующий запрос с использованием оператора SQL EXCEPT:

SELECT ProdName
FROM Solnyshko
EXCEPT
SELECT ProdName
FROM Veterok

Результатом выполнения запроса будет следующая таблица:

ProdName
мясо
сахар

Во многих диалектах SQL, например, MySQL, оператор EXCEPT отсутствует. Наиболее простой альтернативный
способ реализации разности множеств связан с использованием
предиката EXISTS с отрицанием NOT, то есть NOT EXISTS.
В качестве альтернативы им можно пользоваться и в MS SQL Server.

Пример 4. Вывести список продуктов, которые имеются в мазазине
SOLNYSHKO, и отсутствуют в магазине VETEROK. Использовать предикат SQL NOT EXISTS. Пишем следующий запрос:

SELECT ProdName
FROM Solnyshko
AS name_soln
WHERE NOT EXISTS (SELECT
ProdName FROM Veterok WHERE
ProdName=name_soln.ProdName)

Результатом выполнения запроса будет та же таблица, что и в примере 2:

ProdName
мясо
сахар
  • Аналогии между INTERSECT и EXISTS, EXCEPT и NOT EXISTS: более сложные примеры

Поделиться с друзьями

НазадСодержаниеВперёд>>>

SQL | Пункт INTERSECT — GeeksforGeeks

    Написание статьи

  • Написание интервью Опыт
  • Обход дерева (по порядку, предварительному заказу и последующему заказу)
  • SQL | Соединение (внутреннее, левое, правое и полное соединение)
  • найти команду в Linux с примерами
  • Что такое утечка памяти? Как мы можем избежать?
  • Асимптотическая запись и анализ (на основе размера входных данных) в Анализе сложности алгоритмов
  • Часто задаваемые вопросы по структуре данных на собеседовании | Набор 1
  • Как написать псевдокод?
  • Типы асимптотических обозначений в анализе сложности алгоритмов
  • Понимание ключевого слова «extern» в C
  • Анализ алгоритмов наихудшего, среднего и наилучшего случаев
  • Мьютекс против семафора
  • SQL | СГРУППИРОВАТЬ ПО
  • Amazon Вопросы интервью
  • SQL | Представления
  • Рекурсивные практические задачи с решениями
  • Как анализировать циклы для анализа сложности алгоритмов
  • Предотвращение и предотвращение взаимоблокировок
  • Вопросы для собеседования по SQL
  • Тайна Little and Big Endian
  • Различия и применение списков, кортежей, наборов и словарей в Python
  • SQL | INSERT INTO Statement
  • Временная сложность и пространственная сложность
  • Введение в NP-полноту
  • ООП | Объектно-ориентированный дизайн
  • Хранение строк на C
  • Запуск скрипта Python на графическом процессоре.
  • Учебник по Java
  • Расширения и реализации в Java
  • SQL | Соединение (Декартово соединение и самосоединение)
  • Функция getch() в C с примерами

Улучшить статью

Сохранить статью

  • Уровень сложности:
    Easy
  • Последнее обновление:
    15 июл, 2022
  • Читать
  • Обсудить

    • Улучшить статью

    Сохранить статью

    Предложение INTERSECT в SQL используется для объединения двух операторов SELECT, но набор данных, возвращаемый оператором INTERSECT, будет пересечением наборов данных двух операторов SELECT. Проще говоря, оператор INTERSECT вернет только те строки, которые будут общими для обоих операторов SELECT.

    Графическое представление:

    Оператор INTERSECT вернет только те строки, которые находятся в области, заштрихованной красным. общий для обоих наборов данных.

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

    Синтаксис :

      ВЫБЕРИТЕ столбец1, столбец2 ....
ИЗ table_names
ГДЕ условие

ПЕРЕСЕЧАТЬ

ВЫБЕРИТЕ столбец1, столбец2....
ИЗ table_names
ГДЕ условие

    Примеры таблиц :

    Таблица клиентов:

    Таблица заказов:

    Примеры запросов :

    ВЫБЕРИТЕ ID, ИМЯ, Сумма, Дата
     ОТ клиентов
     ЛЕВОЕ СОЕДИНЕНИЕ Заказы
     ON Customers.ID = Orders.Customer_id
ПЕРЕСЕЧАТЬ
     ВЫБЕРИТЕ ID, ИМЯ, Сумма, Дата
     ОТ клиентов
     ПРАВОЕ СОЕДИНЕНИЕ Заказы
     ON Customers.ID = Orders.Customer_id;

    Вывод:

    Эта статья предоставлена ​​ Суровым Агарвалом . Если вам нравится GeeksforGeeks и вы хотите внести свой вклад, вы также можете написать статью с помощью write.geeksforgeeks.org или отправить ее по адресу [email protected]. Посмотрите, как ваша статья появится на главной странице GeeksforGeeks, и помогите другим гикам.

    Пожалуйста, пишите комментарии, если вы обнаружите что-то неправильное, или вы хотите поделиться дополнительной информацией по теме, обсуждаемой выше.

    Связанные статьи

    Что нового

    Мы используем файлы cookie, чтобы обеспечить вам максимальное удобство просмотра нашего веб-сайта. Используя наш сайт, вы
    подтверждаете, что вы прочитали и поняли наши
    Политика в отношении файлов cookie &
    Политика конфиденциальности

    ЗА ИСКЛЮЧЕНИЕМ и ПЕРЕСЕЧЕНИЕ (Transact-SQL) — SQL Server

    • Статья
    • 5 минут на чтение

    Применяется к: SQL Server Azure SQL База данных Azure SQL Управляемый экземпляр Azure Synapse Analytics Analytics Platform System (PDW)

    Возвращает отдельные строки путем сравнения результатов двух запросов.

    EXCEPT возвращает отдельные строки из левого входного запроса, которые не выводятся правым входным запросом.

    INTERSECT возвращает отдельные строки, которые выводятся как левым, так и правым оператором запросов ввода.

    Для объединения наборов результатов двух запросов, использующих EXCEPT или INTERSECT, используются следующие основные правила:

    Соглашения о синтаксисе Transact-SQL

    Синтаксис 

     { <спецификация_запроса> | ( <выражение_запроса> ) }
{ ЗА ИСКЛЮЧЕНИЕМ | ПЕРЕСЕЧАТЬ }
{ <спецификация_запроса> | ( <выражение_запроса> ) }

    Примечание

    Чтобы просмотреть синтаксис Transact-SQL для SQL Server 2014 и более ранних версий, см. документацию по предыдущим версиям.

    Аргументы

    < спецификация_запроса > | ( < query_expression > )
    Спецификация запроса или выражение запроса, которое возвращает данные для сравнения с данными из другой спецификации запроса или выражения запроса. Определения столбцов, которые являются частью операции EXCEPT или INTERSECT, не обязательно должны быть одинаковыми. Но они должны быть сопоставимы посредством неявного преобразования. Когда типы данных различаются, правила приоритета типов данных определяют тип данных, который выполняется для сравнения.

    Результат основан на одних и тех же правилах объединения выражений, когда типы одинаковы, но отличаются точностью, масштабом или длиной. Дополнительные сведения см. в разделе Точность, масштаб и длина (Transact-SQL).

    Спецификация или выражение запроса не могут возвращать xml , text , ntext , image или недвоичные столбцы пользовательского типа CLR, поскольку эти типы данных несопоставимы.

    EXCEPT
    Возвращает любые отличные значения из запроса слева от оператора EXCEPT. Эти значения возвращаются до тех пор, пока правильный запрос не возвращает эти значения.

    INTERSECT
    Возвращает любые различные значения, которые возвращаются запросом как слева, так и справа от оператора INTERSECT.

    Типы данных сопоставимых столбцов возвращаются запросами слева и справа от операторов EXCEPT или INTERSECT. Эти типы данных могут включать в себя символьные типы данных с различными сопоставлениями. Когда они это делают, требуемое сравнение выполняется в соответствии с правилами приоритета сопоставления. Если вы не можете запустить это преобразование, ядро ​​базы данных SQL Server возвращает ошибку.

    При сравнении значений столбца для определения строк DISTINCT два значения NULL считаются равными.

    EXCEPT и INTERSECT возвращают имена столбцов результирующего набора, которые совпадают с именами столбцов, возвращаемых запросом в левой части оператора.

    Имена или псевдонимы столбцов в предложениях ORDER BY должны ссылаться на имена столбцов, возвращаемые левым запросом.

    Обнуляемость любого столбца в результирующем наборе, возвращаемом оператором EXCEPT или INTERSECT, такая же, как обнуляемость соответствующего столбца, возвращаемого запросом в левой части оператора.

    Если EXCEPT или INTERSECT используются вместе с другими операторами в выражении, они оцениваются в контексте следующего приоритета:

    1. Выражения в скобках

    2. Оператор ИНТЕРСЕКТ

    3. EXCEPT и UNION оцениваются слева направо в зависимости от их позиции в выражении

    Вы можете использовать EXCEPT или INTERSECT для сравнения более двух наборов запросов. При этом преобразование типа данных определяется путем одновременного сравнения двух запросов и соблюдения ранее упомянутых правил вычисления выражений.

    EXCEPT и INTERSECT нельзя использовать в определениях распределенных секционированных представлений и уведомлениях о запросах.

    EXCEPT и INTERSECT можно использовать в распределенных запросах, но они выполняются только на локальном сервере и не передаются на связанный сервер. Таким образом, использование EXCEPT и INTERSECT в распределенных запросах может повлиять на производительность.

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

    Когда операция EXCEPT отображается с помощью функции Graphical Showplan в SQL Server Management Studio, операция отображается как левое анти-полусоединение, а операция INTERSECT отображается как левое полусоединение.

    Примеры

    В следующих примерах показано использование операторов INTERSECT и EXCEPT . Первый запрос возвращает все значения из таблицы Production.Product для сравнения с результатами INTERSECT и 9.0249 КРОМЕ .

     -- Использует AdventureWorks
  
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ОТ Производство.Продукт ;
--Результат: 504 строки

    Следующий запрос возвращает любые различные значения, которые возвращаются запросом как слева, так и справа от оператора INTERSECT .

     -- Использует AdventureWorks
  
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ОТ Производство.Продукт
ПЕРЕСЕЧАТЬ
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ИЗ Производство.Заказ на работу ;
--Результат: 238 строк (продукты с заказами на работу)

    Следующий запрос возвращает любые отличные значения из запроса слева от оператора EXCEPT , которые также не найдены в правом запросе.

     -- Использует AdventureWorks
  
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ОТ Производство.Продукт
КРОМЕ
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ИЗ Производство.Заказ на работу ;
--Результат: 266 строк (товары без заказов на работу)

    Следующий запрос возвращает любые отличные значения из запроса слева от оператора EXCEPT , которые также не найдены в правом запросе. Таблицы обратные по сравнению с предыдущим примером.

     -- Использует AdventureWorks
  
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ОТ Производство.Заказ
КРОМЕ
ВЫБЕРИТЕ ИДЕНТИФИКАТОР продукта
ОТ Производство.Продукт ;
--Результат: 0 строк (заказы на работу без продуктов)

    Примеры: Azure Synapse Analytics and Analytics Platform System (PDW)

    В следующих примерах показано, как использовать операторы INTERSECT и EXCEPT .