12.2.7.1 JOIN 構文

12.2.7.1. `JOIN` 構文

MySQL は、SELECT ステートメントの table_references 部分と、複合テーブル DELETE と UPDATE ステートメント

に対して、次の JOIN 構文をサポートします。

table_references:
    table_reference [, table_reference] ...

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

テーブル参照は、接合式としても知られています。

table_factor の構文は、SQL スタンダードと比較して拡張されます。後者は table_reference だけを許容し、カッコ内のそれらのリストは許容しません。

もし table_reference のリスト内の各カンマが内側の接合と同等であると考えると、これは保守的な拡張子という事になります。例:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

は次の物と同等です:

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

MySQL では、CROSS JOIN は INNER JOIN と構文的に同等です。(お互いに置き換える事ができます。)スタンダード SQL では、それらは同等ではありません。INNER JOIN は ON 条項と共に利用され、そうでなければ CROSS JOIN が利用されます。

通常、内側結合演算だけを含む結合式内のカッコは無視する事ができます。MySQL はネスト化した接合をサポートします。(項6.2.10. 「入れ子結合最適化」を参照してください。)

次のリストには、接合を書く時に考慮に入れる通常の要因が説明されています。

テーブル参照では tbl_name AS alias_name か tbl_name alias_name を利用してエイリアスを指定する事ができます。

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

INNER JOIN と , (カンマ)は結合条件がない場合には意味的に同等となります。両方とも、指示されたテーブルの間にデカルト結果を作り出します。(これは、最初のテーブル内の行1つ1つが、2番目のテーブルの行1つ1つに接合されるという事です。)

しかし、カンマ演算子の先行は、INNER JOIN、CROSS JOIN、LEFT JOIN 等のそれよりも少ないです。もし接合条件がある場合にカンマ接合と別のタイプの接合を混合すると、Unknown column 'col_name' in 'on clause' という形のエラーが発生するかもしれません。この問題の対処法は、このセクションの後半で紹介します。
ON 条件文は WHERE 条項の中で利用する事ができる形の条件文です。通常、テーブルをどのように接合するのかを指定する条件には ON 条項を、結果セットの中にどの行が必要であるかを制限するには WHERE 条項を利用する必要があります。
もし LEFT JOIN 内の ON か USING 部分内に右側のテーブルに一致する行がなければ、全てのカラムが NULL に設定されている行が右側のテーブルに利用されます。この事実は、別のテーブル内に対応する物を持たないテーブル内の行を見つける為に利用する事ができます。
```
SELECT table1.* FROM table1
  LEFT JOIN table2 ON table1.id=table2.id
  WHERE table2.id IS NULL;
```
この例は、table2 の中に存在しない id 値を持つ table1 内全ての行を見つけます。(table2 内に対応する行を持たない table1 内全ての行)これは、table2.id が NOT NULL を宣言したと仮定します。詳しくは項6.2.9. 「LEFT JOINとRIGHT JOIN最適化」を参照してください。
USING(column_list) 条項は、両方のテーブルに存在しなければいけないカラムのリストに名前をつけます。もしテーブル a と b の両方がカラム c1、c2、そして c3 を含むと、次の接合は二つのテーブルの対応するカラムを比較します。
```
a LEFT JOIN b USING (c1,c2,c3)
```
2つのテーブルの NATURAL [LEFT] JOIN は INNER JOIN か、両方のテーブルに存在する全てのカラムに名前を付ける USING 条項を持つ LEFT JOIN と意味的に同等になるよう定義されます。
RIGHT JOIN は LEFT JOIN と同じように機能します。コードがデータベース全体に移植できる状態を保つ為に、RIGHT JOIN の代わりに LEFT JOIN を利用する事をお勧めします。
接合構文の説明で表されている { OJ ... LEFT OUTER JOIN ...} 構文は ODBC を利用した互換性に対してだけ存在します。構文内のカールした中括弧は文字通り書き込まれる必要があります。それらは構文説明の別の部分で利用されているようなメタシンタックスではありません。
STRAIGHT_JOIN は、左側のテーブルがいつも右側のテーブルの前に読み込まれるという事以外は JOIN と全く同じです。これは、接合オプチマイザがテーブルを間違った順番で置いてしまうという（数少ない）場合に利用する事ができます。

接合の例:

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
  LEFT JOIN table3 ON table2.id=table3.id;

テーブルから情報を検索する時、MySQL がどのインデックスを利用するべきか、ヒントを与える事ができます。 USE INDEX (key_list) を指定する事で、MySQL がテーブル内の行を見つける為に、有効なインデックスを1つだけ利用するように指示する事ができます。既存構文 IGNORE INDEX (key_list) は、MySQL がいくつかの特定のインデックスを利用しないように指示する事ができます。これらのヒントは、MySQL が可能なインデックスのリストの中から、間違ったインデックスを利用している事を、EXPLAIN が表示した時に便利な物です。

テーブルスキャンは とても 高いと仮定されますが、USE INDEX (key_list) のように機能する FORCE INDEX を利用する事もできます。言い換えると、テーブル内の行を見つける為に与えられたインデックスを利用できない場合、テーブルスキャンを利用する事ができるという事です。

USE INDEX、IGNORE INDEX、そして FORCE INDEX は、MySQL がどのようにテーブルの中の行を見つけ、接合を行うのかを決定する時に、どのインデックスが利用されるのかという事にだけ影響を与えます。それらは、ORDER BY か GROUP BY を解決する時にインデックスを利用するかどうかという事に影響を与えます。

USE KEY、IGNORE KEY、そして FORCE KEY は USE INDEX、IGNORE INDEX、そして FORCE INDEX の同義語です。

例：

SELECT * FROM table1 USE INDEX (key1,key2)
  WHERE key1=1 AND key2=2 AND key3=3;

SELECT * FROM table1 IGNORE INDEX (key3)
  WHERE key1=1 AND key2=2 AND key3=3;

接合処理は MySQL 5.0.12 で変更されました。

注意:自然接合と、外部接合異形を含む USING を利用した接合は、SQL:2003 スタンダードに従って処理されます。その目的は、SQL:2003 に従い NATURAL JOIN と JOIN ... USING について、MySQL の構文と動作を提携させる事でした。しかし、接合処理に関してのこれらの変更は、いくつかの接合に関して異なるアウトプットカラムをもたらす可能性があります。また、古いバージョン (5.0.12 以前の物) で正しく機能していたいくつかのクエリも、スタンダードに適合する為に書き直される必要があります。

これらの変更には、主に5つの特徴があります。

MySQL が NATURAL か USING 接合操作の結果カラムを決定する方法。(従って FROM 条項の結果という事)
選択されたカラムのリストの中への SELECT * と SELECT tbl_name.* の拡大。
NATURAL か USING 接合内でのカラム名の決定。
NATURAL か USING 接合の JOIN ... ON への変形.
JOIN ... ON の ON 条件内のカラム名の決定。

次のリストに、現在のバージョンと古いバージョンの接合処理の効果について比べた詳細が紹介されています。「以前は」という言葉は「MySQL 5.0.12 以前」という意味です。

NATURAL 接合や USING 接合のカラムは以前と異なるかもしれません。特に、余分なアウトプットカラムはもう現れません、そして、SELECT * 拡大のカラムの順番は以前とは異なるかもしれません。

このステートメントのセットを検討してください。
```
CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);
```
以前は、このステートメントはこのアウトプットを産出しました。
```
+------+------+------+------+
| i    | j    | k    | j    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
+------+------+------+------+
+------+------+------+------+
| i    | j    | k    | j    |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
+------+------+------+------+
```
最初の SELECT ステートメントの中で、カラム j は両方のテーブル内に現れた為に接合カラムになります。という事は、スタンダード SQLによると、それはアウトプット内に2回ではなく1回のみ現れる必要があるという事になります。同じように、2番目の SELECT ステートメントの中で、カラム j は USING 条項の中で名前が付けられ、2回ではなく1回だけアウトプットの中に現れる必要があります。しかし、この両方で余分なカラムは排除されていません。また、スタンダード SQL によると、カラムの順番は正しくありません。

そして、ステートメントはこのアウトプットを産出します。
```
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
```
余分なカラムは排除され、スタンダード SQL によると、このカラムの順番は正しいです。
- 最初に、1つ目のテーブルの順番で、2つの接合したテーブルに共通するカラムを合体させました。
- 次に、テーブルの順番で、最初のテーブル固有のカラムを合体させました。
- 最後に、テーブルの順番で、2番目のテーブル固有のカラムを合体させました。
2つの共通カラムを置き換えられる単一結果カラムは、合体操作を通して定義されました。これは、次のステートメントで、t1.a と t2.a の2つに対して、導き出された1つの接合カラム a は a = COALESCE(t1.a, t2.a) として定義される、という事です。
```
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
```
もし接合操作がそれ以外の接合であれば、その接合の結果カラムは、接合されたテーブルの全てのカラムの連続で構成されます。これは以前と同じです。

外部接合に関する合体したカラム定義の結論は、合体したカラムはもし2つのうち1つのカラムがいつも NULL であれば、非 NULL カラムの値を含む、という事です。もしどちらのカラムも NULL でない、または両方がそうである場合、両方の共通カラムは同じ値を持つので、どちらが合体したカラムの値として選択されるかというのは特に問題にはなりません。これを理解する簡単な方法は、外部接合の合体したカラムは JOIN の内側テーブルの共通カラムによって表される、と考える事です。テーブル t1(a,b) と t2(a,c) が次のコンテンツを持つと仮定してください。
```
t1    t2
----  ----
1 x   2 z
2 y   3 w
```
すると:
```
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+
```
ここでは、カラム a は t1.a の値を含んでいます。
```
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+
```
ここでは、カラム a は t2.a の値を含んでいます。

これらの結果を JOIN ... ON を利用した他の同等のクエリと比較してください。
```
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    1 | x    | NULL | NULL |
|    2 | y    |    2 | z    |
+------+------+------+------+
```
```
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    2 | y    |    2 | z    |
| NULL | NULL |    3 | w    |
+------+------+------+------+
```
以前、 USING 条項は、対応するカラムを比較する ON 条項として再度書き込む事ができました。例えば、次の2つの条項は意味的に全く同じでした。
```
a LEFT JOIN b USING (c1,c2,c3)
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
```
今はもうこの2つの条項は同じではありません。
- どの行が接合条件を満たすかの判断に関しては、両方の接合は意味的に全く同じままです。
- SELECT * 拡大に対してどのカラムを表示するかの判断に関しては、両方の接合は意味的に全く同じではありません。ON 接合が全てのテーブルから全てのカラムを選択するのに対して、USING 接合は対応するカラムの合体した値を選択します。先行する USING 接合に対しては、SELECT * はこれらの値を選択します。
```
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
```
  ON 接合に対しては、SELECT * が次の値を選択します。
```
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
```
  内部結合では、両方のカラムが同じ値を持つので COALESCE(a.c1,b.c1) は a.c1 か b.c1 と同じです。外部接合では(LEFT JOIN のような)、2つのうち1つのカラムが NULL になり得ます。そのカラムは結果から排除されます。
- 多方向自然接合の評価は、NATURAL か USING 接合の結果に影響を与え、クエリの再書き込みを必要とするような、大変重要な形で異なっています。それぞれが行を1つ持つ3つのテーブル t1(a,b)、t2(c,b)、そして t3(a,c) があると仮定してください。t1(1,2)、t2(10,2)、そして t3(7,10) です。また、その3つのテーブル上にこの NATURAL JOIN も持っていると仮定してください。
```
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
```
  以前は、2つめの接合の左のオペランドは、ネスト化した接合 (t1 NATURAL JOIN t2) とならなければいけない一方、t2 となると考えられていました。その結果、t3 のカラムは t2 の中だけで共通カラムに関して確認され、そしてもし t3 が t1 を持つ共通カラムを持っていれば、これらのカラムは等価接合カラムとして利用されません。従って、以前は先行クエリは次の等価接合に変形されていました。
```
SELECT ... FROM t1, t2, t3
  WHERE t1.b = t2.b AND t2.c = t3.c;
```
  その接合では、もう1つの等価接合述語 (t1.a = t3.a) がなくなっています。その結果、それはもう1つ行を作成するので、結果は空にはなりません。正しい同等のクエリはこれです。
```
SELECT ... FROM t1, t2, t3
  WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
```
  もし現在の MySQL のバージョンの中で、古いバージョンと同じクエリの結果が必要であれば、自然接合を最初の等価接合として書き換えてください。
- 以前は、カンマ演算子(,)と JOIN の両方は同じ優先順位だったので、接合式 t1, t2 JOIN t3 は ((t1, t2) JOIN t3) として解釈されました。現在は JOIN が高い優先順位を持つので、式は (t1, (t2 JOIN t3)) として解釈されます。この変更は、ON 条項が接合の演算子内のカラムだけを参照する事ができ、優先順位の変更はそれらの演算子が何であるかについての解釈を変えてしまうので、この条項を利用するステートメントに影響を与えます。
  
  例:
```
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
```
  以前は、(t1,t2) としての t1,t2 の暗黙のグループ分けのおかげで、SELECT は正当でした。現在は JOIN が優先順位を持つので ON 条項の演算子は t2 と t3 です。t1.i1 がどちらの演算子でもないので、結果は Unknown column 't1.i1' in 'on clause' エラーになります。接合を実行させるには、ON 条項の演算子が (t1,t2) と t3 となるように、括弧を利用して最初の2つのテーブルを明示的にグループ分けして下さい。
```
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
```
  または、カンマ演算を利用するのを避け、その代わりに JOIN を利用してください。
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
```
  この変更は、カンマ演算子とそれよりも高い優先順位を持つ INNER JOIN、CROSS JOIN、LEFT JOIN または RIGHT JOIN を混合するステートメントにも適応します。
- 以前は、ON 条項はその右側で名前が付けられたテーブル内のカラムを参照する事ができました。現在は ON 条項はその演算子だけ参照する事ができます。
  
  例:
```
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
```
  以前は、SELECT ステートメントは正当でした。現在は、i3 は、ON 条項の演算子ではない t3 内のカラムなので、ステートメントは Unknown column 'i3' in 'on clause' エラーで失敗します。ステートメントは次のように書き換えられなければいけません。
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
```
- NATURAL か USING 接合内でのカラム名の決定は、以前とは違います。FROM 条項の外にあるカラム名に対しては、MySQL は以前と比べると上位集合であるクエリを扱います。それは、以前は MySQL がいくつかのカラムが曖昧であるというエラーを発行したような場合でも、現在はクエリが正確に扱われるという事です。これは、現在は MySQL が NATURAL や USING 接合の共通カラムを単一カラムとして扱う為、クエリがそのようなカラムを参照した時、クエリコンパイラがそれらを曖昧だとは認識しないという事実によるものです。
  
  例:
```
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
```
  以前は、このクエリは ERROR 1052 (23000) を導いていました。場所条項内の、カラム 'b' が曖昧です。.現在はそのクエリは正しい結果を導きます。
```
+------+------+------+
| b    | c    | y    |
+------+------+------+
|    4 |    2 |    3 |
+------+------+------+
```
  SQL:2003 スタンダードと比較した MySQL の拡張機能の1つは、スタンダードは NATURAL や USING 接合(以前のような)の共通(合体した)カラムを修飾する事を許可しなかったのに対して、MySQL はそれを許可するという事です。

12.2.7.1. JOIN 構文

12.2.7.1. `JOIN` 構文