『NOT IN』『NOT EXISTS』の検証

『INDEX』のキホンで、否定条件(NOT IN等)はINDEXが効かず性能的に使用は避けるべきと書きました。
確かに「NOT IN」より「NOT EXISTS」を使うべきだとされていることもありますが、しかし昨今のRDBでは、何かと最適化もされているので実のところどうなのでしょうか・・・

この他にも「LEFT JOIN + IS NULL」も代用できますが、これらの違いを各RDBでの性能の違いを検証してみました。

検証用テーブル

『JOIN と SELECT列のサブクエリー』で使ったテーブルを使用しました。

出力内容

製品マスタで、まだ受注されていない製品を抽出する。

求めたい表イメージ

・・・ ・・・
PRODUCT_ID PRODUCT_NAME
P003001 製品名3001
P003002 製品名3002
P003005 製品名3005

抽出結果が2000件。(未受注製品が2000件)
PRODUCT_IDは必須値。


①「NOT IN」で求める

select p.PRODUCT_ID, p.PRODUCT_NAME
from M_PRODUCT p
where p.PRODUCT_ID not in(select PRODUCT_ID from T_ORDER)
order by p.PRODUCT_ID

②「NOT EXISTS」で求める

select p.PRODUCT_ID, p.PRODUCT_NAME
from M_PRODUCT p
where not exists(select * from T_ORDER o where p.PRODUCT_ID = o.PRODUCT_ID)
order by p.PRODUCT_ID

③「LEFT JOIN + IS NULL」で求める

select p.PRODUCT_ID, p.PRODUCT_NAME
from M_PRODUCT p left join T_ORDER o ON p.PRODUCT_ID = o.PRODUCT_ID
where o.PRODUCT_ID is null
order by p.PRODUCT_ID

結果

今回の測定結果では、以下の通り、サーバ系では性能差がないという結果となり、AccessDBは、大きな性能差が生じる結果となりました。

単位:秒

NOT IN NOT EXISTS LEFT JOIN + IS NULL
postgresql-9.6 0.055 0.060 0.057
MySQL15.1 0.048 0.049 0.047
Oracle11g(XE) 0.037 0.051 0.058
SQLServer2016(Expless) 0.061 0.050 0.060
Access2016 773.536 0.048 0.587

以下のRDBで「実行計画」の違いを確認したところ・・・

postgresql・・・「NOT EXISTS」と「LEFT JOIN + IS NULL」が同じ
MySQL・・・「NOT IN」と「NOT EXISTS」が同じ
Oracle・・・全て同じ

今回の結果では、サーバー系RDBでは、性能差に違いが生じることはなく、オプティマイザが最適な実行計画を選択できたのかと思います。

DBのバージョンやテーブルやデータの状態にもより必ずしも最適に選択されないことも考えられるので注意は必要だと思いますが、「NOT IN」はスッキリとした書き方ではあるので、これらのRDBでは性能や実行計画を確認しながら使ってみてもいいのかも知れません。

AcccessDBについては、この通り性能的に使用すべきではない結果となり、また「NOT IN や「IN」では複数カラムの指定もできないので、「NOT EXISTS(EXISTS)」しておくのが適切な選択だと言えるかと思います。

今回の結果では「LEFT JOIN + IS NULL」は、何れのRDBでも特にメリットがない結果になりました。

また、複数条件の場合、INDEXの掛かり方等で性能差は出てくると思うので、何処かで改めて検証できればと思います。

カテゴリーSQL

2 Replies to “『NOT IN』『NOT EXISTS』の検証”

  1. We arе a grouⲣ օf volunteers and starting a new scheme in ⲟur community.
    Yоur web site offered us wіth ᥙseful info to work on. You
    have ɗone an impressive process аnd oսr entire neighborhood
    wіll ρrobably Ƅe thankful to yoս.

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

twelve − seven =