There are at least 5 ways to return data from one table which is not in another table. Two of these are SQL Server 2005 and greater only. This is a post mostly for beginners but hopefully everyone will get something out of it.
Here are the 5 different ways
NOT IN
NOT EXISTS
OUTER JOIN
OUTER APPLY (2005+)
EXCEPT (2005+)
Let's see how this all works
First create these two tables with the Celko approved naming convention.
CREATE
TABLE #testnulls (ID INT)
INSERT
INTO #testnulls VALUES (1)
INSERT
INTO #testnulls VALUES (2)
INSERT
INTO #testnulls VALUES (null)
CREATE
TABLE #testjoin (ID INT)
INSERT
INTO #testjoin VALUES (1)
INSERT
INTO #testjoin VALUES (3)
NOT IN
Run the following Code
SELECT
* FROM #testjoin WHERE ID NOT IN(SELECT ID FROM #testnulls)
What happened? Nothing gets returned! The reason is because the subquery returns a NULL and you can't compare a NULL to anything
Now run this
SELECT
* FROM #testjoin
WHERE
ID NOT IN(SELECT ID FROM #testnulls WHERE ID IS NOT NULL)
That worked because we eliminated the NULL values in the subquery
This also works
SELECT
* FROM #testjoin j
WHERE
j.ID NOT IN(SELECT ID FROM #testnulls n WHERE n.ID = j.ID)
NOT EXISTS
NOT EXISTS doesn't have the problem that NOT IN has. Run the following code
SELECT
* FROM #testjoin j
WHERE
NOT EXISTS (SELECT 1
FROM
#testnulls n
WHERE
n.ID = j.ID)
Everything worked
as expected
LEFT and RIGHT JOIN
Plain vanilla LEFT and RIGHT JOINS
SELECT
j.* FROM #testjoin j
LEFT
OUTER JOIN #testnulls n ON n.ID = j.ID
WHERE
n.ID IS NULL
With a RIGHT Join you just switch the tables around
SELECT
j.* FROM #testnulls n
RIGHT
OUTER JOIN #testjoin j ON n.ID = j.ID
WHERE
n.ID IS NULL
And we can also do a full outer join
SELECT
j.* FROM #testnulls n
FULL OUTER
JOIN #testjoin j ON n.ID = j.ID
WHERE
n.ID IS NULL
AND
j.ID IS NOT NULL
You might wonder why we have LEFT and RIGHT Joins, here is why:
<AttemptToBeFunny>LEFT joins are for people who tend to vote for the democrats, RIGHT joins are for people who tend to vote for Republicans. FULL Joins are for independents/undecided people. </AttemptToBeFunny>
You can be real silly and do a subquery LEFT join
SELECT j.* FROM #testjoin j
LEFT
OUTER JOIN (SELECT ID FROM #testnulls ) n ON n.ID = j.ID
WHERE
n.ID IS NULL
Now let's talk about SQL 2005 and up
OUTER APPLY (SQL 2005 +)
OUTER APPLY is something that got added to SQL 2005
SELECT j.* FROM #testjoin j
OUTER
APPLY
(
SELECT id FROM #testnulls n
WHERE
n.ID = j.ID) a
WHERE
a.ID IS NULL
EXCEPT(SQL 2005 +)
EXCEPT is something that got added to SQL 2005. It basically returns everything from the top table which is not in the bottom table
SELECT * FROM #testjoin
EXCEPT
SELECT
* FROM #testnulls
I am also mentioning INTERSECT since some people might not have seen it before. INTERSECT returns what ever is in both tables(like a regular join)
SELECT * FROM #testjoin
INTERSECT
SELECT
* FROM #testnulls
So there you have it, most likely you already know all these types of joins. If you learned something from this post that is a good thing also.