EXCEPT и INTERSECT (Transact-SQL)

 

ПРИМЕНЯЕТСЯ К:даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data Warehouse

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

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

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

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

  • количество и порядок столбцов должны быть одинаковыми во всех запросах;

  • типы данных должны быть совместимыми.

Topic link icon Синтаксические обозначения в Transact-SQL

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
{ <query_specification> | ( <query_expression> ) }   
{ EXCEPT | INTERSECT }  
{ <query_specification> | ( <query_expression> ) }  

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

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

Спецификация запроса или выражение не может возвращать xml, текст, ntext, изображение, или недвоичные 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, эта операция отображается как левое антиполусоединение, и операция INTERSECT отображается как левого полусоединения.

Следующие примеры демонстрируют использование 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)  

Следующий пример демонстрирует использование операторов 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.)  

Добавления сообщества

ДОБАВИТЬ
Показ: