☰

SQL「サブクエリに含まれない場所を選択」は結果を返しません 質問する

tag-icon • tag-icon sql sql-server t-sql
SQL「サブクエリに含まれない場所を選択」は結果を返しません 質問する

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)

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

ベストアンサー1

アップデート：

私のブログの以下の記事では、これらの方法の違いについてさらに詳しく説明しています。

このようなクエリを実行するには、次の 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
        )

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

NULL 可能である場合、NOT INは異なります。なぜなら、の値が を含むリスト内のどの値とも一致しない場合は、が返されるからですIN(したがって、NOT IN) 。NULLNULL

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

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

この条件の結果は、リスト内のすべての比較のブール積です。もちろん、単一の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。列が NULL 値を許可する場合、NOT EXISTSが最も効率的です (これも、それほどではありません)。

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

では、オプティマイザによってを に最適化できないためSQL Server、NOT IN/ のNOT EXISTS方が効率的です。LEFT JOIN / IS NULLANTI JOIN

ではPostgreSQL、と は に最適化されているためよりも効率的ですがLEFT JOIN / IS NULL、はを使用します（サブクエリが大きすぎてハッシュできない場合はのみ）。NOT EXISTSNOT INAnti JoinNOT INhashed subplansubplan

おすすめ記事