Pourquoi EXCEPT existe-t-il dans T-SQL?

• INTERSECT et EXCEPT sont des semi-jointures
• la jointure est equi-join

Donc, lorsque vous rejoignez 2 tables qui correspondent, disons, 5 lignes et 3 lignes

• JOIN donne 15 lignes
• L'intersection donne 3 lignes

EXCEPT est similaire à OUTER JOIN pour la même raison

Alors que nous sommes sur les semi-jointures, alors surtout

• INTERSECT donne les mêmes résultats que EXISTS
• EXCEPT donne les mêmes résultats que NOT EXISTS

Le "surtout" vient parce que les deux se croisent et sauf

• traitez NULL différemment: pour un exemple entièrement travaillé, voir ceci par Paul White alias SQL Kiwi
• Appliquer DISTINCT

Modifier, démo rapide de tout cela

DECLARE @t1 TABLE (t1col INT);
INSERT @t1 VALUES (1), (2), (2), (3), (3), (5), (5);

DECLARE @t2 TABLE (t2col INT);
INSERT @t2 VALUES (1), (2), (3), (4);

SELECT 'INNER JOIN', * FROM @t1 t1 JOIN @t2 t2 ON t1.t1col = t2.t2col -- same both ways

SELECT 't1 INTERSECT t2', * FROM @t1 INTERSECT SELECT 't1 INTERSECT t2', * FROM @t2;

SELECT 't2 INTERSECT t1', * FROM @t2 INTERSECT SELECT 't2 INTERSECT t1', * FROM @t1;

SELECT 't1 EXISTS t2', * FROM @t1 t1
WHERE EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);

SELECT 't2 EXISTS t1', * FROM @t2 t2
WHERE EXISTS (SELECT * FROM @t1 t1 WHERE t1.t1col = t2.t2col);

SELECT 't1 LEFT JOIN t2, IS NULL', * FROM @t1 t1 LEFT JOIN @t2 t2 ON t1.t1col = t2.t2col WHERE t2.t2col IS NULL
SELECT 't2 LEFT JOIN t1, IS NULL', * FROM @t2 t2 LEFT JOIN @t1 t1 ON t1.t1col = t2.t2col WHERE t1.t1col IS NULL

SELECT 't1 EXCEPT t2', * FROM @t1 EXCEPT SELECT 't1 EXCEPT t2', * FROM @t2;

SELECT 't2 EXCEPT t1', * FROM @t2 EXCEPT SELECT 't2 EXCEPT t1', * FROM @t1;

SELECT 't1 NOT EXISTS t2', * FROM @t1 t1
WHERE NOT EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);

SELECT 't2 NOT EXISTS t1', * FROM @t2 t2
WHERE NOT EXISTS (SELECT * FROM @t1 t1 WHERE t1.t1col = t2.t2col);

Mise À Jour: Février 2013. Ajout d'une colonne supplémentaire pour décrire l'opération

Vos exemples de requêtes "équivalentes" sont erronés - la requête avec INTERSECT ne renvoie pas toujours le même résultat que INNER JOIN et le même pour EXCEPT et LEFT JOIN.

Regardez un exemple particulier sur INTERSECT:

DECLARE @t TABLE(t INT NOT NULL)
DECLARE @x TABLE(x INT NOT NULL)

INSERT @t
VALUES (1), (2), (3)

INSERT @x VALUES(1), (1), (1)

SELECT t FROM @t
INTERSECT SELECT x FROM @x

SELECT t FROM @t
INNER JOIN @x ON x = t

INTERSECT est plus comme (, mais pas le même) comme IN clause:

SELECT t FROM @t
WHERE t IN (select x FROM @x)

Ou EXISTS

SELECT t FROM @t
WHERE EXISTS (select * FROM @x WHERE x = t)

Les mêmes exemples que vous pouvez adapter à EXCEPT clause.

À mon avis EXCEPT et INTERSECT sont utilisés pour faire les mêmes choses que la commande JOIN, mais c'est plus simple avec des tables qui n'ont pas de clés primaires, par exemple:

Avec INTERSECT:

SELECT FIRSTNAME,
       LASTNAME,
       ADDRESSLINE1,
       CITY,
       STATEPROVINCECODE,
       POSTALCODE
FROM   MANAGER 
EXCEPT
SELECT FIRSTNAME,
       LASTNAME,
       ADDRESSLINE1,
       CITY,
       STATEPROVINCECODE,
       POSTALCODE
FROM   CUSTOMER

Et pour avoir les mêmes résultats avec JOIN, Vous devez faire:

SELECT   M.FIRSTNAME,
         M.LASTNAME,
         M.ADDRESSLINE1,
         M.CITY,
         M.STATEPROVINCECODE,
         M.POSTALCODE
FROM     MANAGER M
WHERE    NOT EXISTS (SELECT *
                     FROM   CUSTOMER C
                     WHERE  M.FIRSTNAME = C.FIRSTNAME
                            AND M.LASTNAME = C.LASTNAME
                            AND M.ADDRESSLINE1 = C.ADDRESSLINE1
                            AND M.CITY = C.CITY
                            AND M.POSTALCODE = C.POSTALCODE)
GROUP BY M.FIRSTNAME,M.LASTNAME,M.ADDRESSLINE1,M.CITY,
         M.STATEPROVINCECODE,M.POSTALCODE 

Plus d'informations ici.