Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

I just recently learned of the existence of the new "EXCEPT" clause in SQL Server (a bit late, I know...) through reading code written by a co-worker. It truly amazed me!

But then I have some questions regarding its usage: when is it recommended to be employed? Is there a difference, performance-wise, between using it versus a correlated query employing "AND NOT EXISTS..."?

After reading EXCEPT's article in the BOL I thought it was just a shorthand for the second option, but was surprised when I rewrote a couple queries using it (so they had the "AND NOT EXISTS" syntax much more familiar to me) and then checked the execution plans - surprise! The EXCEPT version had a shorter execution plan, and executed faster, also. Is this always so?

So I'd like to know: what are the guidelines for using this powerful tool?

like image 569
Joe Pineda Avatar asked Nov 02 '09 18:11

Joe Pineda


People also ask

What to use instead of not exists in SQL?

Using Joins Instead of IN or EXISTS An alternative for IN and EXISTS is an INNER JOIN, while a LEFT OUTER JOIN with a WHERE clause checking for NULL values can be used as an alternative for NOT IN and NOT EXISTS.

Why Except is used in SQL?

The SQL EXCEPT statement is used to filter records based on the intersection of records returned via two SELECT statements. The records that are common between the two tables are filtered from the table on the left side of the SQL EXCEPT statement and the remaining records are returned.

What is the difference between Except and not in?

The EXCEPT operator is equivalent of the Left Anti Semi Join. NOT IN will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result.

What is the difference between Except and minus in SQL?

There is no difference between Oracle MINUS and SQL Server EXCEPT. They are intended to do the same thing. Show activity on this post. This will check for any result set from the first query, then run the except if there is a result.


2 Answers

EXCEPT treats NULL values as matching.

This query:

WITH    q (value) AS         (         SELECT  NULL         UNION ALL         SELECT  1         ),         p (value) AS         (         SELECT  NULL         UNION ALL         SELECT  2         ) SELECT  * FROM    q WHERE   value NOT IN         (         SELECT  value         FROM    p         ) 

will return an empty rowset.

This query:

WITH    q (value) AS         (         SELECT  NULL         UNION ALL         SELECT  1         ),         p (value) AS         (         SELECT  NULL         UNION ALL         SELECT  2         ) SELECT  * FROM    q WHERE   NOT EXISTS         (         SELECT  NULL         FROM    p         WHERE   p.value = q.value         ) 

will return

NULL 1 

, and this one:

WITH    q (value) AS         (         SELECT  NULL         UNION ALL         SELECT  1         ),         p (value) AS         (         SELECT  NULL         UNION ALL         SELECT  2         ) SELECT  * FROM    q EXCEPT SELECT  * FROM    p 

will return:

1 

Recursive reference is also allowed in EXCEPT clause in a recursive CTE, though it behaves in a strange way: it returns everything except the last row of a previous set, not everything except the whole previous set:

WITH    q (value) AS         (         SELECT  1         UNION ALL         SELECT  2         UNION ALL         SELECT  3         ),         rec (value) AS         (         SELECT  value         FROM    q         UNION ALL         SELECT  *         FROM    (                 SELECT  value                 FROM    q                 EXCEPT                 SELECT  value                 FROM    rec                 ) q2         ) SELECT  TOP 10 * FROM    rec  --- 1 2 3 -- original set 1 2 -- everything except the last row of the previous set, that is 3 1 3 -- everything except the last row of the previous set, that is 2 1 2 -- everything except the last row of the previous set, that is 3, etc. 1 

SQL Server developers must just have forgotten to forbid it.

like image 89
Quassnoi Avatar answered Oct 06 '22 03:10

Quassnoi


I have done a lot of analysis of except, not exists, not in and left outer join. Generally the left outer join is the fastest for finding missing rows, especially joining on a primary key. Not In can be very fast if you know it will be a small list returned in the select.

I use EXCEPT a lot to compare what is being returned when rewriting code. Run the old code saving results. Run new code saving results and then use except to capture all differences. It is a very quick and easy way to find differences, especially when needing to get all differences including null. Very good for on the fly easy coding.

But, every situation is different. I say to every developer I have ever mentored. Try it. Do timings all different ways. Try it, time it, do it.

like image 38
Linda Warner Avatar answered Oct 06 '22 04:10

Linda Warner