[解決済み] SQL "select where not in subquery" は結果を返しません。

質問

免責事項：私は問題を理解した（と思う）ですが、私は（簡単に）どこにも見つけることができなかったので、この問題をStack Overflowに追加したいと思いました。 また、私よりも良い答えを持っている人がいるかもしれません。

私は、1つのテーブル "Common"が他のいくつかのテーブルから参照されているデータベースを持っています。 Commonテーブルのどのレコードが孤児になったか（すなわち、他のどのテーブルからも参照されていない）確認したかったのです。

このクエリを実行しました。

select *
from Common
where common_id not in (select common_id from Table1)
and common_id not in (select common_id from Table2)

孤児になったレコードがあることは知っていますが、レコードは返されませんでした。 なぜでしょうか？

(これはSQL Serverです。重要なら)。

解決方法は？

更新してください。

私のブログでは、これらの方法の違いについて、より詳しく説明しています。

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL : MySQL

このようなクエリを行うには、3つの方法があります。

• LEFT JOIN / IS NULL :

  SELECT  *
  FROM    common
  LEFT JOIN
          table1 t1
  ON      t1.common_id = common.common_id
  WHERE   t1.common_id IS NULL
  
  

• NOT EXISTS :

  SELECT  *
  FROM    common
  WHERE   NOT EXISTS
          (
          SELECT  NULL
          FROM    table1 t1
          WHERE   t1.common_id = common.common_id
          )
  
  

• NOT IN :

  SELECT  *
  FROM    common
  WHERE   common_id NOT IN
          (
          SELECT  common_id
          FROM    table1 t1
          )
  
  

いつ table1.common_id がnullableでない場合、これらのクエリはすべて意味的に同じになります。

null可能な場合。 NOT IN は異なるので IN (そのため NOT IN を返します。 NULL を含むリスト内のどの値にもマッチしない場合、その値を削除することができます。 NULL .

これは混乱するかもしれませんが、この代替構文を思い出せば、より明白になるかもしれません。

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

この条件の結果は、リスト内のすべての比較のブーリアン積である。もちろん、1つの NULL の値は NULL をレンダリングした結果、結果全体が NULL もあります。

とは決して言い切れない。 common_id はこのリストのどれとも等しくない。なぜなら、少なくとも1つの値が NULL .

こんなデータがあったとします。

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL と NOT EXISTS が返されます。 3 , NOT IN を返します。 何もない (と評価されるため）。 FALSE または NULL ).

で MySQL NULLでないカラムの場合。 LEFT JOIN / IS NULL と NOT IN の方が少し（数％）効率が良い。 NOT EXISTS . カラムがNullableの場合。 NOT EXISTS が最も効率的です（繰り返しますが、あまり効率的ではありません）。

で Oracle の場合、3つのクエリはすべて同じプランを生成します。 ANTI JOIN ).

で SQL Server , NOT IN / NOT EXISTS はより効率的です。 LEFT JOIN / IS NULL に最適化することはできません。 ANTI JOIN は、そのオプティマイザによって

で PostgreSQL , LEFT JOIN / IS NULL と NOT EXISTS よりも効率的です。 NOT IN に最適化されているので Anti Join , 一方 NOT IN は hashed subplan (あるいは、プレーンな subplan サブクエリが大きすぎてハッシュ化できない場合)