Compartilhar via

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

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW) Ponto de extremidade de análise de SQL no Microsoft Fabric Warehouse no Microsoft Fabric Banco de Dados SQL no Microsoft Fabric

Retorna linhas distintas comparando os resultados de duas consultas.

EXCETO retorna linhas distintas da consulta de entrada à esquerda que não são produzidas pela consulta de entrada à direita.

INTERSECT retorna linhas distintas que são produzidas pelo operador das consultas de entrada à esquerda e à direita.

Para combinar os conjuntos de resultados de duas consultas que usam EXCEPT ou INTERSECT, as regras básicas são:

  • O número e a ordem das colunas devem ser iguais em todas as consultas.

  • Os tipos de dados devem ser compatíveis.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

<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. As definições das colunas que fazem parte de uma operação EXCEPT ou INTERSECT não precisam ser iguais. Porém, elas devem ser semelhantes na conversão implícita. 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.

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. Para obter mais informações, confira Precisão, escala e comprimento (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.

EXCEPT
Retorna qualquer valor distinto da consulta à esquerda do operador EXCEPT. Esses valores são retornados desde que a consulta à direita não retorne esse valores também.

INTERSECT
Retorna qualquer valor distinto retornado pela consulta à esquerda e à direita do operador INTERSECT.

Comentários

Os tipos de dados de colunas comparáveis são retornados pelas consultas à esquerda e à direita dos operadores EXCEPT ou INTERSECT. Esses tipos de dados podem incluir tipos de dados de caractere com ordenações diferentes. Quando isso acontece, a comparação necessária é executada de acordo com as regras de precedência de ordenação. Se você não puder executar essa conversão, o Mecanismo de Banco de Dados do SQL Server retornará um erro.

Ao comparar valores de colunas para determinar linhas DISTINTAS, dois valores NULL são considerados iguais.

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.

Os nomes ou aliases de coluna nas cláusulas ORDER BY devem referenciar nomes de coluna retornados pela consulta à esquerda.

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.

Se EXCEPT ou INTERSECT forem usados com outros operadores em uma expressão, eles serão avaliados no contexto da seguinte precedência:

  1. Expressões em parênteses

  2. O operador INTERSECT

  3. EXCEPT e UNION avaliados da esquerda para a direita com base em sua posição na expressão

Você pode usar EXCEPT ou INTERSECT para comparar mais de dois conjuntos de consultas. 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.

EXCEPT e INTERSECT não podem ser usados em definições de exibição particionadas distribuídas e notificações de consulta.

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. Desse modo, o uso de EXCEPT e INTERSECT em consultas distribuídas pode afetar desempenho.

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. Também é possível usar um cursor dinâmico ou orientado ao conjunto de chaves com uma operação EXCEPT ou INTERSECT. Quando você faz isso, o cursor do conjunto de resultados da operação é convertido em um cursor estático.

Quando uma operação EXCEPT é exibida usando o recurso Plano de Execução Gráfico no SQL Server Management Studio, a operação é exibida como uma left anti semi join, e uma operação INTERSECT é exibida como uma left semi join.

Exemplos

Os exemplos a seguir mostram o uso dos operadores INTERSECT e EXCEPT. A primeira consulta retorna todos os valores da tabela Production.Product para comparar com os resultados de INTERSECT e 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.

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

-- 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. As tabelas são inversas às do exemplo anterior.

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

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

Os exemplos a seguir mostram como usar os operadores INTERSECT e EXCEPT. A primeira consulta retorna todos os valores da tabela FactInternetSales para comparar com os resultados de INTERSECT e 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.

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

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