Skip navigation links
8.2.8.2 インデックスヒントの構文 »
Section Navigation      [Toggle]

8.2.8.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] [index_hint_list]
  | table_subquery [AS] alias
  | ( 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 conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

index_hint_list:
    index_hint [, index_hint] ...

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

index_list:
    index_name [, index_name] ...

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

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 が利用されます。

一般的に、inner join オペレーションを含む join 表現のかっこは無視できます。MySQL はネスト化した結合をサポートします。項4.2.11. 「入れ子結合最適化」 を参照してください)。

インデックスヒントを指定すると、MySQL オプティマイザによるインデックスの使用方法に影響を与えることができます。詳細は 項8.2.8.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 節内のサブクエリーとも呼ばれます。サブクエリーの結果にテーブル名を与えるには、このようなサブクエリーにエイリアスを含める必要があります。簡単な例を次に示します。項8.2.9.8. 「FROM 節内のサブクエリー」 も参照してください。 

    SELECT * FROM (SELECT 1, 2, 3) AS t1;

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

    ただし、コンマ演算子の優先順位は、INNER JOIN、CROSS JOIN、LEFT JOIN などの優先順位より低くなります。もし結合条件がある場合にカンマ結合と別の型の結合を混合すると、Unknown column 'col_name' in 'on clause' という形のエラーが発生するかもしれません。この問題の対処法は、この節の後半で紹介します。

  • ON とともに使用される conditional_expr は、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 内のすべての行) を検索します。これは、right_tbl.id が NOT NULL として宣言されていることを前提にしています。項4.2.9. 「LEFT JOIN と RIGHT JOIN 最適化」 を参照してください。

  • USING(column_list) 節は、両方のテーブルに存在しなければいけないカラムのリストに名前をつけます。もしテーブル a と b の両方がカラム c1、c2、そして c3 を含むと、次の結合は 2 つのテーブルの対応するカラムを比較します。 

    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 との互換性のためにのみ存在します。構文内のカールした中括弧は文字通り書き込まれる必要があります。それらは構文説明の別の部分で利用されているようなメタシンタックスではありません。 

    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;

    MySQL 5.1.24 では、{ 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;

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): Column 'b' in where clause is ambiguous が生成されました。現在は、このクエリーによって正しい結果が生成されます。 

    +------+------+------+
| b    | c    | y    |
+------+------+------+
|    4 |    2 |    3 |
+------+------+------+

    SQL:2003 スタンダードと比較した MySQL の拡張機能の 1 つは、スタンダードは NATURAL や USING 結合 (以前のような) の共通 (合体した) カラムを修飾することを許可しなかったのに対して、MySQL はそれを許可するということです。