Life, the Universe and the Internet

This week I learned a handy little piece of sequel, which even though you probably already now about, I thought I would share it as I had not seen it before.

The Scenario

Say you had two tables, for example a customer table and an orders table. The tables were structured so that whenever an order was placed it was related to a particular customer through the ID. Pretty straightforward so far right? Well now say you wanted a list of all customers that have never had an order placed against them, so therefore no records will exist in the orders table.

Now I was going to approach this by doing all sorts of complicated outer joins, but then someone should be a much neater solution, it is the NOT IN clause which I had not come across before. So you basically say show me all records where table1’s ID is NOT IN table2. Below is an example using the tables I already mentioned.

SELECT cu.CustomerName,
Cu.CustomerID
FROM dbo.Customers cu
WHERE Cu.CustomerID NOT IN
(SELECT CustomerID FROM dbo.Orders)


Pretty simple eh, and works like a charm. Hope this will be as helpful to someone out there as it was to me.