MySQL :: MySQL 8.0 リファレンスマニュアル

MySQL 8.0 リファレンスマニュアル / ... / JOIN 句

13.2.10.2 JOIN 句

MySQL では、SELECT ステートメントおよび複数テーブルの DELETE ステートメントと UPDATE ステートメントの table_references 部分について、次の JOIN 構文がサポートされています:

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

テーブル参照は、結合式とも呼ばれます。

テーブル参照 (パーティションテーブルを参照する場合) には、カンマ区切りのパーティションまたはサブパーティション (あるいはその両方) のリストを含む PARTITION オプションを含めることができます。このオプションはテーブルの名前のあとで、かつエイリアス宣言 (存在する場合) の前に指定されます。このオプションの効果は、リストされたパーティションまたはサブパーティションからのみ行が選択されることです。リストに指定されていないパーティションまたはサブパーティションは無視されます。詳細および例については、セクション24.5「パーティション選択」を参照してください。

table_factor の構文は、標準 SQL と比較して MySQL で拡張されています。標準では、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 では、JOIN、CROSS JOIN、および INNER JOIN は構文上同等です (互いに置き換えることができます)。標準 SQL では、それらは同等ではありません。 INNER JOIN は ON 句とともに使用され、CROSS JOIN はそれ以外のときに使用されます。

一般に、内部結合操作のみを含む結合式内のかっこは無視できます。 MySQL では、ネストした結合もサポートされています。セクション8.2.1.8「ネストした結合の最適化」を参照してください。

インデックスヒントを指定すると、MySQL オプティマイザによるインデックスの使用方法に影響を与えることができます。詳細は、セクション8.9.4「インデックスヒント」を参照してください。オプティマイザヒントおよび optimizer_switch システム変数は、オプティマイザによるインデックスの使用に影響を与える他の方法です。セクション8.9.3「オプティマイザヒント」およびセクション8.9.2「切り替え可能な最適化」を参照してください。

次のリストでは、結合の書込み時に考慮する一般的な要因について説明します:

テーブル参照には、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;

table_subquery は、FROM 句では導出テーブルまたはサブクエリーとも呼ばれます。セクション13.2.11.8「導出テーブル」を参照してください。このようなサブクエリーには、サブクエリーの結果にテーブル名を指定するエイリアスを含める必要があります。また、オプションで、カッコ内にテーブルのカラム名のリストを含めることもできます。簡単な例を次に示します:
```
SELECT * FROM (SELECT 1, 2, 3) AS t1;
```
1 つの結合で参照できるテーブルの最大数は 61 です。これには、FROM 句の導出テーブルおよびビューを外部クエリーブロックにマージすることで処理される結合が含まれます (セクション8.2.2.4「マージまたは実体化を使用した導出テーブル、ビュー参照および共通テーブル式の最適化」を参照)。
結合条件が存在しない場合、INNER JOIN と , (カンマ) は意味的に同等です。どちらも、指定されたテーブル間のデカルト積を生成します (つまり、最初のテーブル内のすべての各行が 2 番目のテーブル内のすべての各行に結合されます)。

ただし、カンマ演算子の優先順位は INNER JOIN, CROSS JOIN, LEFT JOIN の優先順位よりも低くなります。結合条件が存在するときにカンマ結合をほかの結合型と混在させた場合は、「カラム 'col_name' は 'on clause' にはありません」という形式のエラーが発生する可能性があります。この問題への対処に関する情報は、このセクションのあとの方で提供します。
ON で使用される search_condition は、WHERE 句で使用できるフォームの条件式です。通常、ON 句はテーブルの結合方法を指定する条件に使用され、WHERE 句は結果セットに含める行を制限します。
LEFT JOIN 内の ON または USING 部分にある右側のテーブルに一致する行が存在しない場合は、すべてのカラムが NULL に設定された行が右側のテーブルに使用されます。このことを使用して、別のテーブルに対応する行が存在しないテーブル内の行を検索できます。
```
SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;
```
この例では、right_tbl に存在しない id 値を持つ left_tbl 内のすべての行 (つまり、right_tbl 内に対応する行のない left_tbl 内のすべての行) を検索します。セクション8.2.1.9「外部結合の最適化」を参照してください。
USING(join_column_list) 句は、両方のテーブルに存在する必要があるカラムのリストを指定します。テーブル a と b の両方にカラム c1、c2、および c3 が含まれている場合、次の結合は、この 2 つのテーブルの対応するカラムを比較します。
```
a LEFT JOIN b USING (c1, c2, c3)
```
2 つのテーブルの NATURAL [LEFT] JOIN は、両方のテーブル内に存在するすべてのカラムを指定する USING 句を含む INNER JOIN または LEFT JOIN と意味的に同等であるとして定義されます。
RIGHT JOIN は、LEFT JOIN と同じように機能します。コードをデータベース間で移植可能な状態に維持するため、RIGHT JOIN の代わりに LEFT JOIN を使用することをお勧めします。
結合構文の説明に示されている { OJ ... } 構文は、ODBC との互換性のためにのみ存在します。構文内のカールした中括弧は文字どおりに書き込まれる必要があります。それらは構文説明の別の部分で利用されているようなメタ構文ではありません。
```
SELECT left_tbl.*
    FROM { OJ left_tbl LEFT OUTER JOIN right_tbl
           ON left_tbl.id = right_tbl.id }
    WHERE right_tbl.id IS NULL;
```
{ OJ ... } 内では、INNER JOIN や RIGHT 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;

外部結合バリアントを含む USING との自然結合および結合は、SQL:2003 標準に従って処理されます:

NATURAL 結合の冗長カラムは表示されません。次の一連のステートメントを考えてみます。
```
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);
```
最初の SELECT ステートメントでは、カラム j は両方のテーブルに現れるため、結合カラムになります。そのため、標準 SQL に従って、出力には 2 回ではなく 1 回だけ表示されるべきです。同様に、2 番目の SELECT ステートメントでは、カラム j は USING 句で指定されているため、出力には 2 回ではなく 1 回だけ表示されるべきです。

したがって、このステートメントは次の出力を生成します:
```
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
```
冗長なカラムの削除およびカラムの順序付けは、標準 SQL に従って行われ、次の表示順序が生成されます:
- 最初に、結合された 2 つのテーブルの合体した共通カラムが、最初のテーブルに現れた順序で
- 2 番目に、最初のテーブルに一意のカラムが、そのテーブルに現れた順序で
- 3 番目に、2 番目のテーブルに一意のカラムが、そのテーブルに現れた順序で
2 つの共通カラムを置き換える 1 つの結果カラムは、合体操作を使用して定義されます。つまり、t1.a と t2.a の 2 つに対して、結果として得られる 1 つの結合カラム a は a = COALESCE(t1.a, t2.a) として定義されます。ここでは:
```
COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)
```
結合操作が他の結合の場合、結合の結果カラムは結合されたテーブルのすべてのカラムの連結で構成されます。

合体したカラムの定義の結果として、外部結合では、2 つのカラムのいずれかが常に NULL である場合、合体したカラムには NULL 以外のカラムの値が含まれます。どちらのカラムも NULL でないか、または両方のカラムがこの値である場合、両方の共通カラムに同じ値が含まれているため、合体したカラムの値としてどちらが選択されるかは問題にはなりません。これを解釈するための簡単な方法として、外部結合の合体したカラムが JOIN の内部テーブルの共通カラムによって表されると考えてみます。テーブル t1(a, b) と t2(a, c) に次の内容が含まれているとします。
```
t1    t2
----  ----
1 x   2 z
2 y   3 w
```
この結合では、カラム a に t1.a の値が含まれます:
```
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+
```
対照的に、この結合では、カラム a に t2.a の値が含まれます。
```
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+
```
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 句としてリライトできます。ただし、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
```
結合条件を満たす行の判別に関して、両方の結合は意味的に同一です。

SELECT * の展開に対してどのカラムを表示するかの判定に関しては、この 2 つの結合は意味的に同一ではありません。 USING 結合が対応するカラムの合体した値を選択するのに対して、ON 結合は、すべてのテーブルのすべてのカラムを選択します。 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 つのカラムのどちらかが NULL になる場合があります。そのカラムは結果から省略されます。
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;
```
i3 は ON 句のオペランドではない t3 のカラムであるため、このステートメントは Unknown column 'i3' in 'on clause'エラーで失敗します。結合を処理できるようにするには、次のようにステートメントをリライトします:
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
```
JOIN の優先順位はカンマ演算子 (,) より高いため、結合式 t1, t2 JOIN t3 は ((t1, t2) JOIN t3) としてではなく (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);
```
JOIN はカンマ演算子よりも優先されるため、ON 句のオペランドは t2 および t3 です。 t1.i1 はどのオペランドのカラムでもないため、その結果は「カラム 't1.i1' は '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 を混在させるステートメントにも適用されます。これらはすべてカンマ演算子よりも優先順位が高くなります。
SQL:2003 標準と比較した MySQL 拡張機能では、MySQL では NATURAL または USING 結合の共通 (結合) カラムを修飾できますが、標準では修飾できません。

関連キーワード: 　ステートメント, 結合, カラム, テーブル, CREATE, TABLE, FROM, LEFT, DROP, USING

PREV HOME UP NEXT