SQLにおけるNULLの扱いを調べ直す

このクエリのおかしなところは？

今、ここにテーブル student_scores があるとしましょう。

CREATE TABLE `sample.student_scores` (
    id INT64,
    name STRING,
    score INT64
);
INSERT INTO `sample.student_scores` (id, name, score) 
VALUES 
(1, '大谷', 100), 
(2, '鈴木', 80),
(3, '吉田', 80),
(4, '千賀', 90),
(5, '松井', NULL);

さて、ここで問題です。

SELECT COUNT(id) AS cnt
FROM `sample.student_scores`
WHERE score <> 80

とした場合、 cnt としてどのような値が返ってくるでしょうか。

正解は…

Untitled

2 です。

3だと思った方もいるのではないでしょうか。

どちらかというと、SQL以外の言語も普段から触るようなエンジニアのほうがこういった罠にハマりやすいと思います。

SQLの真理値の扱い: 3値論理

さて、では、どうしてこのような事になるのか、それはSQLの真理値の扱いにあります。

SQLは真偽値としての TRUE FALSE の他に UNKNOWN を持ちます。

NULL はその UNKNOWN に該当し、値ではないという、ここがとにかくキモになってきます。

結果として、次のような特性があります。

NULLの否定はNULLであり、NOT NULLはNULLを返す
AND演算子がTRUEを返すのは両条件がTRUEのときのみ
OR演算子がTRUEを返すのは少なくとも一方の条件がTRUEのときのみ
比較演算子（=, <>, <, >, <=, >=）はNULLと比較した場合に常にNULLを返す
IS NULLとIS NOT NULLだけがNULLとの比較で唯一TRUEまたはFALSEを返すことができる

今回の違和感はこの4番目のケースに該当します。なんなら、 NULL = NULL も NULLを返します。

だから、NULLの比較を行うときは IS を使うわけですね。

PythonでもNone比較にisを使うけどそれは？

Pythonでも

if a is None:
	print("aはNoneです")

のようにしてNoneとの比較に is を使いますが、これは同じ話でしょうか？

結論から言うと微妙に違います。

Noneの公式ドキュメントを参照するとPythonにおけるNoneはシングルトンオブジェクトであることがわかります。なので、 is でidentityをチェックする、つまり、同一性をチェックしているわけですね。

実際、等価性の確認でも問題無く通るので、Pythonでは == 演算子による比較も通ります。

if a == None:
	print("aはNoneです")

じゃあ == でいいじゃん、となりがちですが、この場合気をつけないといけないのは、 == 自体がoverrideされているようなライブラリが存在するということです。

例えば、pandasの場合、

Untitled

このように、 None な行に対しての == None での比較が False を返していることがわかります。

一方で、 is はオーバーライドできませんので、そういった心配は無いです。

pandasの場合でいうと、 isna() を使って比較してやると

Untitled

といった形で期待していた回答が得られます。

なんにせよ、同一性のチェックのほうが当然厳しいので、そちらが使えるのであればそちらを使っておくべきですよね。実際、Pythonのコーディング規約にあたるPEP8でも is で比較するように記述されています。

PEP8の日本語版ドキュメントではトップページに下記の記述がありますね。

None のようなシングルトンと比較をする場合は、常に is か is not を使うべきです。絶対に等値演算子を使わないでください。

絶対に、とかなり強めの表現になっています。

まとめ: NULLの扱いには気をつけよう

今日はNULLについて掘り下げてみました。今回の話はSQLでのNULLの扱いは気をつけましょう、の代表例だと思います。

この程度は当たり前、となっていれば強いエンジニアですが、世の中にはなんとなくで作業をしてうまくいったりいかなかったりしている方も多いのではないでしょうか。

個人的にはこういった話はかなり好きです。言語設計というのは奥深いものですね。自分で設計しよう、という気にはなかなかなりませんが…達観してくると、そういった気分にもなるのかもしれません(？)