Phill Pafford Phill Pafford - 1 year ago 85
SQL Question

IN Clause with NULL or IS NULL

Postgres is the database

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

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.

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 Source

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

FROM tbl_name
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download