How can I select rows that are null using bound queries in Perl's DBI?

Question

I want to be able to pass something into an SQL query to determine if I want to select only the ones where a certain column is null. If I was just building a query string instead of using bound variables, I'd do something like:

if ($search_undeleted_only)
{
    $sqlString .= " AND deleted_on IS NULL";
}

but I want to use bound queries. Would this be the best way?

my $stmt = $dbh->prepare(...
    "AND (? = 0 OR deleted_on IS NULL) ");
$stmt->execute($search_undeleted_only);

Answer 1

Yes; a related trick is if you have X potential filters, some of them optional, is to have the template say " AND ( ?=-1 OR some_field = ? ) ", and create a special function that wraps the execute call and binds all the second ?s. (in this case, -1 is a special value meaning 'ignore this filter').

Update from Paul Tomblin: I edited the answer to include a suggestion from the comments.

Answer 2

So you're relying on short-circuiting semantics of boolean expressions to invoke your IS NULL condition? That seems to work.

One interesting point is that a constant expression like 1 = 0 that did not have parameters should be factored out by the query optimizer. In this case, since the optimizer doesn't know if the expression is a constant true or false until execute time, that means it can't factor it out. It must evaluate the expression for every row.

So one can assume this add a minor cost to the query, relative to what it would cost if you had used a non-parameterized constant expression.

Then combining with OR with the IS NULL expression may also have implications for the optimizer. It might decide it can't benefit from an index on deleted_on, whereas in a simpler expression it would have. This depends on the RDBMS implementation you're using, and the distribution of values in your database.

Answer 3

I think that's a reasonable approach. It follows the normal filter pattern nicely and should give good performance.