EXCEPT operator vs NOT IN

Question

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?

Does it do the same? I would like a simple explanation with an example.

Answer 1

There are two key differences between EXCEPT and NOT IN.

EXCEPT

EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause.

It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the left and right hand side of the query

For example, you cannot do:

SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

This would result in the error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

NOT IN

NOT IN does not filter for DISTINCT values and returns all values from the left-hand table that do not appear in the right-hand table.

NOT IN requires you compare a single column from one table with a single column from another table or subquery.

For example, if your subquery was to return multiple columns:

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

You'd get the following error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

However, if the right-hand table contains a NULL in the values being filtered by NOT IN, an empty result set is returned, potentially giving unexpected results.

EXAMPLE

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)

From the above two queries, EXCEPT returns 3 rows from #NewCustomers, filtering out the 1 and 3 that match #ExistingCustomers and the duplicate 8.

NOT IN does not do this distinct filtering and returns 4 rows from #NewCustomers with the duplicate 8.

If we now add in a NULL to the #ExistingCustomers table, we see the same results returned by EXCEPT, however NOT IN will return an empty result set.

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;

Instead of NOT IN, you should really look at NOT EXISTS and there is a good comparison between the two on Gail Shaw's blog .

Answer 2

An addittion to Mark Sinkinson's excellent comment:

NOT IN requires you compare a single column from one table with a single column from another table or subquery.

You can, actually, perform NOT IN with more than one column.
E.g. this is a prefectly legal^* SQL query:

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)

Which will return first_name and last_name of all people who are employees, but are not also managers.

*: but the construction is not yet implemented in SQL Server.

Answer 3

The NOT IN above fails because there needs to be a correlation between the predicates in the main query and the subquery. If you leave it out you get a UNCORRELATED subquery.

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

EXCEPT is better and will handle any null rows without using IS NULL / IS NOT NULL predicates.