CODEWITHSUNDEEP
sqlms-access
Jarred Filmer
Jarred Filmer

Reputation: 151

SQL, Find all entires that don't exist in another table

So I've got two tables, A and B, I'm trying to find the values that exist in a column in table A that do not exist in a matching column in table B my current code is:

SELECT A.x
FROM A
WHERE A.x NOT IN (SELECT B.x FROM B);

I have also tried:

SELECT A.x
FROM A
WHERE EXISTS NOT (SELECT B.x FROM B);

But when i run the query, nothing is in the table, I know for a fact that there are values in the "x" column of A that are not in the "x" column of B, but there not showing up.

I have a feeling that I'm doing something extremely dumb, or missing the obviously obvious answer, but I'm to tired and I've been working on this for to long to care anymore, so cheers for any help =)

Upvotes: 2

Views: 5181

Answers (2)

Yuck
Yuck

Reputation: 50865

Have you tried using an OUTER JOIN?

SELECT A.x
FROM A LEFT OUTER JOIN B ON A.x = B.x
WHERE B.x IS NULL

Upvotes: 4

Andomar
Andomar

Reputation: 238296

Could there be a null value in B.x? If so, the not in reads like:

a.x not in (1, 2, 3, null, ...)

Which is shorthand for:

a.x <> 1 and a.x <> 2 and a.x <> 3 and a.x <> null and ...

Because anything <> null evaluates to unknown, the not in condition is never true. The result is an empty rowset. It's an unpleasant side effect of three valued logic that catches even experienced SQL developers by surprise.

One solution is to exclude the null value, like:

SELECT x FROM A WHERE x NOT IN (SELECT x FROM B where x is not null)

For more details, see the Wikidia article on three-valued logic.

Upvotes: 2

Related Questions