IN Clause with NULL or IS NULL

Question

Postgres is the database

Can I use a NULL value for a IN clause? example:

SELECT * FROM tbl_name WHERE id_field IN ('value1', 'value2', 'value3', NULL) 

I want to limit to these four values.

I have tried the above statement and it doesn't work, well it executes but doesn't add the records with NULL id_fields.

I have also tried to add a OR condition but this just make the query run and run with no end in sight.

SELECT * FROM tbl_name WHERE other_condition = bar AND another_condition = foo AND id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL 

Any suggestions?

Answer 1

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work.

(Comment by Marc B)

I would do this for clairity

SELECT * FROM tbl_name WHERE  (id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL) 

Answer 2

Your query fails due to operator precedence. AND binds before OR!
You need a pair of parentheses, which is not a matter of "clarity", but pure logic necessity.

SELECT * FROM   tbl_name WHERE  other_condition = bar AND    another_condition = foo AND   (id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL);

The added parentheses prevent AND binding before OR. If there were no other WHERE conditions (no AND) you would not need additional parentheses. The accepted answer is misleading in this respect.