述語の評価順序
このシリーズのパート3の前に、SQLの述語について話しました。述語が、外部結合に関連する間違いの因子となっているためです。よくあるSQLクエリの間違いに関するこのシリーズの最後の記事では、述語の評価順序によって、一見うまく構築されたクエリがエラーで失敗する原因を調べるため、述語が再び登場します。
述語の処理順序のクイックレビュー
論理クエリの処理順序に関して、クエリは次の順序で実行されます。
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
したがって、論理的に言えば、FROM句が最初に処理されて、ソースデータセットを定義します。次に、WHERE述語が適用されて結果セットが絞り込まれ、続いてGROUP BYなどが続きます。
実際には、クエリオプティマイザはデータを取得するための最も効率的な計画を作成するためにクエリ内の式を移動する可能性があるため、述語の評価と処理の順序はあまり厳密ではありません。その結果、WHERE句のフィルターは、次の句が処理される前に適用されない場合があります。実際、述語は、予想する物理的な実行計画よりもはるかに遅れて適用される可能性があります。
データベース開発者にとっての混乱とフラストレーションのもう1つの一般的な原因は、ほとんどのプログラミング言語とは異なり、述語が常に左から右に実行されるとは限らないことです。これは、フィルター"WHERE a=1 AND b=2"を含むWHERE句がある場合、"a=1"が最初に評価されるという保証がないことを意味します。実際、クエリを見ただけでは、どの順序フィルターが実行されるかを簡単に判断する方法はありません。
実用例
述語の評価順序をよりよく理解するために、Navicat 16のテーブルデザイナーに表示される次のaccountsテーブルに対するSELECTクエリを記述します。
クエリの対象となるサンプルデータは次のとおりです。
account_number列では、ビジネスアカウントには数値識別子が割り当てられ、個人アカウントには文字で構成される識別子が割り当てられています。それぞれのアカウントタイプに正しいデータタイプが与えられ、同じテーブルを共有しない場合には、account_number列は2つの異なるフィールドで表されるべきであるため、これは優れたテーブル設計ではありません。ただし、設計の変更が常に可能であるとは限らないため、テーブルをそのまま処理する必要があります。
したがって、これを念頭に置いて、account_numberが50より大きい全てのビジネスタイプのアカウントを取得するクエリを考案しましょう。その結果のクエリは次のようになります。
一部のデータベースでは、クエリによってエラーが発生します。
Conversion failed when converting the varchar value 'ACFB' to data type int
クエリオプティマイザが"account_type LIKE 'Business%'"よりも"CAST(account_number AS UNSIGNED INTEGER) > 50"述語を優先することを決定すると、クエリは失敗するでしょう。上記のようなエラーを回避するための最も安全な方法は、次のいずれかです。
- テーブルを正しく設計し、1つの列に混合データを格納しないようにする
または
- CASE式を使用して、次のように、有効な数値のみがINTEGERデータタイプに変換されることを保証する
結論
よくあるSQLクエリの間違いに関するこのシリーズでは、SQLクエリを構築する一見直感的な方法が、誤った結果やパフォーマンスの低下につながるアンチパターンをどのようにもたらす可能性があるかを探りました。述語の配置と評価の順序は、多くの予期しない問題の原因となるため、特に注意してください。
