Meet the EXCEPT Operator
The
EXCEPT operator is a SQL language enhancement introduced in SQL Server 2005. Like the
UNION operator,
EXCEPT combines two
SELECT statements into a single results set. The same rules about number of columns and compatible data types apply equally to
UNION and
EXCEPT.
EXCEPT takes the output from the first (or left-hand side—LHS)
SELECT and compares it with the second (or right-hand side—RHS)
SELECT and returns distinct rows from the LHS
SELECT that are not present in the RHS
SELECT. Think of this processing as similar to the way that a
UNION ignores duplicate rows.
When I first read about the EXCEPT operator, I thought it was cool but I could not actually find a valid reason to use it in production code. In most cases, you can obtain the same results using joins and WHERE clauses. However, EXCEPT becomes very useful when validating a refactored query.
Validate Your Refactored Query
In this example, you use EXCEPT to verify that your refactored query returns the same results, by checking whether the query using EXCEPT returns no rows. If it does, you will know that the queries return the same results:
SELECT * FROM TableA
WHERE EXISTS (SELECT * FROM TableB WHERE TableA.id = TableB.refid)
EXCEPT
SELECT TableA.* FROM TableA
LEFT OUTER JOIN TableB ON TableA.id = TableB.refid
Here are the results:
id textfield
----------- --------------------
(0 row(s) affected)
Great, this returns no rows. That must mean the SQL queries are returning the same results, right? Wrong! EXCEPT works uni-directionally, so it will display only rows from the LHS query that are not in the RHS query. If the RHS query is returning rows that are not in the LHS query, EXCEPT won't return these rows. Therefore, you can test fully only by running the query reversed as well:
SELECT TableA.* FROM TableA
LEFT OUTER JOIN TableB ON TableA.id = TableB.refid
EXCEPT
SELECT * FROM TableA
WHERE EXISTS (SELECT * FROM TableB WHERE TableA.id = TableB.refid)
Here are the results:
id textfield
----------- --------------------
3 Row three
5 Row five
(2 row(s) affected)
This query produces a list of results indicating that when you refactored the query, you changed the results. It also tells you which extra rows are being returned.
Previous Page
