webdevqa.jp.net

INNER JOIN ONとWHERE句

簡単にするために、すべての関連フィールドがNOT NULLであるとします。

できるよ:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

それ以外の場合:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

これら2つはMySQLでも同じように機能しますか?

847
JCCyC

INNER JOINはあなたが使うべきANSI構文です。

特にたくさんのテーブルを結合するときは、一般的に読みやすくなっています。

必要に応じていつでもOUTER JOINに簡単に置き換えることができます。

WHERE構文はよりリレーショナルモデル指向です。

2つのテーブルJOINedの結果は、結合列が一致する行のみを選択するフィルタが適用されるテーブルのデカルト積です。

これはWHERE構文で見るのが簡単です。

あなたの例に関しては、MySQL(そして一般的にSQL)では、これら二つの問い合わせは同義語です。

MySQLにはSTRAIGHT_JOIN句もあります。

この句を使用して、JOINの順序を制御できます。どのテーブルが外側のループでスキャンされ、どのテーブルが内側のループでスキャンされるかです。

MySQLではWHERE構文を使用してこれを制御することはできません。

652
Quassnoi

他の人たちは、INNER JOINが人間の読みやすさを助けることを指摘し、それが最優先事項です。同意する。 why を説明してみましょう。結合構文の方が読みやすくなっています。

基本的なSELECTクエリはこれです:

SELECT stuff
FROM tables
WHERE conditions

SELECT句は、 what に戻ってきていることを示しています。 FROM句は where から取得していることを示し、WHERE句は where 取得しているものを示しています。

JOINは、テーブル、それらがどのように結合されているか(概念的には、実際には1つのテーブルにまとめられている)に関するステートメントです。テーブルを制御するすべてのquery要素 - 私たちが物を得ているところ - は意味的にFROM句に属します(そしてもちろん、それがJOIN要素が入るところです)。結合要素をWHERE句に入れると、 which where-from が結合されます。それがJOIN構文が好まれる理由です。

163
Carl Manaster

ON/WHEREでの条件文の適用

ここでは論理的な問い合わせ処理ステップについて説明しました。


参照:Microsoft®SQL Server™2005のT-SQLクエリの内部
発行元:マイクロソフトプレス
パブ日付:2006年3月7日
印刷するISBN-10:0-7356-2313-9
印刷ISBN-13:978-0-7356-2313-2
ページ数:640

内部Microsoft®SQL Server™2005 T-SQLクエリ

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

他のプログラミング言語と異なるSQLの最初の注目すべき点は、コードが処理される順序です。ほとんどのプログラミング言語では、コードは書かれている順序で処理されます。 SQLでは、最初に処理される句はFROM句ですが、最初に現れるSELECT句はほぼ最後に処理されます。

各ステップは、次のステップへの入力として使用される仮想テーブルを生成します。これらの仮想テーブルは、呼び出し側(クライアントアプリケーションまたは外部クエリ)には利用できません。最後のステップで生成されたテーブルだけが呼び出し元に返されます。特定の句がクエリで指定されていない場合、対応する手順は単純にスキップされます。

論理問合せ処理フェーズの簡単な説明

ステップの説明が今のところあまり意味をなさないようであれば、あまり心配しないでください。これらは参照として提供されています。シナリオ例の後に続くセクションでは、ステップをより詳細に説明します。

  1. FROM:FROM句の最初の2つのテーブル間でデカルト積(クロスジョイン)が実行され、その結果、仮想テーブルVT1が生成されます。

  2. ON:ONフィルターをVT1にかけます。 <join_condition>がTRUEである行だけがVT2に挿入されます。

  3. OUTER(join):(CROSS JOINまたはINNER JOINとは対照的に)OUTER JOINが指定されている場合、一致が見付からなかった保存テーブルからの行がVT2からの行に外部行として追加されます。 VT3 FROM句に2つ以上のテーブルがある場合、最後のジョインの結果とFROM句の次のテーブルの間で、すべてのテーブルが処理されるまで、手順1から3が繰り返し適用されます。

  4. WHERE:WHEREフィルタはVT3に適用されます。 <where_condition>がTRUEである行だけがVT4に挿入されます。

  5. GROUP BY:VT4の行は、GROUP BY句で指定された列リストに基づいてグループにまとめられます。 VT5が生成されます。

  6. CUBE | ROLLUP:スーパーグループ(グループのグループ)がVT5の行に追加され、VT6が生成されます。

  7. HAVING:HAVINGフィルタはVT6に適用されます。 <having_condition>がTRUEであるグループだけがVT7に挿入されます。

  8. SELECT:SELECTリストが処理され、VT8が生成されます。

  9. DISTINCT:重複した行がVT8から削除されました。 VT9が生成されます。

  10. ORDER BY:VT9からの行は、ORDER BY文節に指定された列リストに従ってソートされます。カーソルが生成されます(VC10)。

  11. TOP:指定された行数または割合がVC10の先頭から選択されます。テーブルVT11が生成され、呼び出し元に返されます。



(ON/WHEREで条件文を適用しても、ほとんど違いはありません。これは、結合した表の数と各結合表で使用可能な行数によって異なります)。

125
rafidheen

暗黙のjoin ANSI構文は古く、それほど明確ではないためお勧めできません。

さらに、リレーショナル代数はWHERE句とINNER JOINの述語の互換性を可能にするので、WHERE句を持つINNER JOINクエリでもオプティマイザによって述語を並べ替えることができます。

できるだけ読みやすい方法でクエリを書くことをお勧めします。

時々、これはINNER JOINを比較的「不完全」にしたり、単にフィルタリング基準のリストをもっと簡単に保守できるようにするためにいくつかの基準をWHEREに入れることを含みます。

たとえば、

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

書きます:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

しかし、それはもちろん違います。

60
Cade Roux

暗黙的な結合(これが最初のクエリとして知られているものです)は、クエリにテーブルを追加する必要があると、はるかに混乱しやすくなり、読みにくくなり、保守が難しくなります。 4つか5つの異なるテーブルで同じクエリと結合の種類を実行すると想像してみてください。それは悪夢です。

明示的結合(2番目の例)を使用すると、はるかに読みやすくなり、保守も簡単になります。

29
matt b

また、古い構文を使用するとエラーが発生しやすくなります。 ON句を指定せずに内部結合を使用すると、構文エラーが発生します。古い構文を使用し、where句で結合条件の1つを忘れると、クロス結合が発生します。開発者は、問題を解決するように見えるかもしれませんが、クエリをかなり遅くするかもしれません(まだ結合自体が壊れていることを認識していないので結合を修正するのではなく).

また、古い構文でクロスジョインがある場合のメンテナンスのために、クロスジョインが必要な場合(クロスジョインが必要な状況がある場合)、またはそれが修正されるべき事故だった場合、メンテナはどのようにしてわかりますか?

左結合を使用した場合に暗黙の構文が不適切である理由を確認するために、この質問に注目しましょう。 Sybase * = Ansi Standardに同じ内部テーブルに対して2つの異なる外部テーブルを追加

加えて(ここでは個人的な噂)、明示的結合を使用する標準は20年以上前のものです。つまり、暗黙的な結合構文は20年間古くなっています。あなたは20年前から古くなっている構文を使ってアプリケーションコードを書くでしょうか?なぜあなたはデータベースコードを書きたいのですか?

25
HLGEM

彼らは異なる人間が読める意味を持っています。

ただし、クエリオプティマイザによっては、それらがマシンにとって同じ意味を持つ場合があります。

読みやすいように常にコーディングする必要があります。

つまり、これが組み込みの関係である場合は、明示的結合を使用してください。関連性の低いデータで照合する場合は、where句を使用してください。

12
John Gietzen

SQL:2003標準ではいくつかの優先順位規則が変更されたため、JOINステートメントが "コンマ"結合よりも優先されます。これは実際には設定方法によってはクエリの結果を変更する可能性があります。 MySQL 5.0.12が標準に準拠するようになったとき、これは何人かの人々にいくつかの問題を引き起こします。

したがって、あなたの例では、あなたのクエリは同じように動作するでしょう。しかし、3番目のテーブルを追加したとします。SELECT ... FROM table1、table2 JOIN table3 ON ... WHERE ...

MySQL 5.0.12より前では、table1とtable2が最初に結合され、次にtable3が結合されていました。これで(5.0.12以降)、table2とtable3が最初に結合され、次にtable1が結合されます。それは必ずしも結果を変えるわけではありませんが、それは可能であり、あなたはそれを実感さえしないかもしれません。

私はもう "コンマ"構文を使わない、あなたの2番目の例を選ぶ。とにかく読みやすくなっています、JOIN条件はJOINと一緒になっていて、別々のクエリセクションに分かれていません。

11
Brent Baisley

私はあなたがMySQLについて話していることを知っていますが、とにかく:Oracle 9では明示的結合と暗黙的結合は異なる実行計画を生成するでしょう。 Oracle 10+で解決された私の知る限り、そのような違いはもうありません。

4
João Marcus

ANSI join構文は間違いなく移植性があります。

Microsoft SQL Serverのアップグレードを行っていますが、SQL Serverの外部結合の= *および* =構文は2005互換のSQL Server以降ではサポートされていません(互換モードなし)。

1
Benzo

動的なストアドプロシージャを頻繁にプログラミングする場合は、2番目の例(whereを使用)に夢中になります。さまざまな入力パラメーターと多数のモーフ混乱がある場合、それが唯一の方法です。それ以外の場合は、両方とも同じクエリプランを実行するため、従来のクエリには明らかな違いはありません。

0
Kviz Majster