Opérateurs de jeu - EXCEPT et INTERSECT (Transact-SQL)

Article
05/23/2023

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Retourne des lignes distinctes en comparant les résultats de deux requêtes.

EXCEPT retourne les lignes distinctes de la requête d’entrée à gauche mais non trouvées par la requête d’entrée à droite.

INTERSECT retourne des lignes distinctes générées par l’opérateur des requêtes d’entrée à gauche et à droite.

Voici les règles essentielles pour combiner les ensembles de résultats de deux requêtes utilisant l'opérande EXCEPT ou l'opérande INTERSECT :

Le nombre et l'ordre des colonnes doivent être identiques dans toutes les requêtes.
Les types de données doivent être compatibles.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

<query_specification> | ( <query_expression> )
Spécification ou expression de requête qui retourne les données à comparer avec les données d'une autre spécification ou expression de requête. Les définitions des colonnes faisant partie d'une opération EXCEPT ou INTERSECT ne doivent pas forcément être identiques. Mais elles doivent néanmoins être comparables par le biais d'une conversion implicite. Lorsque les types de données diffèrent, les règles de priorité des types de données déterminent le type de données exécuté pour la comparaison.

Le résultat se détermine d'après les mêmes règles de combinaison d'expressions si les types sont les mêmes mais diffèrent en terme de précision, d'échelle ou de longueur. Pour plus d’informations, consultez Précision, échelle et longueur (Transact-SQL).

La spécification ou l’expression de requête ne peut pas retourner de colonnes de type xml, text, ntext, image ou CLR non binaire définis par l’utilisateur car ces types de données ne sont pas comparables.

EXCEPT
Retourne toute valeur distincte de la requête à gauche de l’opérateur EXCEPT. Ces valeurs sont retournées tans que la requête de droite ne retourne pas également ces valeurs.

INTERSECT
Retourne toute valeur distincte trouvée par les requêtes à gauche et à droite de l'opérateur INTERSECT.

Remarques

Les types de données de colonnes comparables sont retournés par les requêtes à gauche et à droite des opérateurs EXCEPT ou INTERSECT. Ces types de données peuvent inclure des types de données de caractères avec des classements différents. Dans ce cas, la comparaison nécessaire est exécutée selon les règles de priorité de classement. Si vous ne pouvez pas exécuter cette conversion, Moteur de base de données SQL Server retourne une erreur.

Lors de la comparaison des valeurs de colonnes pour trouver les lignes DISTINCT, deux valeurs NULL sont considérées comme égales.

Les opérateurs EXCEPT et INTERSECT retournent les noms de colonnes du jeu de résultats qui sont les mêmes que les noms de colonnes retournés par la requête du côté gauche de l’opérateur.

Les noms ou les alias de colonnes placés dans des clauses ORDER BY doivent faire référence aux noms de colonnes retournés par la requête de gauche.

La propriété de toute colonne acceptant des valeurs NULL, lesquelles font partie du jeu de résultats retourné par EXCEPT ou INTERSECT, revient à la propriété à accepter des valeurs NULL par sa colonne correspondante retournée par la requête se trouvant à gauche de l'opérateur.

Si EXCEPT ou INTERSECT sont utilisés conjointement avec d'autres opérateurs au sein d'une expression, l'expression finale s'évalue d'après la règle de précédence suivante :

Expressions entre parenthèses
opérateur INTERSECT ;
EXCEPT et UNION évaluée de gauche à droite d'après leur position dans l'expression.

Vous pouvez utiliser EXCEPT ou INTERSECT pour comparer plus de deux jeux de requêtes. Dans ce cas, la conversion de type de données est déterminée en comparant deux requêtes à la fois et en suivant les règles précédemment mentionnées relatives à l'évaluation d'expressions.

EXCEPT et INTERSECT ne peuvent pas être utilisés dans une définition de vue partitionnée distribuée ou des notifications de requêtes.

Vous pouvez toujours les utiliser dans les requêtes distribuées à condition de ne les exécuter que sur un serveur local et de ne pas les envoyer à un serveur lié. Il se peut par conséquent que les performances soient affectées par l'utilisation d'EXCEPT et d'INTERSECT dans des requêtes distribuées.

Vous pouvez utiliser des curseurs avant uniquement statiques ou rapides dans l'ensemble de résultats s'ils sont utilisés avec une opération EXCEPT ou INTERSECT. Vous pouvez également utiliser un curseur de jeu de clés ou un curseur dynamique avec une opération EXCEPT ou INTERSECT. Dans ce cas, le curseur du jeu de résultats de l’opération est converti en curseur statique.

Si une opération EXCEPT est affichée à l’aide de la fonctionnalité Graphical Showplan de SQL Server Management Studio, elle apparaît sous forme de Left Anti Semi Join alors qu’une opération INTERSECT apparaît sous forme de Left Semi Join.

Exemples

Les exemples suivants illustrent l’utilisation des opérateurs INTERSECT et EXCEPT. La première requête retourne toutes les valeurs de la table Production.Product pour qu'elles soient comparées aux résultats à l'aide de INTERSECT et de EXCEPT.

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

La requête suivante retourne toute valeur distincte trouvée par les requêtes à gauche et à droite de l'opérateur INTERSECT.

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

La requête suivante retourne toute valeur distincte trouvée par la requête à gauche de l'opérateur EXCEPT, mais non trouvée par la requête à droite.

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

La requête suivante retourne toute valeur distincte trouvée par la requête à gauche de l'opérateur EXCEPT, mais non trouvée par la requête à droite. Les tables sont inversées par rapport à l'exemple précédent.

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

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

Les exemples suivants illustrent la manière d'utiliser les opérateurs INTERSECT et EXCEPT. La première requête retourne toutes les valeurs de la table FactInternetSales pour qu'elles soient comparées aux résultats à l'aide de INTERSECT et de EXCEPT.

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