IN , NOT IN for null value in oracle

Question

In oracle why "Not in" doesn't work on null values but "IN" works For eg

  with temp(n,p) as (
  select 1,2 from dual union all
  select 3,2 from dual union all
  select 4,6 from dual union all
  select 5,6 from dual union all
  select 2,8 from dual union all
  select 6,8 from dual union all
  select 8,null from dual
  )
1. Select * from temp where n in (2,6,8,null);
2. Select * from temp where n not in (2,6,8,null);

First Statement will give the output = 2,6,8 Second statement will not give any output

Can someone please explain why?

Duplicate of [SQL not displaying null values on a not equals query?](https://stackoverflow.com/questions/8036691/sql-not-displaying-null-values-on-a-not-equals-query) — Kaushik Nayak, Jun 22 '18 at 06:08

score 3 · Answer 1 · answered Jun 22 '18 at 06:09

NOT IN essentially works like this:

col NOT IN (value_a, value_b, value_c)
-- is the same as
col != value_a && col != value_b && col != value_c

If one of the values is null, the whole expression evaluates to null, not true (which you probably expect).

You can read more about it here: https://jonathanlewis.wordpress.com/2007/02/25/not-in/

IN , NOT IN for null value in oracle

1 Answers1