Z WYJĄTKIEM operator vs NIE IN

EXCEPTOperator został wprowadzony w SQL Server 2005, ale jaka jest różnica między NOT INi EXCEPT?

Czy to robi to samo? Chciałbym proste wyjaśnienie z przykładem.

Istnieją dwie kluczowe różnice między EXCEPTi NOT IN.

Z WYJĄTKIEM

EXCEPTfiltruje DISTINCTwartości z tabeli po lewej stronie, które nie pojawiają się w tabeli po prawej stronie. Jest to w zasadzie takie same jak robi NOT EXISTSz DISTINCTklauzuli.

Oczekuje również, że dwie tabele (lub podzbiór kolumn z tabel) będą miały tę samą liczbę kolumn po lewej i prawej stronie zapytania

Na przykład nie można wykonać:

SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

Spowodowałoby to błąd:

Wszystkie zapytania połączone za pomocą operatora UNION, INTERSECT lub EXCEPT muszą mieć taką samą liczbę wyrażeń na swoich listach docelowych.

NIE W

NOT INnie filtruje DISTINCTwartości i zwraca wszystkie wartości z tabeli po lewej stronie, które nie pojawiają się w tabeli po prawej stronie.

NOT IN wymaga porównania pojedynczej kolumny z jednej tabeli z pojedynczą kolumną z innej tabeli lub podzapytania.

Na przykład jeśli twoje podzapytanie miało zwrócić wiele kolumn:

SELECT * FROM TableA AS nc
WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec)

Pojawi się następujący błąd:

Tylko jedno wyrażenie może zostać określone na liście wyboru, gdy podzapytanie nie jest wprowadzane za pomocą EXISTS.

Jeśli jednak prawa tabela zawiera NULLwartości w filtrowanych wartościach, zwracany jest NOT INpusty zestaw wyników, co może dawać nieoczekiwane wyniki.

PRZYKŁAD

CREATE TABLE #NewCustomers (ID INT);
CREATE TABLE #ExistingCustomers (ID INT);

INSERT INTO #NewCustomers
        ( ID )
VALUES
     (8), (9), (10), (1), (3), (8);

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( 1) , (2), (3), (4), (5);


-- EXCEPT filters for DISTINCT values
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN returns all values without filtering
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

Z powyższych dwóch zapytań EXCEPTzwraca 3 wiersze #NewCustomers, odfiltrowując pasujące 1 i 3 #ExistingCustomersoraz duplikat 8.

NOT INnie robi tego wyraźnego filtrowania i zwraca 4 wiersze #NewCustomersz duplikatu 8.

Jeśli teraz dodamy NULLdo #ExistingCustomerstabeli a, zobaczymy te same wyniki zwrócone przez EXCEPT, jednak NOT INzwróci pusty zestaw wyników.

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( NULL );

-- With NULL values in the right-hand table, EXCEPT still returns the same results as above
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN now returns no results
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

DROP TABLE #NewCustomers;
DROP TABLE #ExistingCustomers;

Zamiast tego NOT INpowinieneś naprawdę przyjrzeć się, NOT EXISTSa na blogu Gail Shaw istnieje dobre porównanie między nimi .

Uzupełnienie doskonałego komentarza Marka Sinkinsona:

NOT IN wymaga porównania pojedynczej kolumny z jednej tabeli z pojedynczą kolumną z innej tabeli lub podzapytania.

W rzeczywistości możesz wykonywać NOT INz więcej niż jedną kolumną.
Np. Jest to idealnie legalne ^* zapytanie SQL:

SELECT  E.first_name, E.last_name
FROM    employees E
WHERE   (E.first_name, E.last_name) NOT IN 
              (SELECT M.first_name, M.last_name FROM managers M)

Który powróci first_namei last_namewszystkich ludzi, którzy są pracownikami, ale nie są również menedżerami.

*: ale konstrukcja nie jest jeszcze zaimplementowana w SQL Server.

Powyższe NOT IN kończy się niepowodzeniem, ponieważ musi istnieć korelacja między predykatami w głównym zapytaniu i podzapytaniu. Jeśli go pominiesz, otrzymasz niekwerendowane podzapytanie.

WYBIERZ * Z Tabeli A AS nc GDZIE NIE JEST ID (WYBIERZ ID, Nazwa Z Tabeli B AS ec gdzie nc.ID = ec.ID)

Z WYJĄTKIEM jest lepszy i obsłuży wszystkie wiersze zerowe bez użycia predykatów IS NULL / IS NOT NULL.