title: SQL: Binding an undef (NULL) to the placeholder will not select rows which have a NULL value
body: <p>I'm using perl 5.20 and MySQL 5.7, but I think the question is about SQL in general:</p>
  
  <p><a href="http://search.cpan.org/perldoc?DBI" rel="nofollow">perldoc DBI</a> says:</p>
  
  <blockquote>
    <p>Binding an undef (NULL) to the placeholder will not select rows which have a NULL 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 "WHERE age IS NULL".</p>
  </blockquote>
  
  <p>I don't even know what to google for... My question is: What are the reasons behind <code>= ?</code> not matching a binding to <code>NULL</code>/<code>undef</code>? (Beyond "that is how it is defined and documented".)</p>
  
  <p>I've discovered that MySQL has an <a href="http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to" rel="nofollow">operator <code>&lt;=&gt;</code></a> that allows comparisons with <code>NULL</code> and so:</p>
  
  <pre><code>my $sth = $dbh-&gt;prepare('select count(*) from table where field &lt;=&gt; ?');
  $sth-&gt;execute(345);
  $sth-&gt;execute(undef);
  </code></pre>
  
  <p>both work as expected. Unfortunately, <a href="http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to" rel="nofollow">the doc</a> says:</p>
  
  <blockquote>
    <p>The <code>&lt;=&gt;</code> operator is equivalent to the standard SQL <code>IS NOT DISTINCT FROM</code> operator.</p>
  </blockquote>
  
  <p>And MySQL doesn't support the <code>IS NOT DISTINCT FROM</code> operator :-(. So there seems to be no portable way to do this. Except for the very hackish:</p>
  
  <pre><code>my $sth = $dbh-&gt;prepare('
      select count(*) from table
      where field = ? OR ( ? IS NULL AND field IS NULL )
  ');
  $sth-&gt;execute(345, 345);
  $sth-&gt;execute(undef, undef);
  </code></pre>
  
  <p>or the even more hackish snippet from <a href="http://search.cpan.org/perldoc?DBI" rel="nofollow">perldoc DBI</a></p>
  
  <pre><code>$sql_clause = defined $age? "age = ?" : "age IS NULL";
  $sth = $dbh-&gt;prepare(qq{
    SELECT fullname FROM people WHERE $sql_clause
  });
  $sth-&gt;execute(defined $age ? $age : ());
  </code></pre>
  
  <p>Is there a portable way to do <code>WHERE FIELD = ?</code> and have it do what I mean also with <code>NULL</code>s/<code>undef</code>s? What did I miss?</p>
  
  <p>EDIT: I also came up with this workaround, which especially works great if <code>field</code> is a numeric type such as an <code>INT</code>, so we're sure the string <code>"NULL"</code> is not a possible non-NULL value.</p>
  
  <pre><code>my $sth = $dbh-&gt;prepare('
      select count(*) from table
      where COALESCE(field, "NULL") = COALESCE(?,"NULL")
  ');
  $sth-&gt;execute(345);
  $sth-&gt;execute(undef);
  </code></pre>
  
  <p>But performance goes out the window, as I don't think any indexes can be used....</p>