2

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?

Abdelrahman Emam
  • 385
  • 1
  • 6
  • 15
  • 2
    In SQL, `NULL` does not compare with anything else. That's why we have `IS NULL` and `IS NOT NULL`. – Álvaro González Mar 21 '20 at 14:20
  • 1
    Please refer this post. https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause – Suresh Gajera Mar 21 '20 at 14:29
  • Thanks a lot It really helped me a lot. – Abdelrahman Emam Mar 21 '20 at 14:44
  • 1
    You ask if IN with NULL values messes with (something). I hope from this experience you will not form the wrong impression, which many developers seem to hold, that IN doesn't work if the "in list" may contain NULL. That is **only** a problem for the **NOT** IN condition; it is not a problem for the **IN** condition. Then: besides using NOT EXISTS instead of NOT IN, as Gordon Linnoff has shown, a different solution (simpler in my opinion) is to use `when col1 not in (select col2 from table_name WHERE COL2 IS NOT NULL)`. –  Mar 21 '20 at 15:55
  • Thanks @mathguy for the great clarification I actually had that impression that the problem with `IN` in general. Many thanks! Actually that's what I did by adding the `WHERE` Clause and the problem is solved. – Abdelrahman Emam Mar 24 '20 at 15:50

1 Answers1

8

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks a lot for your extremely fast answer it really solved a lot of puzzles in my head, also with @Suresh Gajera 's link [NULL values inside NOT IN clause](https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause) I got the full mystery solved thanks alot – Abdelrahman Emam Mar 21 '20 at 14:43