☰

NOT IN vs NOT EXISTS 質問する

tag-icon • tag-icon sql sql-server notin
NOT IN vs NOT EXISTS 質問する

これらのクエリのうちどれがより高速でしょうか?

存在しない：

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

または、次の場合は該当しません:

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 に固執するつもりです。

ベストアンサー1

私は常に をデフォルトにしますNOT EXISTS。

現時点では実行プランは同じかもしれませんが、どちらかの列が将来 を許可するように変更された場合、NULLそのNOT INバージョンではさらに多くの作業が必要になります (データ内に が実際に存在しない場合でも)。また、が存在する場合NULLのセマンティクスは、いずれにしても必要なものではない可能性があります。NOT INNULL

Products.ProductIDまたはのどちらも指定され[Order Details].ProductIDていない場合、次のクエリと同様に扱われます。NULLNOT IN

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

正確な計画は異なる可能性がありますが、私のサンプルデータでは次のようになります。

NULLでもない

相関サブクエリは結合に比べて常に「悪い」というのが、かなりよくある誤解のようです。確かに、ネストされたループ プラン (サブクエリが行ごとに評価される) を強制する場合は悪いかもしれませんが、このプランにはアンチ セミ結合論理演算子が含まれています。アンチ セミ結合はネストされたループに限定されず、ハッシュ結合またはマージ結合 (この例のように) も使用できます。

/*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が-ableの場合、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は、結果を返さないことです。プランに追加されているこれを確認するには、追加のアンチセミ結合と行カウントスプールを参照してください。

NULL 1 つ

Products.ProductIDも -able に変更された場合、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)

その理由は、サブクエリが結果をまったく返さない場合(つまり、テーブルが空の場合)を除いて、NULL Products.ProductId結果に a が返されるべきではないためです。その場合は返されるべきです。私のサンプル データのプランでは、これは以下のように別のアンチ セミ結合を追加することによって実装されています。NOT IN[Order Details]

両方ともNULL

この効果はバックリーがすでにリンクしているブログ記事この例では、論理読み取り数が約 400 から 500,000 に増加しています。

さらに、1 つのクエリでNULL行数が 0 に減る可能性があるため、カーディナリティの推定が非常に難しくなります。SQL Server がこれが発生すると想定しているが、実際にはNULLデータに行がない場合は、これが大規模なクエリの一部である場合、残りの実行プランは壊滅的に悪化する可能性があります。不適切なネストされたループにより、例えば高価なサブツリーの繰り返し実行が発生する。

NOT INただし、これは、 -able 列のに対して実行可能な唯一のプランではありませんNULL。この記事ではもう一つデータベースに対するクエリ用AdventureWorks2008。

NOT IN列のNOT NULL、またはNOT EXISTSNULL 可能列または NULL 不可能列に対する の場合は、次のプランが提供されます。

存在しない

列がNULL-ableに変わるとNOT IN、計画は次のようになります。

含まれない - Null

これはプランに追加の内部結合演算子を追加します。この装置はここで説明. これまでの単一の相関インデックス シークを、Sales.SalesOrderDetail.ProductID = <correlated_product_id>外側の行ごとに 2 つのシークに変換するために、ここにすべてあります。追加の 1 つは ですWHERE Sales.SalesOrderDetail.ProductID IS NULL。

これはアンチセミ結合の下にあるため、アンチセミ結合が行を返す場合、2 番目のシークは発生しません。ただし、Sales.SalesOrderDetailに が含まれていない場合はNULL ProductID、必要なシーク操作の数が 2 倍になります。

おすすめ記事