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:
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