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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityThe 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.
There are two key differences between EXCEPT
and NOT IN
.
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
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.
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 .
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.
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.