NULLを扱った時の不思議な挙動

今読んでいる「達人に学ぶ SQL徹底指南書」の中でNULLの扱い方について出てきた。
これまで「IS NOT NULL書いとけばえんやろ！」ぐらいにしか思ってなかったので、いろいろ衝撃を受けた。

たとえば、以下のようなテーブルがあるとする。

name	score
cat	100
cat	20
cat	50

name	score
dog
dog	20

dog行の1つのscoreがnullになっているとする。
この時、「catのうちdogのscoreには含まれないscoreを持つのを選びたい」と思い、NOT INを利用して検索すると、結果が何も出てこなくなる。

SELECT *
FROM nullable_table1
WHERE score NOT IN (
    SELECT score
    FROM nullable_table2
    WHERE name='dog'
);

これは「NOT INのサブクエリ内にNULLが含まれていると、この条件を論理展開した結果が『... AND unknown』になってしまい、これは必ずfalseになってしまう」からだ。
ちなみに、この論理展開は以下のようになっている。

1. ... WHERE score NOT IN (SELECT score FROM nullable_table WHERE name='dog');
2. ... WHERE score NOT IN (NULL, 20); -- サブクエリの結果を取得
3. ... WHERE NOT (score = NULL OR score = 20); -- INを展開
4. ... WHERE (score <> NULL AND score <> 20); -- NOTを分配
5. ... WHERE (unknown AND score <> 20); -- NULLに対する比較はunknownになる
6. ... WHERE false; -- unknown AND ... => false

このように、最終的に「unknown AND ...」が出てきてしまう。
ちなみに上記のクエリはNOT EXISTSを使用するとうまくいく。

SELECT *
FROM nullable_table1 nt1
WHERE NOT exists(
    SELECT *
    FROM nullable_table2 nt2
    WHERE nt2.name = 'dog' AND nt1.score = nt2.score
);

このNOT INがNULLを含んでいた場合と同じようなことが、ALLを利用した条件をしていた場合にも起きる。

SELECT *
FROM nullable_table1
WHERE score > ALL (
    SELECT score
    FROM nullable_table2
    WHERE name='dog'
);

これについては「SQLにおけるNULLは値や変数ではなく『値がないという状態』を表すもの」であり、NULLを比較対象にすることで不定を表すunknownになり思わぬ結果になることがある、ということ。
対策としては、クエリで工夫することもできるけれども、やっぱりカラムの制約としてNULLが入らないような作りにするのが一番いいと思う。

---

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

posted with amazlet at 17.07.10

ミック
翔泳社
売り上げランキング: 10,012

Amazon.co.jpで詳細を見る