Earlier this week, I had the opportunity to take many different interviews. Most of the DBA and Developers update their resume with various versions of the SQL Server. It seems like everybody had written SQL Server 2005 as one of their expertise. At this point of time, I remembered keyword EXCEPT which was introduced in SQL Server 2005 but not many people know about it. After interviewing, I realized that even after 11 years of release of SQL Server 2005 still most of the people do not know about EXCEPT keyword.
Question: What is the Difference Between EXCEPT and NOT IN?
Answer: The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there is no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.
Example of EXCEPT operator is displayed along Example of NOT IN. If you run both of the Query and compare the Execution plan it is exactly the same. EXCEPT and NOT IN does same functions and have same execution plan but EXCEPT has much simpler syntax. The row-by-row comparison provided by EXCEPT, combined with the number of rows being returned remaining consistent, provides compelling evidence that re-factored query is correct. EXCEPT works with * as well as alias.
Example: (Both of the scripts returns same number of rows)
----SQL SERVER 2005 Method USE AdventureWorks; GO SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder; GO
—-SQL SERVER 2000 Method which works IN SQL SERVER 2005 USE AdventureWorks; GO SELECT ProductID FROM Production.Product WHERE ProductID NOT IN ( SELECT ProductID FROM Production.WorkOrder); GO
Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan. Click on below image to see larger image.
Reference: Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Hi Pinal, another important detail: with EXCEPT we can use more than one column to compare the datasets.
Thank you
Hi Pinal, I tend to use “LEFT JOIN” (T-SQL). How is it different from “Left Anti Semi Join”. Thank you.
A little unfortunate example, as you are checking an Identity column, might be confusing for not very carefull readers. If you were checking any not unique column, then unless you put DISTINCT in the “NOT IN” query, you cannot be sure that the results will be the same.
Here is a good example anyone can run to backup your point:
WITH GoodEnvironmentList
AS (SELECT IQ.EnvironmentId
,IQ.EnvoronementName
FROM ( VALUES ( 1, ‘Dev’), ( 2, ‘Test’), ( 3, ‘Staging’), ( 4, ‘Production’) ) IQ (EnvironmentId, EnvoronementName)
),
ShortEnvironmentList
AS (SELECT IQ.EnvironmentId
,IQ.EnvoronementName
FROM ( VALUES ( 1, ‘Dev’), ( 2, ‘Test’), ( 3, ‘Production’) ) IQ (EnvironmentId, EnvoronementName)
)
SELECT GEL.EnvironmentId
,GEL.EnvoronementName
FROM GoodEnvironmentList GEL
EXCEPT
SELECT SEL.EnvironmentId
,SEL.EnvoronementName
FROM ShortEnvironmentList SEL;
You cannot do this with NOT IN!
Interesting!
you can do the same with NOT IN, if you use CHECKSUM / BINARY_CHECKSUM. The only exception is, that NOT IN has no implicit DISTINCT. On the other hand a checksum compare could be faster, if you have only few rows but tons of columns.
WITH GoodEnvironmentList
AS (SELECT IQ.EnvironmentId
,IQ.EnvoronementName
FROM ( VALUES ( 1, ‘Dev’), ( 2, ‘Test’), ( 3, ‘Staging’), ( 4, ‘Production’) ) IQ (EnvironmentId, EnvoronementName)
),
ShortEnvironmentList
AS (SELECT IQ.EnvironmentId
,IQ.EnvoronementName
FROM ( VALUES ( 1, ‘Dev’), ( 2, ‘Test’), ( 3, ‘Production’) ) IQ (EnvironmentId, EnvoronementName)
)
SELECT GEL.EnvironmentId
,GEL.EnvoronementName
FROM GoodEnvironmentList GEL
WHERE BINARY_CHECKSUM(*)
NOT IN (SELECT BINARY_CHECKSUM(*)
FROM ShortEnvironmentList SEL
)
I hope you missed something important, Except returns distinct result set but Not In does not.
Good Day!!!
Thanks for sharing.
Also, of note with EXCEPT (and INTERSECT) is that the left and right queries must have the exact same columns in the exact same order, and all columns are compared. You cannot pad values you want to ignore with blanks or nulls as the comparison will not match.
Thanks for the comment Joe O’Connor
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product p
WHERE not exists
(
SELECT 1
FROM Production.WorkOrder w
where w.ProductID=p.ProductID);
Also Showing Same execution plan, is it means Except,Not IN and Not Exist all are same ?
Try to run the queries with not unique columns (e.g. the product name or a datetime column instead of the ID). In the EXECPT you should see a DISTINCT SORT that does not occur in the other plans.
Thanks for your reply Thomas.
Another important difference is that, if there are NULL values in the result from subquery, NOT IN will evaluate to UNDEFINED, and query will return empty set. EXCEPT doesn’t have this limitation:
SELECT *
FROM
(VALUES (1), (2), (3), (4), (5), (6)) AS ii(i)
WHERE i NOT IN
(SELECT i
FROM (VALUES (NULL), (2), (3), (4), (5), (6)) AS ii(i))
— returns empty set
SELECT *
FROM
(VALUES (1), (2), (3), (4), (5), (6)) AS ii(i)
EXCEPT
(SELECT i
FROM (VALUES (NULL), (2), (3), (4), (5), (6)) AS ii(i))
— returns: 1
perfect , good post and impressive Comment
For null, not in always fail.
create table #temp1(id int)
create table #temp2(id int)
insert #temp1 select 1
insert #temp1 select 2
insert #temp1 select null
insert #temp1 select null
insert #temp2 select 1
select * from #temp1
except
select * from #temp2
select * from #temp1 where id not in (select id from #temp2)
Good finding.
I am always amazed by the little snippets of code that creep into comments. In this case the creation of a temporary table using SELECT Column from VALUES as Tablename(Column). Intriguing. I had been experimenting with EXCEPT with an extract of around 100,000 records and 30 columns. It works well and resolves the NULL issue but I have to repeat the select 30 columns in the except clause with a WHERE for the records I don’t want. My original filter was WHERE UNIT NOT IN (‘0001’, ‘0002’). EXCEPT works great but is a bit cumbersome with the select statement repeated.
So, I did a LEFT OUTER JOIN on
SELECT Exceptions FROM (VALUES (‘0001’), (‘0002’)) as EX(Exceptions)
and then a WHERE EX(Exceptions) IS NULL.
Its easy to write, resolved the NULL issue and, for some reason, is incredibly fast.
Thanks for sharing your idea.