[解決済み] NOT IN vs NOT EXISTS

質問

これらのクエリのうち、どちらが速いか？

NOT EXISTSです。

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Northwind..[Order Details] od 
    WHERE p.ProductId = od.ProductId)

またはNOT IN。

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

クエリ実行計画では、どちらも同じことをすると言っています。 もしそうであれば、どちらが推奨される形なのでしょうか？

これはNorthWindのデータベースを元にしたものです。

[編集]をクリックします。

ちょうどこの役に立つ記事を見つけました。 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

NOT EXISTSで我慢しようかな。

どのように解決するのですか？

私はいつもデフォルトで NOT EXISTS .

現時点では実行計画は同じかもしれませんが、将来どちらかのカラムが変更され NULL となります。 NOT IN のバージョンでは、より多くの作業を行う必要があります（たとえ NULL のセマンティックは、実際にデータ中に存在するものではありません。 NOT IN もし NULL s は は、いずれにせよ、あなたが望むものである可能性は低いでしょう。

どちらも Products.ProductID または [Order Details].ProductID 許す NULL の NOT IN は、次のクエリと同じように扱われます。

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId) 

正確な計画は異なるかもしれませんが、私の例のデータでは、次のようになります。

よくある誤解として、相関サブクエリは結合と比較して常に悪いものであるというものがあります。入れ子ループ計画（サブクエリが行ごとに評価される）を強制する場合、確かにそうなる可能性がありますが、この計画には反半結合論理演算子が含まれています。半結合対策は入れ子ループに限らず、ハッシュやマージ（この例のように）結合を使用することもできます。

/*Not valid syntax but better reflects the plan*/ 
SELECT p.ProductID,
       p.ProductName
FROM   Products p
       LEFT ANTI SEMI JOIN [Order Details] od
         ON p.ProductId = od.ProductId 

もし [Order Details].ProductID は NULL -を使用すると、クエリは次のようになります。

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL) 

この理由は、正しいセマンティクスが [Order Details] には、いずれかの NULL ProductId は結果を返さないことです。プランに追加された、これを検証するための追加のアンチセミジョインと行数スプールを参照してください。

もし Products.ProductID に変更され、さらに NULL -を有効にすると、クエリは次のようになります。

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL)
       AND NOT EXISTS (SELECT *
                       FROM   (SELECT TOP 1 *
                               FROM   [Order Details]) S
                       WHERE  p.ProductID IS NULL) 

その1つの理由は NULL Products.ProductId は結果で返されるべきではありません。 ただし もし NOT IN サブクエリが全く結果を返さなかった場合 (すなわち [Order Details] テーブルが空であることを意味します。） その場合は、そうする必要があります。私のサンプルデータの計画では、これは以下のように別のアンチセミジョインを追加することで実装されています。

その効果を示すのが Buckleyが既にリンクしているブログ記事 . この例では、論理的な読み取り回数が約400回から50万回に増加しています。

さらに、1つの NULL が行数をゼロにする可能性があるため、カーディナリティの推定が非常に困難になっています。SQL Server がこのような事態を想定していたとしても、実際に NULL 行を含むデータでは、これがより大きなクエリの一部である場合、残りの実行計画が壊滅的に悪化する可能性があります。 不適切なネストされたループにより、例えば、高価なサブツリーが繰り返し実行されるような場合。 .

の実行計画はこれだけではありません。 NOT IN の上に NULL -が可能なカラムです。 この記事では別のものを紹介しています に対するクエリで AdventureWorks2008 データベースを使用します。

については NOT IN の上に NOT NULL 列または NOT EXISTS をNullableまたはNon nullableのカラムに対して実行すると、次のようなプランが得られます。

<イグ

に変更すると、カラムが NULL -を有効にします。 NOT IN は次のようになります。

<イグ

これは、プランに余分な内部結合演算子を追加するものです。この器具は ここで説明 . での以前の単一相関インデックスシークを変換するためにあるものです。 Sales.SalesOrderDetail.ProductID = <correlated_product_id> を外側の行あたり2つのシークに変更しました。追加された1つは WHERE Sales.SalesOrderDetail.ProductID IS NULL .

これはアンチセミジョインの下にあるため、アンチセミジョインが行を返した場合、2回目のシークは起こりません。しかし、もし Sales.SalesOrderDetail が含まれていない場合 NULL ProductID の場合、必要なシーク回数が2倍になります。