IN Clause with NULL or IS NULL
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.
SELECT *
FROM tbl_name
WHERE coalesce(id_field,'unik_null_value')
IN ('value1', 'value2', 'value3', 'unik_null_value')
So that you eliminate the null from the check. Given a null value in id_field, the coalesce function would instead of null return 'unik_null_value', and by adding 'unik_null_value to the IN-list, the query would return posts where id_field is value1-3 or null.
An
in
statement will be parsed identically tofield=val1 or field=val2 or field=val3
. Putting a null in there will boil down tofield=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)