view release on metacpan or  search on metacpan

DBI.pm  view on Meta::CPAN

C<WHERE> clause.  Consider:

  SELECT fullname FROM people WHERE age = ?

Binding an C<undef> (NULL) to the placeholder will I<not> select rows
which have a NULL C<age>!  At least for database engines that
conform to the SQL standard.  Refer to the SQL manual for your database
engine or any SQL book for the reasons for this.  To explicitly select
NULLs you have to say "C<WHERE age IS NULL>".

A common issue is to have a code fragment handle a value that could be
either C<defined> or C<undef> (non-NULL or NULL) at runtime.
A simple technique is to prepare the appropriate statement as needed,
and substitute the placeholder for non-NULL cases:

  $sql_clause = defined $age? "age = ?" : "age IS NULL";
  $sth = $dbh->prepare(qq{
    SELECT fullname FROM people WHERE $sql_clause
  });
  $sth->execute(defined $age ? $age : ());