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

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?

Null is "unknown", it might as well be `1` - we don't know. So we don't know whether the unknown value is in the list and get null (unknown) as result. Makes sense when you look at it this way. [Implementation details for Postgres.](http://stackoverflow.com/questions/19517899/not-in-in-postgresql-not-working/19528722#19528722) — Erwin Brandstetter, Apr 13 '15 at 17:16

score 8 · Accepted Answer · answered Apr 13 '15 at 15:47

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.

score 1 · Answer 2 · answered Apr 13 '15 at 15:48

1

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

answered Apr 13 '15 at 15:48

JFPicard

5,029
3
19
43

1

And since the result is unknown, the condition is false, and nothing gets selected. – Tim Biegeleisen Apr 13 '15 at 15:49

score 0 · Answer 3 · answered Apr 13 '15 at 15:51

When you add NULL into Boolean logic you can actually end up with three values: TRUE, FALSE, and UNKNOWN...

In your where clause you end up with an UNKNOWN, and no which evaluates as false and so... no records. If you did this:

WHERE NULL in (1,2,3,NULL)

You would, again, get an 'Unknown' and no records.

In other words, NULL is not a value that can be evaluated, it's more like a state of a record.

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

3 Answers3

Linked