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.
exceptoperatorsql serversql-server-2005sql-server-2008-r2
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.
Best Answer
There are two key differences between
EXCEPT
andNOT IN
.EXCEPT
EXCEPT
filters theDISTINCT
values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing aNOT EXISTS
with aDISTINCT
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:
This would result in the error:
NOT IN
NOT IN
does not filter forDISTINCT
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:
You'd get the following error:
However, if the right-hand table contains a
NULL
in the values being filtered byNOT IN
, an empty result set is returned, potentially giving unexpected results.EXAMPLE
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 byEXCEPT
, howeverNOT IN
will return an empty result set.Instead of
NOT IN
, you should really look atNOT EXISTS
and there is a good comparison between the two on Gail Shaw's blog .