Operadores de conjunto – EXCEPT e INTERSECT (Transact-SQL)Set Operators - EXCEPT and INTERSECT (Transact-SQL)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Retorna linhas distintas comparando os resultados de duas consultas.Returns distinct rows by comparing the results of two queries.

EXCETO retorna linhas distintas da consulta de entrada à esquerda que não são produzidas pela consulta de entrada à direita.EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

INTERSECT retorna linhas distintas que são produzidas pelo operador das consultas de entrada à esquerda e à direita.INTERSECT returns distinct rows that are output by both the left and right input queries operator.

Para combinar os conjuntos de resultados de duas consultas que usam EXCEPT ou INTERSECT, as regras básicas são:To combine the result sets of two queries that use EXCEPT or INTERSECT, the basic rules are:

  • O número e a ordem das colunas devem ser iguais em todas as consultas.The number and the order of the columns must be the same in all queries.

  • Os tipos de dados devem ser compatíveis.The data types must be compatible.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

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

ArgumentosArguments

<query_specification> | ( <query_expression> )<query_specification> | ( <query_expression> )
É uma especificação ou expressão de consulta que retorna dados a serem comparados com os dados de outra especificação ou expressão de consulta.Is a query specification or query expression that returns data to be compared with the data from another query specification or query expression. As definições das colunas que fazem parte de uma operação EXCEPT ou INTERSECT não precisam ser iguais.The definitions of the columns that are part of an EXCEPT or INTERSECT operation don't have to be the same. Porém, elas devem ser semelhantes na conversão implícita.But, they must be comparable through implicit conversion. Quando os tipos de dados diferem, as regras para precedência de tipo de dados determinam o tipo de dados que é executado para comparação.When data types differ, the rules for data type precedence determine the data type that is run for comparison.

O resultado se baseia nas mesmas regras para combinar expressões quando os tipos são iguais, mas diferem em precisão, escala ou tamanho.The result is based on the same rules for combining expressions when the types are the same but differ in precision, scale, or length. Para obter mais informações, consulte Precisão, escala e comprimento (Transact-SQL).For more information, see Precision, Scale, and Length (Transact-SQL).

A especificação ou expressão de consulta não pode retornar colunas xml, text, ntext, image ou colunas de tipo de dado CLR definido pelo usuário não binárias, pois esses tipos de dados não são comparáveis.The query specification or expression can't return xml, text, ntext, image, or nonbinary CLR user-defined type columns because these data types aren't comparable.

EXCEPTEXCEPT
Retorna qualquer valor distinto da consulta à esquerda do operador EXCEPT.Returns any distinct values from the query left of the EXCEPT operator. Esses valores são retornados desde que a consulta à direita não retorne esse valores também.Those values return as long the right query doesn't return those values as well.

INTERSECTINTERSECT
Retorna qualquer valor distinto retornado pela consulta à esquerda e à direita do operador INTERSECT.Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

RemarksRemarks

Os tipos de dados de colunas comparáveis são retornados pelas consultas à esquerda e à direita dos operadores EXCEPT ou INTERSECT.The data types of comparable columns are returned by the queries left and right of the EXCEPT or INTERSECT operators. Esses tipos de dados podem incluir tipos de dados de caractere com ordenações diferentes.These data types can include character data types with different collations. Quando isso acontece, a comparação necessária é executada de acordo com as regras de precedência de ordenação.When they do, the required comparison is run according to the rules of collation precedence. Se você não puder executar essa conversão, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine retornará um erro.If you can't run this conversion, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine returns an error.

Ao comparar valores de colunas para determinar linhas DISTINTAS, dois valores NULL são considerados iguais.When comparing column values for determining DISTINCT rows, two NULL values are considered equal.

EXCEPT e INTERSECT retornam os nomes de coluna do conjunto de resultados que são iguais aos nomes de coluna retornados pela consulta do lado esquerdo do operador.EXCEPT and INTERSECT return the result set's column names that are the same as the column names that the query on the operator's left side returns.

Os nomes ou aliases de coluna nas cláusulas ORDER BY devem referenciar nomes de coluna retornados pela consulta à esquerda.Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.

A nulidade de qualquer coluna do conjunto de resultados retornada por EXCEPT ou INTERSECT é igual à nulidade da coluna correspondente que é retornada pela consulta do lado esquerdo do operador.The nullability of any column in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column that is returned by the query on the operator's left side.

Se EXCEPT ou INTERSECT forem usados com outros operadores em uma expressão, eles serão avaliados no contexto da seguinte precedência:If EXCEPT or INTERSECT is used together with other operators in an expression, it's evaluated in the context of the following precedence:

  1. Expressões entre parêntesesExpressions in parentheses

  2. O operador INTERSECTThe INTERSECT operator

  3. EXCEPT e UNION avaliados da esquerda para a direita com base em sua posição na expressãoEXCEPT and UNION evaluated from left to right based on their position in the expression

Você pode usar EXCEPT ou INTERSECT para comparar mais de dois conjuntos de consultas.You can use EXCEPT or INTERSECT to compare more than two sets of queries. Quando isso é feito, a conversão de tipo de dados é determinada pela comparação de duas consultas por vez e segue as regras de avaliação de expressão mencionadas anteriormente.When you do, data type conversion is determined by comparing two queries at a time, and following the previously mentioned rules of expression evaluation.

EXCEPT e INTERSECT não podem ser usados em definições de exibição particionadas distribuídas e notificações de consulta.EXCEPT and INTERSECT can't be used in distributed partitioned view definitions, query notifications.

EXCEPT e INETERSECT podem ser usados em consultas distribuídas, mas são executados somente no servidor local e não são enviados ao servidor vinculado.EXCEPT and INTERSECT may be used in distributed queries, but are only executed on the local server and not pushed to the linked server. Desse modo, o uso de EXCEPT e INTERSECT em consultas distribuídas pode afetar desempenho.As such, using EXCEPT and INTERSECT in distributed queries may affect performance.

Você pode usar cursores estáticos e somente de avanço rápido no conjunto de resultados quando eles são usados com uma operação EXCEPT ou INTERSECT.You can use fast forward-only and static cursors in the result set when they're used with an EXCEPT or INTERSECT operation. Também é possível usar um cursor dinâmico ou orientado ao conjunto de chaves com uma operação EXCEPT ou INTERSECT.You can also use a keyset-driven or dynamic cursor together with an EXCEPT or INTERSECT operation. Quando você faz isso, o cursor do conjunto de resultados da operação é convertido em um cursor estático.When you do, the cursor of the operation result set is converted to a static cursor.

Quando uma operação EXCEPT é exibida usando o recurso Plano de Execução Gráfico no SQL Server Management StudioSQL Server Management Studio, a operação é exibida como uma left anti semi join, e uma operação INTERSECT é exibida como uma left semi join.When an EXCEPT operation is displayed by using the Graphical Showplan feature in SQL Server Management StudioSQL Server Management Studio, the operation appears as a left anti semi join, and an INTERSECT operation appears as a left semi join.

ExemplosExamples

Os exemplos a seguir mostram o uso dos operadores INTERSECT e EXCEPT.The following examples show using the INTERSECT and EXCEPT operators. A primeira consulta retorna todos os valores da tabela Production.Product para comparar com os resultados de INTERSECT e EXCEPT.The first query returns all values from the Production.Product table for comparison to the results with INTERSECT and EXCEPT.

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

A consulta a seguir retorna qualquer valor distinto retornado pela consulta à esquerda e à direita do operador INTERSECT.The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

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

A consulta a seguir retorna qualquer valor distinto da consulta à esquerda do operador EXCEPT que não seja encontrado também na consulta à direita.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.

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

A consulta a seguir retorna qualquer valor distinto da consulta à esquerda do operador EXCEPT que não seja encontrado também na consulta à direita.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query. As tabelas são inversas às do exemplo anterior.The tables are reversed from the previous example.

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

Exemplos: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) e Parallel Data WarehouseParallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and Parallel Data WarehouseParallel Data Warehouse

Os exemplos a seguir mostram como usar os operadores INTERSECT e EXCEPT.The following examples show how to use the INTERSECT and EXCEPT operators. A primeira consulta retorna todos os valores da tabela FactInternetSales para comparar com os resultados de INTERSECT e EXCEPT.The first query returns all values from the FactInternetSales table for comparison to the results with INTERSECT and EXCEPT.

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

A consulta a seguir retorna qualquer valor distinto retornado pela consulta à esquerda e à direita do operador INTERSECT.The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

-- 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.)  

A consulta a seguir retorna qualquer valor distinto da consulta à esquerda do operador EXCEPT que não seja encontrado também na consulta à direita.The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.

-- 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.)