Skip to content
Tags

,

Interesting NULL issue

February 4, 2020

I had a question today from someone in my team wondering why their code wasn’t returning the correct results. They’d written:

SELECT
      *
FROM TableA;

SELECT
      *
FROM TableA
WHERE 1 = 1
    AND ID1 IN (SELECT ID1 FROM TableB);

SELECT
      *
FROM TableA
WHERE 1 = 1
    AND ID1 NOT IN (SELECT ID1 FROM TableB);

The first query returned about 600 rows, the second returned 300ish, the third returned nothing.

I spotted some NULLs in TableA.ID1, and added an OR ID1 IS NULL to the WHERE clause of the third query. This produced some results but not enough.

We looked at it for a little while longer, and eventually found some NULLs in TableB.ID1. These were causing the problem because a NULL represents unknown values. Therefore, when then query tries to evaluate if any particular value for TableA.ID1 is not in the list of TableB.ID1s from the subquery, it can’t be sure because one of the items in the list has an unknown value.

In summary, if you are doing a NOT IN, make sure you don’t have a NULL in your list anywhere, or the predicate will always return false and (if the predicate is in the WHERE clause) your query won’t return any values.

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: