sql select with argument which can be NULL

Question

What is the best way to have a select query which has an argument which can be NULL depending on some variable in the program?

I can think of two solutions (pseudocode):

bool valueIsNull;
int value;

query = "SELECT * FROM table WHERE field ";

if (valueIsNull)
{
  query += "IS NULL";
}
else
{
  query += "= ?";
}
statement st = sql.prepare(query);

if (!valueIsNull)
{
  st.bind(0, value);
}

or

bool valueIsNull;
int value;

query = "SELECT * FROM table WHERE field = ? OR (? IS NULL AND field IS NULL)";
statement st = sql.prepare(query);
if (valueIsNull)
{
  st.bindNull(0);
  st.bindNull(1);
}
else
{
  st.bind(0, value);
  st.bind(1, value);
}

It is a lot of code for just a simple SELECT statement and I find it just ugly and unclear.

The cleanest way would be something like:

bool valueIsNull;
int value;

query = "SELECT * FROM table WHERE field = ?";   // <-- this does not work
statement st = sql.prepare(query);
st.bind(0, value, valueIsNull);  // <-- this works

Obviously this does not work. But is there a clean way to handle this?

I do not think it matter much but I am using C++, cppdb and postgresql.

Answer 1

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.

Answer 2

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 table
INNER JOIN (SELECT ? AS param1 /* FROM DUAL */) AS params
    ON 1 = 1
WHERE field = param1
    OR COALESCE(param1, field) IS NULL