NOT IN 対 NOT EXISTS
「アンチパターン」と呼ばれるプログラミングサークルで一般的に使用される用語があります。これは、効果がないだけでなく、非常に逆効果になるリスクがある、繰り返し発生する問題への対応を指します。この用語は、1995年にコンピュータープログラマーのAndrew Koenigが著書『Design Patterns』で、信頼性と効果の両方を備えていると考えられているデザインパターンの反対として造られました。
SQLは実際にはプログラム言語ではありませんが、特に問題のクエリがかなり複雑な場合は、アンチパターンの影響を受けやすいことがわかります。時々、間違いは見つけるのが難しく、クエリが実稼働環境の圧力調理器に押し込まれるまで明らかになりません。
SQLの間違いを早期に発見することを目的として、次のいくつかのブログでは、最も一般的な原因のいくつかに焦点を当てます。今日の例を実行するためにMySQLを使用しますが、概念はどの種類のSQLでも同様に有効です。
NOT IN 対 NOT EXISTS
SELECTクエリの一般的なタイプの1つは、値のリストに含まれていないデータを取得するものです。説明のために、Navicat for MySQL 16で作成された2つの非常に単純なテーブルを次に示します。最初のテーブルには色が含まれています。
2番目のテーブルには製品が含まれています。
私たちがやりたいことは、まだどの製品にも関連付けられていない全ての色を選択することです。つまり、その色の製品がない色のみを返すクエリを作成する必要があります。問題のレコードをフェッチするためにNOT IN述語を使用したくなるかもしれません。
次のクエリは2つの行（「black」と「green」）を返すと予想されますが、実際には空の結果セットが返されます。
何が問題でしょうか？productsテーブルのcolor列にNULL値が存在します。これは、NOT IN述語によって次のように変換されます。
color NOT IN (Red, Blue, NULL)
または
NOT(color=Red OR color=Blue OR color=NULL)
「color=NULL」という表現はUNKNOWNと評価され、多くのデータベース開発者が見落としているのは、NOT UNKNOWNもUNKNOWNと評価されるということです。その結果、全ての行が除外され、クエリはゼロ行を返します。
この問題は、要件が変更され、null不許可の列がNULLを許可するように更新された場合にも発生する可能性があります。したがって、最初の設計で列がNULLを許可しない場合でも、状況が変更された場合に、クエリがNULLで正しく機能し続けることを確認する必要があります。
最も簡単な解決策は、INの代わりにEXISTS述語を使用することです。
問題が解決しました！
では、なぜこれが機能するのでしょうか？INキーワードは対応するサブクエリ列の全ての値を比較しますが、EXISTSはtrueまたはfalseを評価します。したがって、IN演算子を使用すると、SQLエンジンは内部クエリからフェッチされた全てのレコードをスキャンします。一方、EXISTSを使用している場合、SQLエンジンは、一致するものが見つかるとすぐにスキャンプロセスを停止します。
結論
よくあるSQLクエリの間違いに関するこの最初の記事では、NOT IN述語の誤った使用から始めて、SELECTクエリでアンチパターンがどのように発生するかについて学びました。
Navicat 16 for MySQLを試したい場合は、こちらから14日間のトライアル版をダウンロードできます。
