Interview Question of the Week #060 – What is the Difference Between EXCEPT Keyword and NOT IN?

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.

Interview Question of the Week #060 - What is the Difference Between EXCEPT Keyword and NOT IN? SQLSERVER_EXCEPT_ExecPlan_small

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
Interview Question of the Week #059 – What are the Limitations of User Defined Functions (UDF) ?
Next Post
Interview Question of the Week #061 – How to Retrieve SQL Server Configuration?

Related Posts

19 Comments. Leave new

  • Ricardo Camargos Chaves
    February 28, 2016 10:31 am

    Hi Pinal, another important detail: with EXCEPT we can use more than one column to compare the datasets.

    Thank you

    Reply
  • Hi Pinal, I tend to use “LEFT JOIN” (T-SQL). How is it different from “Left Anti Semi Join”. Thank you.

    Reply
  • 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.

    Reply
  • 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!

    Reply
  • Thomas Franz
    March 4, 2016 1:59 pm

    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
    )

    Reply
  • I hope you missed something important, Except returns distinct result set but Not In does not.
    Good Day!!!

    Reply
  • Joe O'Connor
    March 8, 2016 6:56 pm

    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.

    Reply
  • 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 ?

    Reply
    • 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.

      Reply
  • 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

    Reply
  • Mustafa EL-Masry
    April 16, 2016 5:35 am

    perfect , good post and impressive Comment

    Reply
  • 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)

    Reply
  • 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.

    Reply

Leave a Reply

Menu