交差テーブルに削除日時を持たせる場合、is null はON句に書く？WHERE句に書く？

Question

論理削除という設計の是非はここではおいておいて、交差テーブルにa_id, b_idという複合主キーに加えて削除日時というカラムを持たせる場合があります。
（論理削除というよりは、設定日時というカラムも持つことでいつからいつまでの時期にこの関係が存在していたという事実の表明をしているのだと言えます）

さてこの日時カラムを考慮してJOINを行う際、日時条件の判定式はJOINのON句に書きますか？ それともWHERE句に書きますか？

意味的にはどちらでも同じですし、ほとんどのDBでは実行プランの差も出ないと思いますので、純粋に読みやすさやポリシーの問題であると思っています。

●WHERE句に書くべき根拠

• ON句に書くのはキーの関係である。日時条件はキーの関係ではない
• USINGだけで書ける条件をわざわざ日時条件のためにONに展開するのは馬鹿馬鹿しい

●ON句に書くべき根拠

• 日時条件はキーの関係の存在に関わることであり、ON句の責任範囲だ
• 結合ルールの存在が一行でわかることは読み手にスキーマ理解のヒントを与える良い書き方

自分で思いつくメリットデメリットは以上のような感じですが、ほかに考慮点があったら教えてください。

具体例の追記

usersテーブル

user_id	nickname	face_image
1	Alice	(BLOB)
2	Bob	(BLOB)
3	Charley	(BLOB)

followingテーブル

follower_user_id	followee_user_id	created_at	deleted_at
1	2	2015-01-01	9999-12-31
2	3	2015-01-01	2016-12-31

このとき、あるユーザーのフォロワー一覧を表示しようとしたら

A. フォロー関係の生存期間をONで判定

SELECT u.user_id, u.nickname, u.face_image
FROM   following f
  JOIN users u
    ON (f.follower_user_id = u.user_id AND current_time < f.deleted_at)
WHERE  f.followee_user_id = ?

B. フォロー関係の生存期間をWHEREで判定

SELECT u.user_id, u.nickname, u.face_image
FROM   following f
  JOIN users u
    ON (f.follower_user_id = u.user_id)
WHERE  f.followee_user_id = ? AND current_time < f.deleted_at

どちらの記法を選びますか、その根拠はどういったものが考えられますか、という質問になります。

Comments

[yambejp]

ちょっとイメージがわかないので、具体的なテーブルを例示してください。またどのSQLを想定しているかも書いたほうがよりネイティブな書き方が提示されると思います

[yuba]

yambejpさん 具体例を追記しました。イメージわきやすくなったでしょうか。

Answer 1

個人的な意見としてですが、「実行プランの差も出ない」前提ですが、
交差テーブルということであれば、その関係性における条件なので、「●ON句に書くべき根拠」派です。
※選択肢にはないですが、厳密には「コストが低い方に使い分ける派」ですけど。

細かい点で言うとその交差テーブルの削除日時に関しては、交差テーブルとして独立したもので、
サブクエリーとして条件をwhereに記述することもできます。

それをサブクエリーではなく記述すると漏れなくon句での記述となります。

また削除云々ではなく、結合相手の日付項目と設定日時と削除日時でbetweenにより結合する場合も考えられますし、等結合なら関係ありませんが、外部結合である場合、where条件に記述する際には、Null考慮が必要になる点です。

※本題とは逸れますが、こういった条件に関係する項目はNot Nullとする設計を行って、インデックスを効率的に使用する方が良いかと思います。

Comments

[yuba]

なるほど、サブクエリに書くのが本来の姿であり、それはやりすぎなのでフラットにするとONに出てくるということですね。
それは説得力があります。

not nullにした方が良いのはご指摘の通りです。質問文内に追記した実例のスキーマもnot nullの設計にしてしまいました。

Answer 2

一般論的に問われるならば、
テーブル結合前に、テーブル単体で解決する絞り込み条件なのであれば、
テーブル単体にWHERE句を設けて絞り込んで、
そのあと他のテーブルと結合するのが妥当ではないかと思います。

JOINやLEFT JOINで結合する対象のレコード数を事前になるべく省く努力をする、という主旨。

あと実際は、実行計画（MySQL/PostgreSQLならEXPLAIN）をにらみながら
タイピング数と性能のバランスをどこまで頑張るかでしょうか。
必要ならインデックスを設けるとしても、（一般論では）NULL判定そのものにはインデックスを張れないので、
別の単純フラグを設けてインデックスが機能する方向で構築するのもよいかと。

例示するなら、削除日時にNULLを許さず、
例えば200年位先の特定日付（2222/2/22）を既定値として、
その日付の場合には削除されていないと判断するとかか、
あるいは、日付の有り無しではなく削除フラグを別途設けたらインデックスも機能します。

処理系ごとバージョンごとの癖もある世界なので、特化して学ばれるか、
あるいは汎用的な話としてざっくり理解しておくかと。

Comments

[yuba]

JOINする前に絞り込めということでsaziさんと同じ見解ということになりますね。
サブクエリでなくともビューとかCTEとかありますし、記述を複雑にしすぎることもなさそうです。

結局実行プラン次第だというのはご指摘の通りだと思います。
チューニング前の、まずは一般的に書き下ろす場合の書き方として考えておりました。

Answer 3

この場合 削除日時は テーブルA, テーブルB, 交差テーブルを結合させるための条件ではないですよね？
結合した上での絞り込みに使う条件ならば、where 句に書きます。

Comments

[yuba]

結合させるための条件であるともそうでないとも解釈できます。そこでこの議論が生まれてきているとお考えいただけますか。

Answer 4

WHERE党です。
書かれている根拠に加えて、
・最近はそんなことないですけど、クエリオプティマイザがインデックスを利用しないことがある。
・最近はそんなことないですけど、ON句を複雑にするとクエリオプティマイザが迂遠なプランをたてることがあった。
・ON句はシンプルにと誰かが言っていた気がする。
書いてて気づきましたが、明確な根拠は有りませんでした。

Comments

[yuba]

「ON句はシンプルにと誰かが言っていた気がする」
なんか私もどこかで聞いたことがあるような気がするんですよ。