Why is null not in(1,2,3) false [duplicate]

Question

Is it expected that when I test if a null value is not in a list, the result is always false.

That is:

 select 'Hello world' where null not in(1,2,3);

Don't select anything, because null not in(1,2,3) is false.

I don't understand this, because the list(1,2,3) don't contain any undefined values(null) so I would expect the null not in(1,2,3) to be true. So why is it false?

Answer 1

Actually null not in (1,2,3) returns null, not false, but it works like false a where clause (since it's not true).

Null comparisons (a = null), etc. always return null, and since null not in (1,2,3) is essentially the same as:

NOT (null = 1 OR null = 2 OR null = 3)

The return value will be null. Your best bet is to do explicit null checks and handle those cases separately.

Side note: NULL in (null, 1, 2, 3) will return null as well since null = null returns null.

Answer 2

Null is always a special case in SQL. See

http://www.postgresql.org/docs/8.3/static/functions-comparison.html

The ordinary comparison operators yield null (signifying "unknown") when either input is null.

So Null not In (...) will wield Null

You can always use a CASE WHEN to bypass this behavior. See http://www.postgresql.org/docs/7.4/static/functions-conditional.html