[SQL] 論理演算子とNULLの挙動まとめ
前提
この記事の内容はJISX3005-2で裏をとっているつもりですが、間違いがあればご指摘ください。記載したSQLはほとんどのデータベースで同じ結果になると思いますが、挙動の確認はBigQueryで行っています。
導入
突然ですがクイズです！全問正解できますか？
Q1
以下のSQL文が返すのは TRUE FALSE NULL のどれでしょう？
SELECT
TRUE OR NULL AS col1,
FALSE AND NULL AS col2,
NULL AND FALSE AS col3
;
正解はcol1から順に TRUE FALSE FALSE です。 NULL と間違えた人もいるのでは？
Q2
次の問題です。以下のSQLが返すのは TRUE FALSE NULL のどれでしょう？
SELECT NULL OR FALSE;
正解は NULL です。おそらく FALSE と迷う人もいたでしょう。
解説
Q1を誤って NULL と答える心理はおそらくこうです。数値演算などにNULLが含まれるとNULLが返る[1]から、論理値の演算も同様だと思い込んでいませんか？
SELECT 1 + CAST(NULL AS INT64); -- NULL
Q2で FALSE と間違った人は、左辺 NULL がfalsyだから（短絡評価できず）右辺の FALSE が評価されて返ってくると思ったのではないでしょうか？例えばPythonでは None or False は False なのでその考えは正しいですが、SQLではさにあらずです。
# python
None or False # False
False or None # None
結論、論理演算子の挙動を表にまとめると以下の通りです。 AND OR については左辺・右辺の順番によらず、結果は同じです。
| (AND) | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | |
| NULL | NULL |
| (OR) | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | FALSE | NULL | |
| NULL | NULL |
| (NOT) | |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | NULL |
「上記の挙動はこういう思想にもとづくからこう覚えるとよい」と説明できればよいのですが、JISX3005-2にも表としてまとまっているだけなので思想を読み取るのが難しいです。こじつけで覚えるなら「TRUE > NULL > FALSE のように順序が決まっていて、 AND は両辺を比較して順序が後のものを返す（ OR は先のものを返す）」[2]などと覚えることができます。
所感
ややこしいから COALESCE() とか使って NULL を避けるのが一番よいのでは？という気もします。それと、以下について説明できる方がいたらぜひ教えてください。
「上記の挙動はこういう思想にもとづくからこう覚えるとよい」と説明できればよいのですが
Discussion