SQL における NULL との比較
こんにちは、Snowflake でサポートエンジニアをやっている @indigo13love です。
この記事では、すべてのサンプルクエリが Snowflake の文法で書かれ Snowflake で実行されていますが、基本的に ANSI SQL 標準に書かれている話をしているので、だいたいどの SQL 実装でも(ANSI SQL 準拠であれば)同じような動きになると思います。
突然ですが、下記のようなテーブルがあったとします。
select c1 from t1;
1
2
NULL
NULL でない行だけを取ってこようと考え、下記のように書くと…
select c1 from t1
where c1 != NULL;
何も結果が返ってきません。
おそらく SQL の経験がある方は、この場合、= や != ではなく IS (NOT) NULL を使わなければならないことを知っているでしょう。
select c1 from t1
where c1 is not NULL;
1
2
しかし、なぜ = や != は NULL との比較に使えないかを考えたことはあるでしょうか。
この記事では、どういうロジックでそうなっているかを確認していきます。
NULL との等価比較では一体何が起きているのか
まずは、!= NULL を条件にしたときに何が返ってくるかを確認しましょう。
!= を使った条件は、単純に真偽値を返す式なので、実は SELECT の式リスト側に書くことができます。
select c1 != NULL from t1;
これの結果を見れば、それぞれの行で c1 != NULL 式がどう評価され、結果として「どの行も返さない」という結論になったかがわかります。
で、結果がどうかというと…
NULL
NULL
NULL
すべての行で NULL が返ってきています。!= は TRUE でも FALSE でもなく NULL を返すことがあることがわかりました。
NULL との比較の特性
等価比較における NULL の特性をもう少し掘り下げていきます。今度は NULL の格納された行を抽出しようとしてみます。
select c1 = NULL from t1;
すると、こちらも全行 NULL が返ってきました。
NULL
NULL
NULL
では、今度は右辺を NULL ではない値で実験してみます。c1 = 1 で 1 と一致する行を確認してみます。
select c1 = 1 from t1;
この場合は 1 の行、 2 の行は正しく(直感的に)それぞれ True と False が返っており、 NULL の行だけ NULL が返ってきています。
TRUE
FALSE
NULL
c1 != 1 でも同様の結果です。
select c1 != 1 from t1;
FALSE
TRUE
NULL
また、大小比較においても同様の結果になります。
select c1 > 1 from t1;
FALSE
TRUE
NULL
つまり、左辺であれ右辺であれ NULL を含む比較はすべて NULL を返すということがわかりました。
3 値論理
実はこの動作は SQL における Boolean 型の正しい動作として ANSI SQL 標準にも定義されており、一般的に「3 値論理 (Three-Valued Logic)」と呼ばれます。
ANSI SQL では、 Boolean 型の返す真理値と、 NULL との比較が返す値を下記のように定義しています。
- Boolean 型は True, False, Unknown の 3 種類の真理値を持つ
- NULL との比較、 Unknown 真理値との比較は Unknown 真理値を返す
- Boolean 型では NULL も Unknown 真理値の表現の一つとしてサポートする
つまり、「NULL を含む比較はすべて NULL を返す」という動作は…
- NULL を含む比較はすべて Unknown 真理値を返す (定義 2)
- Unknown 真理値は NULL として表現される (定義 3)
- 結果として NULL を含む比較はすべて NULL を返す
という意味を持っていたことがわかります。
すなわち、「テーブルに格納されている NULL」 は 「空値としての NULL」 であり、「比較式から返ってきた NULL」 は 「Unknown 真理値としての NULL」 であって、違う意味を持つ値だということがわかりました。
さらに WHERE 句の定義が「検索条件が True に評価される行を結果とする」ことになっているため、常に Unknown (NULL) に評価される NULL との比較は、常に結果行に含まれない形となります。
3 値論理における IS (NOT) NULL の扱い
では、IS NULL や IS NOT NULL はなぜ問題なく動作するのでしょうか。
ANSI SQL 標準では比較演算子 (=, !=, <, >) と IS はまったく異なる演算子として定義されています。IS については Boolean 型の定義において AND や OR とともに真理値表として以下のように定義されています。
| IS | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | FALSE | FALSE |
| FALSE | FALSE | TRUE | FALSE |
| UNKNOWN | FALSE | FALSE | TRUE |
ここまででいちばん直感的な結果が出てきました。
NULL は前述の通り Unknown 真理値でもあるので、IS NULL はすなわち IS Unknown であることになり、左辺が NULL (Unknown) のときのみ True を、それ以外の場合は False を返す形となります。
NULL を含む列での適切な比較方法
ここまでで NULL の比較がどう動作するのか、そして IS NULL は正しく動作するということがわかりました。 NULL の行(もしくは NULL でない行)を抽出したい場合には、IS (NOT) NULL を使用すればいいでしょう。
しかし、最初の問題はまだ完全には解決していません。「NULL を含む 1 でない行」を抽出したい場合にはどうすればいいでしょうか。
(c1 is NULL) or (c1 != 1) ... これはあまり綺麗でないので避けたいところです。
SQL にはもちろんこれを実現するための方法が用意されています。以下でいくつかの例を紹介したいと思います。
IS (NOT) DISTINCT FROM / EQUAL_NULL()
1 つ目は IS (NOT) DISTINCT FROM です。
"distinct" とは「まったく別の」「まったく異なる」と言ったような意味の単語で、つまり left IS DISTINCT FROM right は「left と right が異なる値である」、すなわち != に近い意味を持つ句です。
left IS NOT DISTINCT FROM right はその逆で、「left と right が同値である」ことを意味しています。
!= と IS DISTINCT FROM の違いは、後者が NULL-safe である、すなわち NULL との比較で Unknown (NULL) を返さず、NULL かどうかをちゃんと判定してくれるという点です。
実際にクエリで確認してみると、確かに 1 と NULL が違う値である (False) と判定できています。
select c1 is distinct from 1 from t1;
FALSE
TRUE
TRUE
したがって、NULL を含む可能性がある (Nullable な) 列との比較で、かつ NULL の行も値の 1 つとして扱いたい場合には、!= ではなく IS DISTINCT FROM を使用することで、適切に処理することができるようになります。
ちなみに、この IS DISTINCT FROM もちゃんと ANSI SQL 標準で "distinct predicate" として定義されている句なのですが、その定義はわりと泥臭く
- 左辺値も右辺値も NULL だったら False
- 左辺値が NULL で右辺値が NULL でなかったら True
- 右辺値が NULL で左辺値が NULL でなかったら True
と 1 つ 1 つパターンを列挙して定義されています。[1]
また、Snowflake では ANSI SQL 標準に存在しない EQUAL_NULL() という関数も用意しており、これも IS NOT DISTINCT FROM と同様に動作します。
select not equal_null(c1, 1) from t1;
FALSE
TRUE
TRUE
IFNULL() / COALESCE()
もう 1 つは IFNULL() や COALESCE() といった NULL を別の NULL ではない値に変換する関数を使う方法です。
IFNULL() は引数に 2 つの式を取る関数で、1 つ目の引数が NULL だったら 2 つ目の引数を、そうでなければ 1 つ目の引数を返します。
下記の例では IFNULL(C1, 0) で c1 が NULL のときのみ 0 に変換することで、1 との比較が != を使っていても適切に処理できています。
select ifnull(c1, 0) != 1 from t1;
FALSE
TRUE
TRUE
COALESCE() も同じような動きをする関数ですが、複数の(可変長の)引数を取ることができ、最初に NULL でなかった値を返します。
select coalesce(c1, 0) != 1 from t1;
FALSE
TRUE
TRUE
今回の例では IFNULL() との違いがわからないかと思いますが、例えば「A 列が NULL だったら B 列、B 列も NULL だったら文字列リテラル 'foo' を使う」といったような処理を COALESCE(A, B, 'foo') のようにシンプルに記述することができます。
IS DISTINCT FROM と比較しての利点としては、比較以外の操作、例えば文字列操作・集約・結合などに NULL を含み得る列を使用する場合に、考慮点が少なく済み、期待通りの動作を実現するのが簡単になります。
逆に、値を変換するにあたっての処理コストはどうしてもかかってしまうため、シチュエーションに応じて使い分けられるといいでしょう。
-
実際はそれ以降も、普通の値だったら、ユーザ定義型だったら…といろいろな細かいケースの動作が 1 つ 1 つ定義されています。 ↩︎
Discussion