sql select with argument which can be NULL

sql postgresql

With Postgresql (but I believe not standard) you can use

SELECT * from some_table where field IS NOT DISTINCT FROM ?;

IS NOT DISTINCT FROM, unlike plain =, is true when both sides are NULL.

sql postgresql

As you've noted, the main problem with this:

SELECT * FROM table WHERE field = ? OR (? IS NULL AND field IS NULL)

is that it is actually two parameters which need to be bound.

You can get around that with a (should be fairly portable) construct like:

SELECT *FROM tableINNER JOIN (SELECT ? AS param1 /* FROM DUAL */) AS params    ON 1 = 1WHERE field = param1    OR COALESCE(param1, field) IS NULL

CodeHunter