EXCEPT et INTERSECT (Transact-SQL)

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

EXCEPT retourne toute valeur distincte de la requête à gauche mais non trouvée dans la requête à droite.

INTERSECT retourne toute valeur distincte renvoyée tant par la requête à gauche que celle à droite de l'opérande INTERSECT.

Voici deux 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 syntaxe de 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 doivent néanmoins être comparables par le biais d'une conversion implicite. Si les types de données diffèrent, le type utilisé pour effectuer la comparaison et retourner les résultats se déterminé d'après les règles de précédence des types de données.

  Si les types sont les mêmes mais diffèrent en terme de précision, d'échelle ou de longueur, le résultat se détermine d'après les mêmes règles de combinaison d'expressions. 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 colonne de type xml, text, ntext, image ou CLR non binaire définie 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érande mais qui n'est pas retournée par la requête à droite.

• INTERSECT
  Retourne toute valeur distincte retournée tant par la requête à gauche que celle à droite de l'opérande INTERSECT.

Notes

Si les types de données de colonnes comparables retournées par les requêtes à gauche et à droite de l'opérande EXCEPT ou de l'opérande INTERSECT sont de type caractère et que leur classement diffère, la comparaison nécessaire s'effectue selon les règles de précédence de classement. Si cette conversion est impossible, le Moteur de base de données SQL Server retourne une erreur.

Si vous comparez des lignes afin de connaître leurs valeurs distinctes, deux valeurs NULL sont considérées comme égales.

Les noms de colonnes de l'ensemble de résultats retournés par EXCEPT ou INTERSECT sont identiques à ceux retournés par la requête se trouvant à gauche de l'opérande.

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 de l'ensemble 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érande.

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 :

1. expressions entre parenthèses ;

2. opérande INTERSECT ;

3. EXCEPT et UNION évaluée de gauche à droite d'après leur position dans l'expression.

Si EXCEPT ou INTERSECT est utilisé pour comparer plus de deux ensembles de requêtes, 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.

Ces deux opérandes ne peuvent pas être utilisés dans des définitions de vue partitionnée distribuée, dans des notifications de requêtes ou conjointement avec les clauses COMPUTE et COMPUTE BY.

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.

Les curseurs avant uniquement statiques ou rapides sont entièrement pris en charge dans l'ensemble de résultats s'ils sont utilisés avec une opération EXCEPT ou INTERSECT. Si un curseur piloté par jeu de clés ou un curseur dynamique est utilisé avec une opération EXCEPT ou INTERSECT, le curseur de l'ensemble 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, l'opération 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érandes 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.

USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.Product ;
--Result: 504 Rows

La requête suivant retourne toute valeur distincte renvoyée aussi bien par la requête à gauche que celle à droite de l'opérande INTERSECT.

USE AdventureWorks2008R2;
GO
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 qui est renvoyée par la requête se trouvant à gauche de l'opérande EXCEPT, mais qui n'est pas renvoyée par la requête de droite.

USE AdventureWorks2008R2;
GO
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 qui est renvoyée par la requête se trouvant à gauche de l'opérande EXCEPT, mais qui n'est pas renvoyée par la requête de droite. Les tables sont inversées par rapport à l'exemple précédent.

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

Voir aussi

Concepts

Réalisation de semi-jointures avec EXCEPT et INTERSECT

Utilisation de UNION, EXCEPT et INTERSECT avec d'autres instructions Transact-SQL