Simple Postgres SQL query not working for null values (when using IN condition)

Question

I am using Postgres v11+ and want to return a dataset if col1 equals value1, value2, or null.

select * from table1 where col1 in (value1, value2, null);

Only thing is that the rows with null are not coming. I have also tried the reverse:

select * from table1 where col1 not in (value3, value4);

However, still the null rows are not coming. I know you can do this with a separate OR statement, i.e.

select * from table1 where col1 in (value1, value2) or col1 isnull;

But this doesn't work for me as I have additional conditions that get (incorrectly) passed when I use OR. (Basically I'd prefer to do this in a single shot).

What's wrong with postgres to where an in statement doesn't work with null?

Gordon Linoff · Accepted Answer · 2020-10-13 15:27:30Z

2

NULL fails pretty much all comparisons. But it is particularly nefarious with NOT IN: If any element in the NOT IN list is NULL, then the NOT IN never evaluates to "true". It evaluates to either "false" or NULL.

Instead of these:

where col1 in (value1, value2, null);
where col1 not in (value1, value2, null);

You can use:

where (col1 in (value1, value2) or col1 is null);
where col1 not in (value1, value2) ;

null values will fail the second condition anyway.

answered Oct 13, 2020 at 15:27

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Simple Postgres SQL query not working for null values (when using IN condition)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related