CODEWITHSUNDEEP
sqloracle-databasenullsql-in
Abdelrahman Emam
Abdelrahman Emam

Reputation: 393

The NOT IN with NULL values dilemma in ORACLE SQL

When I used this code

WHEN col1 NOT IN (SELECT col2 FROM table_name) THEN 'something'

it didn't give the expected results knowing that col2 contains a NULL value, Why did this happened ? Does using IN with NULL values messes with data stored in memory or what?

Upvotes: 2

Views: 8089

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is not an issue with Oracle. This is how SQL is defined.

When the subquery returns a NULL value with NOT IN, then no rows match at all. For this reason, I strongly recommend always using NOT EXISTS instead:

WHEN NOT EXISTS (SELECT 1 FROM bst WHERE x.n = bst.p)
     THEN 'Leaf'

As a corollary, I usually use EXISTS instead of IN, even though it does not have this problem.

Why does this occur? NULL means that the value is unknown, not that the value is "missing" or something else.

So, if all the elements have values, this is easy to calculate:

1 NOT IN (1, 2)  --> false
3 NOT IN (1, 2)  --> true

However:

1 NOT IN (1, 2, NULL)  --> false 
3 NOT IN (1, 2, NULL)  --> NULL, because NULL could be equal to "3"

Basically, if any value is NULL, then NOT IN returns either "false" or NULL. Both "false" and NULL are treated the same in WHEN and WHERE.

Upvotes: 8

Related Questions