SQL・テストデータ
IN句にNULLを入れたのに、空の値が見つからないのはなぜ？
使うツール
状態がopenの行と、状態がまだ登録されていない行をまとめて探したい。そこでIN ('open', NULL)と書いたのに、未登録の行が出てこない。これはNULLの綴りや引用符の小さな間違いだけではありません。SQLでは『値がない』を、普通の値と同じ一致判定で扱わないためです。SQL IN句生成を使って、通常の値を探す条件と、NULLを探す条件を分けると、検索したい意味が見える形になります。
画面で空に見える三つの値を分ける
'NULL' -- text consisting of four letters
'' -- empty string
NULL -- SQL null文字列の'NULL'はN、U、L、Lという文字です。空文字は文字数0の文字列、SQLのNULLは値がないことを表します。元データの空欄がどれへ変換されるかは、取り込み処理の規則によります。画面の見た目だけで決めず、列の型と取り込み時の変換規則を先に確認します。この区別を付けずに条件を増やすと、必要な行を拾い損ねるだけでなく、本来別の意味の行まで拾うことがあります。
このツールの行入力では、空行は取り除かれます。空行を一つ挟んだから空文字の条件が追加される、とは考えないでください。また、値は引用符なしで入力し、SQLの引用は生成側に任せます。空文字も探したい場合は、生成した条件とは別に利用先で条件を設計する必要があります。空行、NULLという文字、欠損値を勝手に同一視しないことが出発点です。
NULLはIS NULLへ分けて生成する
open
NULL("state" IN ('open') OR "state" IS NULL)値の型を文字列、列名をstate、方言をpostgresql、NULLの扱いを別条件にします。openはINの中へ、NULLはIS NULLへ分かれます。列名が必要なのは、どの列の欠損値を確認するかを生成側が知るためです。NULLだけの入力なら、通常値用の空のIN句を作らずIS NULLだけになります。括弧まで含めて、どの条件同士がORでつながるかを読みます。
一方、NULLの扱いを文字列にすれば、'NULL'という文字を検索する条件になります。どちらが正しいかはデータの意味によります。取り込み途中のNULL表記を誤って欠損値として扱いたくない場合は、拒否する設定で一度止める方法もあります。エラーを避けることより、何を検索対象とするかを明確にする方を優先します。
一致しない、ではなく判定が不明になる
通常の比較でNULLを使った結果は、単純なtrueやfalseではなく不明になることがあります。WHEREが選ぶのは条件がtrueになる行なので、不明の行は選ばれません。『NULLも一覧に入れたから一致するはず』という直感は、この三つ目の状態を見落としています。NULLを通常の値へ置き換える前に、欠損を欠損として探すIS NULLで目的を表せないかを考えます。
NOT INへ変えるときは、さらに注意する
INで一致しないならNOT INで逆を取ればよい、と思うかもしれません。しかし一覧にNULLが含まれると、通常値に一致しない場合でもNOT INがtrueにならないことがあります。NOTで反転すれば不明が真になるわけではありません。このツールが生成するIN条件を文字置換だけでNOT INへ変え、欠損値も自動的に正しく扱えるとは考えないでください。
まず『除外一覧にない通常値だけ』『欠損値も含める』『欠損値は除く』のどれが目的かを文章で決めます。その上で、利用先のDBで小さいテスト表を使い、通常値、一致値、NULLの三種類の結果を確認します。SheepDevはDBへ接続しないため、ここで生成できたことを実データでの検索成功とは扱いません。
ほかの条件と結合したときの括弧も確認する
active = TRUE AND ("state" IN ('open') OR "state" IS NULL)ORで足したNULL条件の括弧を外すと、別のAND条件との結び付きが変わることがあります。この例ではactiveがtrueであることを、openにもNULLにも要求しています。生成結果を長いSQLへ差し込むときは、SQL整形で読みやすくしてから、条件群の境界を追います。整形しただけで意味が検証されたわけではないので、期待する行の一覧も用意して確かめます。