NOT IN について

念の為 NOT IN の使い方について復習しましょう。簡単でわかりやすい使い方は下記になります。

WITH dataset AS (
SELECT *
  FROM (VALUES
          (1, 'hoge'), -- 結果として返されない
          (2, 'fuga')  -- 結果として返される
       ) AS t(user_id, name)
)
-- ここまでテスト用テーブル定義
SELECT *
  FROM dataset
 WHERE user_id NOT IN (1, 3)
;

事前にテーブルを用意しなくて済むようにクエリ内でテーブルを定義しており、下記テーブルとなります。

user_id	name
1	hoge
2	fuga

もちろん上記クエリは結果としてuser_idカラムが2となるレコードのみ返されます。

条件として指定したい 1 や 2 という値を都度書き換える運用は現実的ではないため、基本的には下記のようにサブクエリを NOT IN を組み合わせて使うことが多いでしょう。

WITH dataset AS (
SELECT *
  FROM (VALUES
          (1, 'hoge'), -- 結果として返されない
          (2, 'fuga')  -- 結果として返される
       ) AS t(user_id, name)
)
-- ここまでテスト用テーブル定義
SELECT user_id, name
  FROM dataset
 WHERE user_id NOT IN (SELECT user_id
                        FROM (VALUES
                                (1, 'tokyo'),
                                (3, 'kanagawa')
                             ) AS t(user_id, location)
                      )
;

サブクエリは1と3を返します。一方datasetテーブルのuser_idは1と2となるのでuser_idが2となるレコードが結果となります。この挙動は想定通りかと思います。

なぜか1件も結果を返さない

さて、本記事のメインとなる事象について説明していきます。

下記サンプルクエリを見てください。先程のクエリと差分は NOT IN のサブクエリが2行から3行返すようになっただけです。

WITH dataset AS (
SELECT *
  FROM (VALUES
          (1, 'hoge'),
          (2, 'fuga')  -- 結果として返されそうだが・・・
       ) AS t(user_id, name)
)
-- ここまでテスト用テーブル定義
SELECT user_id, name
  FROM dataset
 WHERE user_id NOT IN (SELECT user_id
          FROM (VALUES
                  (1, 'tokyo'),
                  (3, 'kanagawa'),
                  (null, 'chiba') -- 差分
               ) AS t(user_id, location)
       )
;

user_idが2となる結果をサブクエリが返さないため、結果として user_id が2となるレコードが返されそうですよね。ですがセクションのタイトルにもある通り、このクエリの結果は0件となります。

原因

user_id!=1 AND user_id!=3 AND user_id!=null の評価結果が TRUE となるレコードが結果として返されると考えると良いでしょう。

user_idが2の場合 2!=1 AND 2!=3 AND 2!=null が TRUE になるかどうか確認が行われ、 TRUE AND TRUE AND NULL --> NULL となりますので、user_idが2のレコードは結果として返されません。

見ていただいたらわかるかと思いますが NULL が原因です。NULLかどうか評価する際は IS NULL や IS NOT NULL を使いますよね。 NOT IN のときはそのような考慮がされないので、その結果期待した結果が得られません。

下記SQLで確認して見ると机上ではなく実際に動作確認できるためわかりやすいかもしれませんね。

SELECT (true AND true AND null) AS col1;

回避策

原因がサブクエリが NULL を返すことだったので、下記のように IS NOT NULL にて除外することで期待する結果を取得することができます。

WITH dataset AS(
SELECT *
  FROM (VALUES
          (1, 'hoge'),
          (2, 'fuga')
       ) AS t(user_id, name)
)
-- ここまでテスト用テーブル定義
SELECT user_id, name
  FROM dataset
 WHERE user_id NOT IN (SELECT user_id
                         FROM (VALUES
                                 (1, 'tokyo'),
                                 (3, 'kanagawa'),
                                 (null, 'chiba')
                              ) AS t(user_id, location)
                        WHERE user_id IS NOT NULL -- 回避策として追加 
       )
;

もしくは下記のように NOT EXISTS で代替すると良いでしょう。

WITH dataset AS(
SELECT *
  FROM (VALUES
          (1, 'hoge'),
          (2, 'fuga')
       ) AS t(user_id, name)
)
-- ここまでテスト用テーブル定義
SELECT user_id, name
  FROM dataset
 WHERE NOT EXISTS (SELECT 1
                     FROM (VALUES
                             (1, 'tokyo'),
                             (3, 'kanagawa'),
                             (null, 'chiba')
                          ) AS t(user_id, location)
                    WHERE dataset.user_id=t.user_id
                 )
;