条件はwhere句に書くべきか？

Question

お世話になっております。
条件はwhere句、結合条件はon句に書くと思いますが、
全てをon句に書くのはよくないでしょうか？

個人的な意見ですが、テーブル数が多い場合、
from句に指定されたテーブルはwhere句、それ以外のjoinで指定されたテーブルの条件は全てon句に書いた方が、どのテーブルの属性なのかはっきりするので見やすいと思っています。

外部結合が入らないことと、実行計画に変わりがないことが前提ですがみなさんどうしていますか？

Answer 1

外部結合の場合は「実行結果」が変わってしまうので、使い分ける必要があります。

OUTER JOINのONに書く条件とWHERE句に書く条件とでは、条件の意味合いが違うんだな

内部結合の場合は実行結果は変わりません。

「実行計画」については、変わる可能性があります。SQLの文法上の意味としては、ONに記述する条件は結合の際に適用され、WHEREに記述する条件は結合した後の結果に適用されることになっているからです（外部結合で結果が変わる原因）。しかし、最近のデータベースのオプティマイザであれば内部結合の場合は結果が同じになることを理解しており、EXPLAINしてみるとWHERE句に記述した条件も通常効率がよくなる結合の際 or 前に適用されているはずです。実際に実行計画が変わることはほぼないと考えています

私が内部結合のSQLを書く場合は、論理データモデルとして結合の中心として考えるテーブル、通常FROM句の最初に指定するテーブルに適用する条件であれば、意味合いとしてそれは結合条件というよりは抽出条件であると捉えてWHERE句に記述します（実際にどれを駆動表とするかはオプティマイザが選択するのですが）。

Comments

[lupus_dingo]

回答ありがとうございます。
すみません、join は内部結合限定です。説明不足で申し訳ありません。

またDBはoracle11gのためCBOによりチューニングが入っているため実行計画が最適化されています。

Answer 2

一番理解されるのはベン図と同じように記述することだと思います。

ですが、そのままの記述とすると冗長だったりするので、記述する際には頭の片隅にある程度ですね。

私の回答としては、whereとjoinどちらに記述するかは、読みやすさや好み以前に、実行計画を最優先です。

terateilでの回答する場合なども、実際の実行計画など知る由もない訳ですが、チューニングするとしても追加・変更が少なくて済みそうな記述をある程度は意識しています。

過去にSQLに対して修正が行われた時に、性能が劣化した時がありました。
内容を確認すると、修正内容は条件の追加で、サブクエリーでネストしているSQLだったので、該当するネスト内にwhere条件を追加する修正を行っていました。
稼働後だったので、インデックスの追加は行わず、条件をネストの一番外側になるように修正。

上記などは、後から見ると説明なしでは、なんで？ってなりますよね。
もう一度、修正の機会があって読みやすさを考えて記述の修正だけをされたりしたら、逆戻りです。

なので、そのような記述をおこなう理由の説明も重要ということで。

oracle 優先ということならご存知かもしれませんが、以下参考に。
第9回 良いSQLについて

Comments

[lupus_dingo]

回答ありがとうございます。
もちろん実行計画が最優先ですね。
私の環境では、sqlは結合しようがin句にしようがexistsにしようが実行計画が変わらなかったので質問しました。
副問合せのネストは特に実行計画がおかしなことになる可能性が高いので出来るなら全て並列化したいですね。

[sazi]

DATABASE LINK で異なるインスタンスのテーブル同士の結合だったら、多分実行計画変わると思いますよ。

[lupus_dingo]

そうなんですね。知りませんでした。
私の環境では他スキーマが多い割にdbリンクを使う機会はほとんどないですが、
変わるというのはdbリンクを含んだsqlの場合、実行計画が安定しないということでしょうか？

[sazi]

単純にテーブル同士をjoinすると、極端に言うとjoinの為に１件ごとにトラフィックが発生している感じで、非常に時間が掛かっていました。
なので、リンク先のテーブルを(select * from xxx)のようなサブクエリーと結合することで、トラフィックの発生回数を抑えることができました。
スナップショットであれば、そういうことは無いでしょうけど。
かなり前なので、改善されているかもしれませんけれど。

[lupus_dingo]

必要な他スキーマのテーブルを直接dbリンクで結合するのではなく、そのスキーマ内で必要なテーブルを全てまとめたビューをdbリンクとして結合するということでしょうか？

ちなみにスナップショット知らなかったのでみましたが、マテビューみたいなものでしょうか。

[sazi]

ビューではありません。サブクエリーです。
DATABASE LINKやスナップショットはここでは書ききれないので、御自身で調べてみて下さい。

[lupus_dingo]

直接join xxx@linkではなく、
join (select * from xxx@link)
ということでしょうか？

[sazi]

そうです。
join している項目であっても、データ量を減らすために、サブクエリー内でのwhere条件で絞り込んだりすることもあります。

[lupus_dingo]

ありがとうございます。
参考にします。

Answer 3

大抵のDBエンジンは実行計画の最適化をするのでどうやっても内部的にはかわらないようです
Oracleはよくわかりませんが、たとえばMySQLなどだとテーブル同士の直接リレーションしない
データをorで検索する時若干処理効率がかわるようです

select * from a inner join b on a.x=b.x and (a.y=1 or b.z=2);
select * from a inner join b on a.x=b.x where a.y=1 or b.z=2;

まぁあまり想定されないような使い方なので気にすることはないと思います

Comments

[lupus_dingo]

回答ありがとうございます。
mysqlでも最適化されるんですね。
sqlを組み立てる場合はとりあえず実行計画を確認するのがよいですね。

Answer 4

実行結果は変わりますよ。
バグの元になっているのに気が付かないでいる人もいます。
外部結合におけるONとWHEREの違い
ON 句は結合条件、WHERE 句は抽出条件

Comments

[lupus_dingo]

回答ありがとうございます。
すみません、join は内部結合限定です。説明不足で申し訳ありません。

[Orlofsky]

内部結合で設計したけど実際にデータがそろったら外部結合にしないといけなかったとか、その逆の場合も良くあることです。その時バグを起こしにくいように準備しておくことも大切なことです。

[lupus_dingo]

ありがとうございます。心がけます。

Answer 5

実行計画に変わりがないことが前提

「実行結果に変わりがない」の間違いでしょうか？
条件をwhereまたはonにすることで実行計画が変わると思います。
経験上、MySQLではあまり使うことがないですが、PostgreSQLでは速度が劇的に改善することがあったような。

例えばテーブルAとBの1万件同士を結合して結果が10件のクエリを考えると

• AとBを結合した1万件のデータからwhere条件に一致する10件を抽出
• Bをonの条件で10件抽出してからAと結合

このような方法で抽出することが可能になるのではないでしょうか。後者のほうが速そうです。

Comments

[lupus_dingo]

回答ありがとうございます。
すみません、join は内部結合限定です。説明不足で申し訳ありません。

またDBはoracle11gのためCBOによりチューニングが入っているため実行計画が最適化されています。