EXCEPT operador vs NOT IN

Existem duas diferenças principais entre EXCEPTe NOT IN.

EXCETO

EXCEPTfiltra os DISTINCTvalores da tabela à esquerda que não aparecem na tabela à direita. É essencialmente o mesmo que fazer um NOT EXISTScom uma DISTINCTcláusula.

Ele também espera que as duas tabelas (ou subconjunto de colunas das tabelas) tenham o mesmo número de colunas no lado esquerdo e direito da consulta

Por exemplo, você não pode fazer:

SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

Isso resultaria no erro:

Todas as consultas combinadas usando um operador UNION, INTERSECT ou EXCEPT devem ter um número igual de expressões em suas listas de destino.

NÃO EM

NOT INnão filtra DISTINCTvalores e retorna todos os valores da tabela à esquerda que não aparecem na tabela à direita.

NOT INrequer que você compare uma única coluna de uma tabela com uma única coluna de outra tabela ou subconsulta.

Por exemplo, se sua subconsulta deveria retornar várias colunas:

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

Você obteria o seguinte erro:

Apenas uma expressão pode ser especificada na lista de seleção quando a subconsulta não é introduzida com EXISTS.

No entanto, se a tabela à direita contiver a NULLnos valores que estão sendo filtrados por NOT IN, um conjunto de resultados vazio será retornado, potencialmente gerando resultados inesperados.

EXEMPLO

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)

Das duas consultas acima, EXCEPTretorna 3 linhas de #NewCustomers, filtrando o 1 e o 3 que correspondem #ExistingCustomerse o 8 duplicado.

NOT INnão faz essa filtragem distinta e retorna 4 linhas #NewCustomerscom a duplicata 8.

Se agora adicionarmos NULLa à #ExistingCustomerstabela, veremos os mesmos resultados retornados por EXCEPT, porém NOT INretornará um conjunto de resultados vazio.

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;

Em vez de NOT IN, você deve realmente olhar NOT EXISTSe há uma boa comparação entre os dois no blog de Gail Shaw .

Uma adição ao excelente comentário de Mark Sinkinson:

NOT IN requer que você compare uma única coluna de uma tabela com uma única coluna de outra tabela ou subconsulta.

Você pode, na verdade, atuar NOT INcom mais de uma coluna. ^{Por exemplo, esta é uma consulta *} SQL
perfeitamente legal :

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)

Que retornará first_namee last_namede todas as pessoas que são funcionários, mas também não são gerentes.

*: mas a construção ainda não está implementada no SQL Server.

O NOT IN acima falha porque precisa haver uma correlação entre os predicados na consulta principal e na subconsulta. Se você deixar de fora, obterá uma subconsulta UNCORRELATED.

SELECT * FROM TableA AS nc WHERE ID NOT IN (SELECT ID, Nome FROM TableB AS ec onde nc.ID = ec.ID)

EXCEPT é melhor e manipulará todas as linhas nulas sem usar predicados IS NULL / IS NOT NULL.