NOT INの引数にnullが入ると結果がなくなる理由 〜NULLとNOT IN とドモルガンの法則〜

NOT IN (NULL) に苦しめられる昼下がり
sql文のnot inとnull | アララグループの技術者ブログ

上記リンクの方々のように
NULLを含むサブクエリの結果などをNOT INに渡した場合、
期待していたクエリの結果が得られないことがある。

検証

postgresql(9.6.1)で確認

以下のテーブルを準備する

準備

--日本で作ってるフルーツ一覧
CREATE TABLE japan_fruits (name VARCHAR(10));
INSERT INTO japan_fruits VALUES ('りんご'),('ぶどう'),('みかん');

--フィリピンで作ってるフルーツ一覧
CREATE TABLE Philippines_fruits(name VARCHAR(10));
INSERT INTO Philippines_fruits VALUES ('バナナ'),('みかん');

日本でしか作っていないフルーツの一覧(りんご、ぶどう)が欲しい。
以下のクエリを実行する

日本でしか作れないフルーツ一覧

SELECT * FROM japan_fruits
 WHERE NAME NOT IN(
  SELECT name FROM Philippines_fruits
);

結果

  name  
--------
 りんご
 ぶどう
(2 rows)

実行できた。フィリピンは暑いのでりんごとぶどうが作れない。
ただし上記の結果がPhilippines_fruitsにnullのレコードが無いときのみ有効で、
nullのレコードが存在している場合、以下のようになってしまう。

nullのレコードがある場合

insert into philippines_fruits values (null);
INSERT 0 1
postgres=# SELECT * FROM japan_fruits WHERE NAME NOT IN(
select name from Philippines_fruits
);

結果

 name 
------
(0 rows)

解説

日本でしか作れないフルーツ一覧

SELECT * FROM japan_fruits
 WHERE NAME NOT IN(
  SELECT name FROM Philippines_fruits
);

プログラマのためのSQLによれば、結果がなかった時、上記SQLは以下の順序で実行される

サブクエリを評価する

サブクエリの評価

SELECT * FROM japan_fruits
 WHERE NAME NOT IN('バナナ','みかん',null);

NOT INを定義により変換する

NOT_INの変換

SELECT * FROM japan_fruits
 WHERE
  NOT (NAME IN('バナナ','みかん',null));

IN述語を展開する

INの展開

SELECT * FROM japan_fruits
 WHERE
   NOT ((NAME = 'バナナ') OR (NAME = 'みかん') OR (NAME = null));

ド・モルガンの法則を適用する

ド・モルガンの法則

\overline{A \cup B} = \overline{A} \cap \overline{B}

\overline{A \cap B} = \overline{A} \cup \overline{B}

今回はNOT(P OR Q) を (NOT P) AND (NOT Q)に変換する

WHERE句の中の条件
NOT ((NAME = 'バナナ') OR (NAME = 'みかん') OR (NAME = null));
が
(NAME <> 'バナナ') AND (NAME <> 'みかん') AND (NAME <> null) ;
に変換される。

ド・モルガンの法則の適応

SELECT * FROM japan_fruits
 WHERE
  ((NAME <> 'バナナ') AND (NAME <> 'みかん') AND (NAME <> null) );

式を定数に評価する

NULLはIS NULL/IS NOT NULL以外(=,<,>,<>)で比較すると結果はUNKNOWNとなる。

ド・モルガンの法則の適応

SELECT * FROM japan_fruits
 WHERE
  ((NAME <> 'バナナ') AND (NAME <> 'みかん') AND UNKNOWN);

ANDの演算を定数に評価する

UNKNOWNとANDの結果はUNKNOWNまたはFALSEになる。

x	x AND UNKWNON
TRUE	UNKNOWN
FALSE	FALSE

ANDの演算を定数に評価

SELECT * FROM japan_fruits
 WHERE
  UNKNOWNまたはFALSE;

WHERE文の中がTRUEとならないので
レコードが1件も選択されない。

対策

• INの中で選択するカラムにnot null制約をつける
• EXISTSを使う

SELECT * FROM japan_fruits jf
 WHERE NOT EXISTS(
  SELECT 1 FROM Philippines_fruits pf
  WHERE jf.name = pf.name
);

補足

NOT INでレコードにNULLが含まれている時の挙動
こちらの方が紹介しているStack OverFlowにほぼ全てまとまっちゃっていますが、
折角書いたので投稿。