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

by

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.

More Related Contents:

  1. Can an INNER JOIN offer better performance than EXISTS
  2. Is it possible to write single query on two table which are not connected to each other?
  3. Get top 1 row of each group
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. Referring to a Column Alias in a WHERE Clause
  7. SQL query to select dates between two dates
  8. Convert Month Number to Month Name Function in SQL
  9. Create a date from day month and year with T-SQL
  10. SET versus SELECT when assigning variables?
  11. Multiple INSERT statements vs. single INSERT with multiple VALUES
  12. Selecting COUNT(*) with DISTINCT
  13. Generate a resultset of incrementing dates in TSQL
  14. Getting result of dynamic SQL into a variable for sql-server
  15. How to get difference between two rows for a column field?
  16. Better techniques for trimming leading zeros in SQL Server?
  17. SQL – Subtracting a depleting value from rows
  18. What’s the best way to select the minimum value from several columns?
  19. Counting DISTINCT over multiple columns
  20. Splitting delimited values in a SQL column into multiple rows
  21. SELECT FOR UPDATE with SQL Server
  22. varbinary to string on SQL Server
  23. SQL Server 2005 ROW_NUMBER() without ORDER BY
  24. OPENXML with xmlns:dt
  25. How to check which locks are held on a table
  26. When using GETDATE() in many places, is it better to use a variable?
  27. Cumulative Total in MS Sql server [duplicate]
  28. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  29. Compare dates in T-SQL, ignoring the time part
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment