Operatori sui set - EXCEPT e INTERSECT (Transact-SQL)

Articolo
05/23/2023

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric Warehouse nel database SQL di Microsoft Fabric in Microsoft Fabric

Restituiscono righe distinte eseguendo un confronto dei risultati di due query.

EXCEPT restituisce righe distinte dalla query di input sinistra non generate dalla query di input destra.

INTERSECT restituisce righe distinte generate dall'operatore query di input sinistro e destro.

Le regole di base per la combinazione dei set di risultati di due query che usano EXCEPT o INTERSECT sono le seguenti:

Tutte le query devono includere lo stesso numero di colonne nello stesso ordine.
I tipi di dati devono essere compatibili.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

<query_specification> | ( <query_expression> )
Specifica di query o espressione di query che restituisce dati da confrontare con i dati di un'altra specifica o espressione di query. Le definizioni delle colonne incluse in un'operazione EXCEPT o INTERSECT non devono essere necessariamente identiche. Devono tuttavia essere confrontabili tramite una conversione implicita. Se i tipi di dati sono diversi, le regole per la precedenza dei tipi di dati determinano il tipo di dati eseguito per il confronto.

Il risultato viene determinato in base alle stesse regole previste per la combinazione di espressioni quando i tipi sono gli stessi ma differiscono per precisione, scala o lunghezza. Per altre informazioni, vedere Precisione, scala e lunghezza (Transact-SQL).

La specifica o l'espressione di query non può restituire colonne di tipo xml, text, ntext, image o con un tipo di dati CLR non binario definito dall'utente, perché questi tipi di dati non sono confrontabili.

EXCEPT
Restituisce tutti i valori distinti della query a sinistra dell'operatore EXCEPT. Tali valori vengono restituiti a condizione che non siano restituiti anche dalla query a destra.

INTERSECT
Restituisce tutti i valori distinti restituiti da entrambe le query sul lato sinistro e destro dell'operatore INTERSECT.

Osservazioni:

I tipi di dati delle colonne confrontabili vengono restituiti dalle query a sinistra e a destra dell'operatore EXCEPT o INTERSECT. Questi tipi di dati possono includere tipi di dati Char con regole di confronto diverse. In tal caso, il confronto richiesto viene eseguito in base alle regole di precedenza delle regole di confronto. Se non è possibile eseguire questa conversione, il motore di database di SQL Server restituisce un errore.

Durante il confronto dei valori di colonna per la determinazione delle righe DISTINCT, due valori NULL vengono considerati uguali.

EXCEPT e INTERSECT restituiscono i nomi di colonna del set di risultati che corrispondono ai nomi di colonna restituiti dalla query a sinistra dell'operatore.

I nomi o gli alias di colonna in clausole ORDER BY devono fare riferimento a nomi di colonna restituiti dalla query a sinistra dell'operatore.

Il supporto dei valori Null in qualsiasi colonna nel set di risultati restituito da EXCEPT o INTERSECT equivale a quello della colonna corrispondente restituita dalla query a sinistra dell'operatore.

Se si usa EXCEPT o INTERSECT insieme ad altri operatori in un'espressione, la valutazione avviene in base all'ordine di precedenza seguente:

Espressioni racchiuse tra parentesi
Operatore INTERSECT
EXCEPT e UNION, valutati da sinistra verso destra in base alla relativa posizione nell'espressione

È possibile usare EXCEPT o INTERSECT per confrontare più di due set di query. In tal caso, la conversione dei tipi di dati viene determinata tramite il confronto di due query alla volta e in base alle regole sopra indicate per la valutazione delle espressioni.

Non è possibile usare EXCEPT e INTERSECT nelle definizioni di viste partizionate distribuite e nelle notifiche di query.

EXCEPT e INTERSECT possono essere utilizzati in query distribuite, ma vengono eseguiti solo nel server locale e non è possibile eseguirne il push nel server collegato. Pertanto, l'uso di EXCEPT e INTERSECT nelle query distribuite può influire sulle prestazioni.

È possibile usare i cursori fast forward-only e statici nel set di risultati quando vengono usati con un'operazione EXCEPT o INTERSECT. È anche possibile usare un cursore gestito da keyset o dinamico insieme a un'operazione EXCEPT o INTERSECT. In tal caso, il cursore del set di risultati dell'operazione viene convertito in un cursore statico.

Quando un'operazione EXCEPT viene visualizzata tramite la funzionalità Showplan grafico di SQL Server Management Studio, viene indicata come left anti semi join, mentre un'operazione INTERSECT viene indicata come left semi join.

Esempi

Negli esempi seguenti viene illustrato l'uso degli operatori INTERSECT e EXCEPT. La prima query restituisce tutti i valori della tabella Production.Product per il confronto con i risultati ottenuti con INTERSECT e EXCEPT.

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

La query seguente restituisce tutti i valori distinti restituiti da entrambe le query a sinistra e a destra dell'operatore INTERSECT.

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

La query seguente restituisce tutti i valori distinti della query a sinistra dell'operatore EXCEPT non presenti nella query a destra.

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

La query seguente restituisce tutti i valori distinti della query a sinistra dell'operatore EXCEPT non presenti nella query a destra. Vengono utilizzate tabelle invertite rispetto a quelle dell'esempio precedente.

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

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

Gli esempi seguenti mostrano come usare gli operatori INTERSECT e EXCEPT. La prima query restituisce tutti i valori della tabella FactInternetSales per il confronto con i risultati ottenuti con INTERSECT e EXCEPT.

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