【SQL】INNNER JOINではWHERE区はJOINの前に実行されるかも知れない

要約

タイトルは間違っていてJOINより前に(論理的に)実行されることはない。SQLをオプティマイザが最適化する過程でWHEREで絞った状態でJOINされることがあるという話。

本題

qiita.com

• from テーブル指定
• join テーブルの結合
• where 条件指定
• group by グループ化
• sum,avgなど 関数
• having 集計後の絞り込み
• select, distinct 検索
• order by 並べ替え
• limit 取得件数の指定

考察

SQLの順序は上記のようになっている。今回取り上げたいのはjoinとwhereの順番で例えばテーブルaというのが1億レコードあってテーブルbというのが1億レコードあってそれをINNNER JOINする方法は以下のようなクエリになる。(カラムはてきとう)。

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id

このクエリにテーブルaの検索条件を加える。ageというカラムがあるとして50以上の場合を抽出する。

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id
WHERE a.age > 50

このときSQLの実行順序がJOIN -> WHEREならINNNER JOINされたテーブルからWHEREで行を抽出するのかです。aもbもほぼ同一のidのレコードがある場合はwhereで判定すべき文は1億レコードになります。(MySQLの場合は結合はNLJなので計算量はO(MN)になる。WHEREで絞らないとなかなかにしんどい)

オプティマイザの役割

qiita.com

結論を書くとオプティマイザがSQLを書き換えていい感じに最適化された上で実行してくれる。なのでINNER JOINはWHEREより先に実行はされるがSQLの内容通りの実行順序ではなくなる。例えばさっきの例のクエリは以下のように書き換えられる。

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id
    AND a.age > 50

少し思ったのはオプティマイザがこのような選択をしない場合はあるのだろうか？という点。ググってみてもあらゆるDBで最適化は効きそうだったが効かない場合は明示的に最適化後のように書いて上げる必要があったりするのだろうか？誤った統計情報が残ってると起きたりもあったり？？

OUTER JOINの場合は置き換えられるケースもある

SELECT *
FROM a
OUTER JOIN b
    ON a.id = b.id
WHERE a.age > 50

は

SELECT *
FROM a
LEFT OUTER JOIN b
    ON a.id = b.id
    AND a.age > 50

は置き換えが可能。ただしageがテーブルbでの比較の場合は

SELECT *
FROM a
LEFT OUTER JOIN b
    ON a.id = b.id
    AND b.age > 50

は置き換えられない。b.ageがNULLの場合でも外部結合だと結果がNULLのレコードとして帰って来てしまうから。

参考

stackoverflow.com