「入力した項目だけで検索」に IS NOT FALSE を使うなら被検索列が NULL にならないか注意

MySQL

わりとよくある、複数の項目に検索条件が入力できて、入力した項目だけを検索条件として使用し、未入力の項目は検索条件には使わない、というフォーム、クエリビルダのようなものを使わないなら IS NOT FALSE を使うと便利です。

SELECT * FROM t WHERE (
    a = :a AND b = :b AND c = :c
) IS NOT FALSE

NULL と比較すると UNKNOWN となり、UNKNOWN AND TRUE は UNKNOWN、UNKNOWN AND FALSE は FALSE、というSQLの3論理値の特徴を利用しています。

  • a = :a と b = :b が TRUE で c = :c が UNKNOWN なら TRUE AND TRUE AND UNKNOWN で UNKNOWN なので IS NOT FALSE で TRUE
  • a = :a が TRUE で b = :b が FALSE で c = :c が UNKNOWN なら、TRUE AND FALSE AND UNKNOWN で FALSE なので IS NOT FALSE で FALSE

ただこれは被検索列の a や b や c が NOT NULL の場合のみ期待した結果になります。もし被検索列が NULL だと、例えば a が NULL だった場合、:a がどんな値だったとしても結果は a = :a は UNKNOWN になるため :a になにを入力してもヒットします。

具体例 v1

例えばあるテーブルに日付の FROM と TO が記録されていて、入力した FROM と TO と期間が重なるレコードを検索する、ただし FROM や TO が未入力なら条件として使わない、というクエリは IS NOT FALSE で次のように書けます。

SELECT * FROM t WHERE (
        date_from <= :date_to
    AND date_to   >= :date_from
) IS NOT FALSE

がしかし、もし t.date_from が NULL だったとすると :date_to になにを入力しても検索にヒットしてしまいます。

ん？ t.date_from が NULL なら大抵の場合は開始日が無期限というか無限の過去みたいな扱いだろうので、何を入力してもヒットするのは正しいのでは。

というわけでこれは例が悪かったです。むしろ IS NOT FALSE を使えば簡単になる良い例でした。

具体例 v2

例えば、カテゴリとかで整数型な ID でカテゴリテーブルと関連させていて、カテゴリ未設定のときに NULL が入るようにしているとき。

SELECT * FROM t WHERE (
        date_from <= :date_to
    AND date_to   >= :date_from
    AND category   = :category
) IS NOT FALSE

検索条件に「カテゴリ＝フルーツ」とか入れて 検索して、カテゴリが未設定の行が検索結果に表示されると変ですね。

どう書くのがキレイでしょうかね。

/* IS NOT FALSE の外に出して IS NULL を付ける */
SELECT * FROM t WHERE (
        date_from <= :date_to
    AND date_to   >= :date_from
) IS NOT FALSE
AND (category = :category OR :category IS NULL);

/* IS NOT FALSE の内側に置くなら <=> を使う */
SELECT * FROM t WHERE (
        date_from <=  :date_to
    AND date_to   >=  :date_from
    AND (category <=> :category OR :category IS NULL)
) IS NOT FALSE

/* IFNULL でもいいかも */
SELECT * FROM t WHERE (
        date_from <= :date_to
    AND date_to   >= :date_from
    AND category <=> IFNULL(:category, category)
) IS NOT FALSE

さいごに

要するに「入力した項目だけで検索」で IS NOT FALSE を使うときに被検索列が NULL になる可能性があるならその NULL が検索でどのように扱うのかを留意する必要があるということですね。

あと、↑のどの書き方でもインデックスはまあまともに使われないと思うので、実は NULL が来ることはないのだけどコピペで IS NOT FALSE にしてしまってパフォーマンス出ない、ということにならないように注意する必要もあります。