Using EXCEPT in SQL

EXCEPT is an Operator in SQL that will return any distinct values found in the "left" table that is not found in the "right" table.  How can this be useful?  Let's say that one day you notice you have some data corruption in your database.  Your Orders table has some orders that are not found in the OrdersDetails table.  How do we easily figure out which orders are missing?  Our initial query could look something like this:

SELECT distinct OrderNumber
from Orders o
LEFT OUTER JOIN OrdersDetails d
on o.OrderNumber = d.OrderNumber
WHERE d.OrderNumber is null

This gets the job done, but doesn't look very organized.  We have to make sure we:

• Have the correct join (LEFT or RIGHT, depending on which table has the rows we want)
• Make sure the NULL is on the correct table (NULL would return where rows don't exist).

An easier and more efficient way to do this is to use the EXIST Operator in SQL:


SELECT OrderNumber
from Orders
EXISTS
select OrderNumber
from OrdersDetails

You will want to make sure that your data types for comparing are the same.  If not, it is possible you will not get expected results.

Where do you think you could use EXCEPT in your organization?

To read more on EXCEPT, go here: SQL EXCEPT