How can I select rows that are null using related queries in Perl DBI?

Question

I want to be able to pass something into an SQL query to determine if I want to select only those where a particular column is NULL. If I were just building a query string instead of using related variables, I would do something like:

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

but I want to use related 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 that if you have X potential filters, some of which are optional, you must have the template " AND ( ?=-1 OR some_field = ? ) " and create a special function that completes the execute call and binds all the others . (in this case, -1 is a special value meaning "ignore this filter").

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

Answer 2

So, do you rely on the short-circuited semantics of Boolean expressions to trigger the IS NULL condition? This seems to work.

One interesting point is that a constant expression of type 1 = 0 , which had no parameters, should be taken into account by the query optimizer. In this case, since the optimizer does not know whether the expression is true or false until runtime, this means that it cannot exclude it. It should evaluate the expression for each line.

Thus, it can be assumed that this will add a negligible cost to the query as to what it will cost if you used a non-parameterized constant expression.

Then combining with OR with the expression IS NULL can also have consequences for the optimizer. He may decide that he cannot benefit from the index on deleted_on , while in a simpler expression he will have. It depends on the RDBMS implementation you use and the distribution of values ​​in your database.

Answer 3

I think a reasonable approach. It follows a normal filter well and should provide good performance.