Geek asks and answers

Sql select with an argument, which may be NULL

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

I can present two solutions (pseudo-code):

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);
}

This is a lot of code for a simple SELECT statement, and I find it just ugly and obscure.

The cleanest way is 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 don't think this matters much, but I use C ++, cppdb and postgresql.

+3
source share
2 answers

With Postgresql (but I consider it not standard) you can use

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

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

+5
source

As you noted, the main problem with this is:

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

, , .

( ) , :

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

More articles:


All Articles