LoginSignup
15
6

More than 1 year has passed since last update.

@syumiwohossu

[SQL]単純にNOT INをNOT EXISTSに置き換えることはできない

Last updated at Posted at 2021-01-17

はじめに

パフォーマンスチューニングの手法の一つとして、サブクエリ＆INの組み合わせをEXISTSに置き換える方法があります。
何も知らなかった僕はそのままNOT INもNOT EXISTSに置き換えれるものだろうと思っていたのですが、ここに罠があったなんて...
結論から言うと nullable なカラムを対象とした場合、NOT IN と NOT EXISTSで挙動が異なります。

INをEXISTSに変換する

まずINをEXISTSに変換してみましょう。


> select * from member;
+----+----------+------+---------------+
| id | name     | age  | prefecture_id |
+----+----------+------+---------------+
|  1 | Tanaka   |   22 | 1             |
|  2 | Morimoto |   21 | NULL          |
|  3 | Sato     |   18 | 1             |
|  4 | Kimura   |   17 | 2             |
|  5 | Shimizu  |   20 | 2             |
|  6 | Hukuda   |   19 | 3             |
|  7 | Wada     |   28 | 4             |
|  8 | Yamamoto |   17 | 5             |
|  9 | Kawasaki |   18 | 5             |
| 10 | Morimoto |   18 | 5             |
| 11 | Hamamoto |   16 | 5             |
+----+----------+------+---------------+

> select * from prefecture;
+----+----------+--------+
| id | name     | region |
+----+----------+--------+
|  1 | Tokyo    | Kanto  |
|  2 | Kanagawa | Kanto  |
|  3 | Chiba    | Kanto  |
|  4 | Osaka    | Kinki  |
|  5 | Hyogo    | Kinki  |
+----+----------+--------+

このようなテーブルがあった時に、「memberの中で成人している人がいる都道府県」を抽出するとします。
つまり、


-- prefecture
+----+----------+--------+
| id | name     | region |
+----+----------+--------+
|  1 | Tokyo    | Kanto  |
|  2 | Kanagawa | Kanto  |
|  4 | Osaka    | Kinki  |
+----+----------+--------+

が出ればいいものとします。
20歳以上のメンバーの都道府県IDのリストをサブクエリで取得し、
このリストに存在しない都道府県を出力すれば良いでしょう。
INを使った場合、

SELECT * 
  FROM prefecture
  WHERE id IN 
      ( SELECT prefecture_id 
          FROM member
          WHERE age >= 20 );

こうなりますがEXISTSを用いて

SELECT * 
  FROM prefecture
  WHERE EXISTS 
      ( SELECT prefecture_id 
          FROM member
          WHERE member.age >= 20
          AND member.prefecture_id = prefecture.id);

とも書けます。
このようにサブクエリを引数にとるようなINではEXISTSを用いた方が多くの場合で高速です。

nullableなカラムでNOT IN句を使用する時

NOT IN句ではnullなカラムが含まれていると直感と外れる動作をするので注意が必要です。

例えば「memberの中で未成年者しかいない都道府県」を抽出するとします。
つまり、


-- prefecture
+----+----------+--------+
| id | name     | region |
+----+----------+--------+
|  3 | Chiba    | Kanto  |
|  5 | Hyogo    | Kinki  |
+----+----------+--------+

が出ればいいものとします。
考え方としては、20歳以上のメンバーの都道府県IDのリストをサブクエリで取得し、
このリストに存在しない都道府県を出力すれば良いでしょう。
これをそのままクエリにすると以下のようになります。

SELECT * 
  FROM prefecture
  WHERE id NOT IN 
      ( SELECT prefecture_id 
          FROM member
          WHERE age >= 20 );

つまり、先ほどのクエリのINをNOT INに変えただけですね。
ところがこのクエリの結果はなんと空です。

nullableなカラムでNOT EXISTSを使用する時

では、これをNOT EXISTSで置き換えてみましょう。
こちらも先ほどのクエリにNOTを付けただけとなります。

SELECT * 
  FROM prefecture
  WHERE NOT EXISTS 
      ( SELECT prefecture_id 
          FROM member
          WHERE member.age >= 20
          AND member.prefecture_id = prefecture.id);

こちらの結果は意図通りの結果となります。


-- prefecture
+----+----------+--------+
| id | name     | region |
+----+----------+--------+
|  3 | Chiba    | Kanto  |
|  5 | Hyogo    | Kinki  |
+----+----------+--------+

なぜでしょうか？
ちなみにmember.id = 2のprefecture_idがnullでなければ、両者は同じ結果を返します。
prefecture_idにnullを禁止するとNOT INはNOT EXISTSにそのまま変換できます。

NOT INとNOT EXISTSの内部処理

この違いは内部でどのような変換作業が行われているのかを知ることでなんとなくわかります。
(参考)　達人に学ぶSQL徹底指南書 第2版　ミック著

NOT IN

SELECT * 
   FROM prefecture
   WHERE id NOT IN ( SELECT prefecture_id FROM member WHERE age >= 20 );

=> WHERE id NOT IN (1,null,2,4)
=> WHERE NOT ((id = 1) or (id = null) or (id = 2) or (id = 4))
=> WHERE id NOT (id = 1) AND NOT (id = null) AND NOT (id = 2) AND NOT (id = 4) -- ド・モルガンの法則
=> WHERE (id <> 1) AND (id <> null) AND (id <> 2) AND (id <> 4)
=> WHERE (id <> 1) AND unknown AND (id <> 2) AND (id <> 4)
=> WHERE unknown

となり、WHERE句は常にtrueではなくなります。
idに何が入ろうが、unknownがANDでつながっている時点で表示されません。
つまり空の結果が帰ってきます。
論理積和演算のやり方は[SQLの3値論](# [補足]SQLの3値論)をご覧ください。

NOT EXISTS

prefecture_id = 1の行が表示されるか否かは、

SELECT * 
  FROM prefecture
  WHERE NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND member.prefecture_id = prefecture.id);
  -- member.id = 1
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE 22 >= 20 AND 1 = 1);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND true);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE true); -- member.id=1のユーザのprefecture.idが帰ってくる
  => NOT true; 
  => false;
  -- member.id = 2
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND null = 1);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND unknown);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE unknown);
  => NOT false; -- サブクエリのWHERE unknownの部分で何も返してこない
  => true;

... 
-- 全員分やってANDで繋ぐとfalseなので表示されない。

prefecture.id = 3の行が表示されるか否かは、

SELECT * 
  FROM prefecture
  WHERE NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND member.prefecture_id = prefecture.id);

  -- member.id = 1
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE 22 >= 20 AND 1 = 3);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND false);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE false); -- 何も帰ってこない
  => NOT false; 
  => true;
  -- member.id = 2
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND null = 3);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND unknown);
  => NOT EXISTS ( SELECT prefecture_id FROM member WHERE unknown);
  => NOT false; -- サブクエリのWHERE unknownの部分で何も返してこない
  => true;

...
-- 全員分やってANDで繋ぐとtrueなので表示される。

EXISTSの場合は必ずtrueかfalseかが帰ってきます。
内部の処理は難しいですが、結果は直感的なものが帰ってくるし、こちらの方が速いので、なるべくEXISTSを使った方がいいのかなと思います。

終わりに

本当にNULL悪さしかしない。。。

[補足]SQLの３値論

例えば「memberの中でprefecture_id = 1でない　かつ　成人している人」を抽出するとします。


SELECT * 
  FROM member
  WHERE prefecture_id <> 1 AND age >= 20;

結果はこのようになります。
prefecture_id = NULLのユーザは結果にありません。

+----+---------+------+---------------+
| id | name    | age  | prefecture_id |
+----+---------+------+---------------+
|  5 | Shimizu |   20 | 2             |
|  7 | Wada    |   28 | 4             |
+----+---------+------+---------------+

なぜでしょうか？

実はmember.id = 2のユーザは内部ではこのように評価されています。

   prefecture_id <> 1 AND age >= 20
=> NULL <> 1 AND 21 >= 20 
=> unknown AND true
=> unknown

このようにnullを含む式は全てunknownとして評価されます。

-- 全部 unknown
null = 1
null = null 
null < 1
1 < null
1 <> null
...

またSQLの世界で真理値は以下のようになります。

  • 否定
NOT true unknown false
false unknown true
  • 論理和
OR true unknown false
true true unknown false
unknown unknown unknown unknown
false false unknown false
  • 論理積
AND true unknown false
true true unknown false
unknown unknown unknown unknown
false false unknown false

そしてWHEREで選択されるレコードはtrueとなるもののみです。
なのでunknownとfalseに評価される行は選択されません。

参考文献

達人に学ぶSQL徹底指南書 第2版　ミック著

15
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
6