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

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

ただいまの
回答率

89.52%

SQLの内部結合について

解決済

回答 5

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,682

jakelizzI

score 27

 ISOかANSIか?

現在、業務で作成しているSQLが下記のように作成しているのですが、Inner Join等を書いてある場合と、書いていない場合があり、どちらが良いのか迷ったので質問させて頂きました。
恥ずかしながら、ISOかANSIという名称も先程知りました。

また、あまり詳しくないのですが、SQLはoracleから入ったので、自分はパターンAで書いてしまいます。

ポイントとしては、結合部と条件部の切り分け、可読性、可変性、メンテナンス性、作りやすさといったところでしょうか?そこに着眼してみたときに、どちらのパターンが優位なのでしょうか?

  • Aパターン
select
  テーブルA.カラム1
  ,テーブルB.カラム2
from
  テーブルA
  ,テーブルB
where
  テーブルA.カラム3 = テーブルB.カラム3
  • Bパターン
select
  テーブルA.カラム1
  ,テーブルB.カラム2
from
  テーブルA
  inner join テーブルB on テーブルA.カラム3 = テーブルB.カラム3

 DB情報

私が普段使用するDBは下記の2つなので、その中ではどのようなものなのか、また、SQL ServerやMySqlでのパターンも知識として知れたらなと思っていますので、ご意見等があればお教えいただきたいです。

  • Oracle
  • postgres

 追記

解答から発生した疑問について追記させて頂きます。

やはり「結合してから絞り込む記述」より「絞り込んでから結合の記述」の方が速度的にも早いのでしょうか?
深く考えたことは無かったのですが、どちらの記述を行ってもSQL解析の時点で最適な方法に変換されると思っているのですが、ど
うなのでしょう?

With句を用いたものや、InnerJoin句内のWhere句で絞り込んだ結果を結合する場合と、そこではWhere句は書かずに結合し、SQL全体として条件を指定して絞り込む場合とで、速度等に影響が及ぶのでしょうか?
漠然とですが、SQLの実行時に最適化されて、結果同じ速度になるかと思っていますが、この認識はあっていますか?

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 5

+3

FROMでテーブルを列記しても、INNERJOINしてもほぼ同じと考えてよいでしょう。
JOINの強みはJOIN後のデータに対してWHERE句をつかって絞込ができることです。
LEFT JOINした場合など、二重で条件設定させる場合がよくあります。

あとは効率というよりは認識の問題ですね
JOINの良さのひとつは主従関係がイメージしやすい
もう一つは複数連結する際の連結条件が見やすい
などがあげられると思います

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/20 14:08

    ご解答ありがとうございます!

    現状、この部分に関しては統一していないため、人によってはInnerJoinを記述してあったりなかったりします。どちらが良い・優れているというものが無ければ。。。。どっちがいいのでしょうかね?

    インデントをソフトタブかタブ文字かといった宗教戦争レベルの話なんでしょうか?

    キャンセル

+2

最近のDBだとSQL解析が優秀でどちらで記述しても
あまり結果に差がなく、見た目と好みの問題になりつつあると思います

もちろん可読性・メンテナンス性も大事だと思いますが
取得元も同じ、取得結果も同じで記述方法のみ違うSQLが
ミリ秒単位で結果を返す場合と数分を要して結果を返す場合とを
何度も目にしてきています

もちろんコーディング規約等で可読性・メンテナンス性を考慮した
記述を強制した上で、速く結果を返すSQLが一番良いのでしょうが
何よりも速く結果が返却されるSQLが私としては正解なのかなと思います

何も絞り込み条件が無くテーブルを結合して利用すること少ないので
WITH句を利用してあらかじめ結合するテーブルを絞り込んでおき
その結果をINNER JOINで記述するのが個人的に最近は好みです

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/21 12:30

    ご解答ありがとうございます。

    なるほど、やはり見た目や好みの問題になってくるんですね。
    With句の使用に関しては確かに用いたほうが見やすくなると思います。

    やはり「結合してから絞り込む記述」より「絞り込んでから結合の記述」の方が速度的にも早いのでしょうか?
    深く考えたことは無かったのですが、どちらの記述を行ってもSQL解析の時点で最適な方法に変換されると思っているのですが、どうなのでしょう?
    この件に関しては気になる部分でもあったので、質問に追記させて頂きます!

    キャンセル

+2

追記に対する解答です

「結合してから絞り込む記述」より「絞り込んでから結合の記述」が早いかは一概に言えず
実行計画がない状態で全てオプティマイザまかせ(ヒント句などを利用しない場合)にした場合
駆動表がどのテーブルになるか、その時の索引の状況やレコード数、また
挿入のみ行われるようなテーブルなのか、更新や削除も行われるテーブルなのか
検索条件、ソート条件によってSQL実行時間が異なりますし
必ずしも最も高速で動作するようにSQLを解析してくれるとは限りません

またトランザクション系のテーブルに100件程度しかレコードがない状態で
10件抽出するSQLを実行、その時点で実行計画が作成され
そのままレコードが100万件超まで増えたとし、同じ検索条件で10件抽出するSQLが
実行されたとしても
レコード数が100件の時に作成された実行計画でSQLが実行されてしまうと
SQLの実行時間が遅くなってしまう可能性があります

あくまで私が初めにSQLを記述する方法として
「WITH句を利用してあらかじめ結合するテーブルを絞り込んでおき
その結果をINNER JOINで記述」の場合が多いだけです
※場合によって「結合してから絞り込む記述」SQLを記述する場合もあります

本当に速いSQLを求めるのであれば
想定されるレコード数や索引の状態、検索条件、ソート条件を考慮してSQLを記述し
想定されるレコード数をテーブルに格納した状態で性能試験を行って実行計画を取得
速度が遅い場合は実行計画からSQLが遅くしている原因の箇所を探し
SQLをチューニングするになると思います

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/21 18:35

    ご解答ありがとうございます。

    >挿入のみ行われるようなテーブルなのか、更新や削除も行われるテーブルなのか
    更新・削除が行われるテーブルかによってSQLの実行時間が異なるのは始めて知りました!

    また、恥ずかしながら実行計画が作成されるタイミングや、その後どうなるかなどあまり考えたことがありませんでした。
    状況に応じてSQLをチューニングしていく必要があるということをしみじみと実感いたしました。

    実行計画に関してもまだまだ薄知であり、勉強する必要を実感したしました。
    ありがとうございます!

    キャンセル

  • 2017/07/21 18:55

    Oracleであれば
    ALTER INDEX インデックス名 REBUILD
    が何を行っているか?、何のために必要か?
    統計情報とは?辺りを詳しく調べてみるといかがでしょう

    キャンセル

  • この投稿は削除されました

  • 2017/07/24 18:20

    お返事が遅くなってしまい、申し訳ございません。

    その辺の知識が全く無く、お恥ずかしい限りです。
    勉強するための取っ掛かりをお教えいただいたので、その部分からいろいろとDBについて勉強させて頂きます!
    ありがとうございました!

    キャンセル

checkベストアンサー

+1

動けばパターンAでもパターンBでもどちらでも良いと思います。

私もOracle出身ですが、パターンBの方が好みです。
(理由は、yambejp様の記載内容と同じです。)

また、主従関係をよりイメージさせる場合、
INNER JOINではなくLEFT JOINにするくらいですかね。

パターンAを使うケース

ケース1)テーブルAとテーブルBの紐づけがない場合
(例)大容量のテストデータ作成時等、純粋に掛け合わせの件数をヒットさせたい場合

ケース2)テーブルAまたはテーブルBを抽出する結果が1件(または数件)だけで、JOINするよりも性能が良い場合
(例)抽出結果が結合するテーブルを抽出する条件になる場合等

ケース3)テーブルAとテーブルBが多対多の場合
※多対多のテーブルの結合は、
中間テーブルを作成して、
それぞれに対して、1対多にして、JOINした方が性能面は良いはずです。

追記に対する回答

やはり「結合してから絞り込む記述」より「絞り込んでから結合の記述」の方が速度的にも早いのでしょうか?

⇒テーブル設計や実際のデータによって異なります。

どちらの記述を行ってもSQL解析の時点で最適な方法に変換されると思っている

⇒SQL文、データベースやバージョンによって、
解析の仕方、最適な方法の算出方法は変わってきます。
データベースのSQL解析が間違えていたり、最適な方法が違う場合もあります。
SQLチューニングは奥が深く経験が必要なので、
追及すると本1冊じゃ足りません。
(大容量のデータを取り扱う現場でなければ、そこまで気にする必要はないかもしれません)

With句を用いたものや、InnerJoin句内のWhere句で絞り込んだ結果を結合する場合と、そこではWhere句は書かずに結合し、SQL全体として条件を指定して絞り込む場合とで、速度等に影響が及ぶのでしょうか?

⇒イメージが共有しきれてないかもしれませんが、
「Where句は書かずに結合」というのを、全件結合(掛け合わせ)してから、絞り込むという意味であれば、テーブルのINDEXが効かないため、遅くなります。

SQLの実行時に最適化されて、結果同じ速度になるかと思っていますが、この認識はあっていますか?

⇒検索結果が同じでも、結合方法・EXISTS句・副問合せ等でSQL文が違ければ、
速度も違う可能性は多々あります。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/21 18:49

    ご解答ありがとうございます。

    「動く」という観点からすれば、パターンA・パターンBに関してはやはり好みの問題となりそうですね。
    また、主従関係を明確にするのであればLEFT JOINを使用するという考え方は私には無かったので、少し驚きました!そのような考え方があるのですね!

    >⇒イメージが共有しきれてないかもしれませんが、
    >「Where句は書かずに結合」というのを、全件結合(掛け合わせ)してから、絞り込むという意味であれば、テーブルのINDEXが効かないため、遅くなります。

    「掛け合わせて絞り込む」という意味でしたので、あっているかと思います。Indexが効かなくなるため遅くなるというのは理解しました!ありがとうございます。

    > ⇒検索結果が同じでも、結合方法・EXISTS句・副問合せ等でSQL文が違ければ、
    > 速度も違う可能性は多々あります。

    SQLのチューニングに関しては、まだまだ勉強不足であると実感いたしました。ありがとうございます。

    ただ、
    > パターンAを使うケース
    に関して、何故この3つのケースはパターンAを使うのかな?と素人ながら思ってしまいました。単純にこのケースの場合はパターンAで記述するのが好みということでしょうか?
    それとも何かしらメリットがあるからということでしょうか?

    キャンセル

  • 2017/07/21 21:41

    > 主従関係を明確にするのであればLEFT JOINを使用する
    ⇒LEFT JOINの時には、結合条件のみを記載して、
     INNER のような条件文は WHERE句に書いたりしますね。

    FROM句のテーブルに対して、何件のデータを抽出するか、パッと見で見やすくするためです。
    (INNER JOINの場合でもなれれば分かるので、完全に好みの問題です。)

    > 何故この3つのケースはパターンAを使うのかな?
    ⇒期待を裏切るようで申し訳ないですが、ご認識の通り「好み」です(笑)。
     分かりやすく言うと、記載した3つのパターンには、
     『主従関係』が存在しないケースになります。

    キャンセル

  • 2017/07/24 18:17

    お返事が遅くなってしまい、申し訳ありません。

    >FROM句のテーブルに対して、何件のデータを抽出するか、パッと見で見やすくするためです。
    (INNER JOINの場合でもなれれば分かるので、完全に好みの問題です。)
    パッと見で分かりやすいのと、もし分離するときにメリットになりそうですね!

    >『主従関係』が存在しないケースになります。
    納得いたしました!
    確かに、結合の時は必ずしも主従関係があるとは限りませんからそのように書き分けるのも、作成者の意図が判りやすいかも知れませんね!ありがとうございます!

    キャンセル

  • 2017/07/24 18:23

    他のかたがたも分かりやすいご返答をして頂きましたが、パターンA等を利用するケースなど、例を提示していただき、無知な私にも分かりやすく教えて頂いたのでベストアンサーに選ばせて頂きます。m(_ _)m

    キャンセル

0

Oracleでjoinが使えるようになったのはOracle9iからでしたがバグがひどくて、parallelヒントと外部結合を組み合わせると結果から対象になるはずのデータが足りなくなって、サポートにクレームをつけてパッチの提供を約束してもらったこともあります。joinはOracle9.2の最終パッチでまともに使えるようになったかどうか?

Oracle SQL言語リファレンス 結合

Oracleの結合演算子よりも、FROM句のOUTER JOIN構文を使用することをお薦めします。

ということで (+) は過去の遺産を継承できるように残っているので、なるべくouter joinを使った方が良い、ということらしいです。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/07/24 18:13

    ご解答ありがとうございます!
    お返事が遅くなってしまい、申し訳ございません。

    Oracle9.2まではまともに使えなかったのですか!?今では想像できませんが、大変だったのですね...

    >ということで (+) は過去の遺産を継承できるように残っているので、なるべくouter joinを使った方が良い、ということらしいです。
    確かに、最近の記事か何かで(+)は非推奨と書いてあったので、使わないようにしています。(過去の遺産で残っているものはありますがw)

    キャンセル

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

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