質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.03%

  • SQL

    2010questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 499

yuba

score 5063

論理削除という設計の是非はここではおいておいて、交差テーブルにa_idb_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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2018/01/22 11:15

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

    キャンセル

  • yuba

    2018/01/22 22:54

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

    キャンセル

回答 4

checkベストアンサー

+2

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

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/23 20:43

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

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

    キャンセル

+1

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

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/23 20:49

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

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/23 20:44

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/23 20:45

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 91.03%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2010questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。