Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.9.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
  | join_table

table_factor:
    tbl_name [PARTITION (partition_names)] 
        [[AS] alias] [index_hint_list]
  | table_subquery [AS] alias
  | ( table_references )

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] ...

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

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

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

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

インデックスヒントを指定すると、MySQL オプティマイザによるインデックスの使用方法に影響を与えることができます。詳細は、セクション13.2.9.3「インデックスヒントの構文」を参照してください。

次のリストは、結合を記述するときに考慮に入れるべき一般的な要因について説明しています。

  • テーブル参照には、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.10.8「FROM 句内のサブクエリー」も参照してください。

    SELECT * FROM (SELECT 1, 2, 3) AS t1;
    
  • 結合条件が存在しない場合、INNER JOIN, (カンマ) は意味的に同等です。どちらも、指定されたテーブル間のデカルト積を生成します (つまり、最初のテーブル内のすべての各行が 2 番目のテーブル内のすべての各行に結合されます)。

    ただし、カンマ演算子の優先順位は、INNER JOINCROSS JOINLEFT JOIN などの優先順位より低くなります。結合条件が存在するときにカンマ結合をほかの結合型と混在させた場合は、「カラム 'col_name' は '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.idNOT NULL として宣言されていることを前提にしています。セクション8.2.1.9「LEFT JOIN および RIGHT JOIN の最適化」を参照してください。

  • USING(column_list) 句は、両方のテーブル内に存在する必要のあるカラムのリストを指定します。テーブル ab の両方にカラム c1c2、および 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 JOINRIGHT 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 標準に従って処理されます。その目標は、NATURAL JOINJOIN ... USING に関連した MySQL の構文とセマンティクスを SQL:2003 に合わせることでした。ただし、結合処理でのこれらの変更によって、一部の結合で異なる出力カラムが生成される可能性があります。また、古いバージョン (5.0.12 より前) では正しく機能するように見えた一部のクエリーを、この標準に準拠するように書き換える必要があります。

これらの変更の主要な側面として、次の 5 つがあります。

  • MySQL が NATURAL または USING 結合操作の結果カラム (したがって、FROM 句全体の結果) を決定する方法。

  • SELECT * および SELECT tbl_name.* の選択されたカラムのリストへの展開。

  • NATURAL または USING 結合でのカラム名の解決。

  • NATURAL または USING 結合の JOIN ... ON への変換。

  • JOIN ... ONON 条件でのカラム名の解決。

次のリストは、現在の結合処理のいくつかの効果を古いバージョンでの結合処理と比較した場合のさらに詳細な情報を示しています。以前という用語は、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 ステートメントでは、カラム jUSING 句で指定されているため、出力には 2 回ではなく 1 回だけ表示されるべきです。ただし、どちらの場合も、冗長なカラムは削除されていません。また、標準 SQL に従うとカラムの順序も正しくありません。

    現在は、このステートメントによって次の出力が生成されます。

    +------+------+------+
    | j    | i    | k    |
    +------+------+------+
    |    1 |    1 |    1 |
    +------+------+------+
    +------+------+------+
    | j    | i    | k    |
    +------+------+------+
    |    1 |    1 |    1 |
    +------+------+------+
    

    冗長なカラムは削除され、カラムの順序も標準 SQL に従って正しくなっています。

    • 最初に、結合された 2 つのテーブルの合体した共通カラムが、最初のテーブルに現れた順序で

    • 2 番目に、最初のテーブルに一意のカラムが、そのテーブルに現れた順序で

    • 3 番目に、2 番目のテーブルに一意のカラムが、そのテーブルに現れた順序で

    2 つの共通カラムを置き換える 1 つの結果カラムは、合体操作を使用して定義されます。つまり、t1.at2.a の 2 つに対して、結果として得られる 1 つの結合カラム aa = COALESCE(t1.a, t2.a) として定義されます。ここでは:

    COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
    

    この結合操作がほかのいずれかの結合である場合、その結合の結果カラムは、結合されたテーブルのすべてのカラムの連結で構成されます。これは以前と同じです。

    合体したカラムの定義の結果として、外部結合では、2 つのカラムのいずれかが常に 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    |
    +------+------+------+
    

    ここでは、カラム at1.a の値が含まれています。

    mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
    +------+------+------+
    | a    | c    | b    |
    +------+------+------+
    |    2 | z    | y    |
    |    3 | w    | NULL |
    +------+------+------+
    

    ここでは、カラム at2.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 * の展開に対してどのカラムを表示するかの判定に関しては、この 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
      

      内部結合では、a.c1b.c1 の両方のカラムに同じ値が含まれるため、COALESCE(a.c1,b.c1) はどちらのカラムとも同じです。外部結合 (LEFT JOIN など) では、2 つのカラムのどちらかが NULL になる場合があります。そのカラムは結果から省略されます。

  • 多方向自然結合の評価は、NATURAL または USING 結合の結果に影響を与え、さらにはクエリーの書き換えが必要になる場合もある非常に重要な点で異なります。3 つのテーブル t1(a,b)t2(c,b)、および t3(a,c) があり、各テーブルに t1(1,2)t2(10,2)、および t3(7,10) の 1 行が含まれているとします。また、これらの 3 つのテーブルに対して次の NATURAL JOIN を実行するとします。

    SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
    

    以前は、2 番目の結合の左のオペランドが t2 であると見なされたのに対して、現在はネストされた結合 (t1 NATURAL JOIN t2) であると見なされます。その結果、t3 のカラムは t2 でのみ共通カラムに対してチェックされ、さらに t3t1 との共通カラムが含まれている場合、これらのカラムは等価結合カラムとして使用されません。そのため、以前は、前のクエリーは次の等価結合に変換されました。

    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);
    

    以前は、この SELECT は、(t1,t2) として t1,t2 の暗黙的なグループ化のために正当でした。現在は、JOIN が優先されるため、ON 句のオペランドは t2t3 になります。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 JOINCROSS JOINLEFT 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 ステートメントは正当でした。現在は、i3ON 句のオペランドではない t3 内のカラムであるため、このステートメントは「カラム 'i3' は '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 つの拡張として、MySQL では、NATURAL または USING 結合の共通 (合体した) カラムを (以前と同様に) 修飾できるのに対して、標準ではそれが禁止される点があります。


User Comments
  Posted by Scott Atkins on January 23, 2003
Tip time:

(Background: This database is used to keep track of scores for students in my classes.)

So in this case, I have three tables, one has student's "codename" (as posting their real name on the web is a no-no) and an index (there is more data in this table, but this is all you really need to know.) Then there's a table with the assignments, containing the assignment name, and an index for each assignment. Finally, there is a scores table, which has for each paper I get turned in, a student_id (releated to the student index) an act_id (related to the assignments index) and a score.

It looked something like this:
students table:
+----+---------------+
| id | codename |
+----+---------------+
| 1 | Budy |
+----+---------------+

assignments table:
+--------+------------+
| act_id | name |
+--------+------------+
| 1 | Activity 1 |
| 2 | Activity 2 |
+--------+------------+

scores table:
+------------+--------+-------+
| student_id | act_id | score |
+------------+--------+-------+
| 1 | 1 | 10 |
| 1 | 2 | 10 |
+------------+--------+-------+

Now the problem was, I wanted to have the assignments listed across the top, and the scores next to the names. Something like this:
+---------------+------------+------------+-------+
| codename | Activity 1 | Activity 2 | Total |
+---------------+------------+------------+-------+
| budy | 10 | 10 | 20 |
+---------------+------------+------------+-------+

So here's how the sql statement ended up:
SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;

As you can see, for each activity, I need to add another left join, but it looks exactly like the last one, thus it is easy to build through a program like php. I hope this helps someone out.
  Posted by Thomas Mayer on April 21, 2003
I use left joins to generate sums on one table using different conditions:
t1 to make sure that ALL grouped records are shown
t(n+1) for use per condition
and as mentioned above, the JOIN condition must be used as well for the primary key AND for the condtion per sum!

Here is an example:

drop table if exists testtable;
create table testtable
(mykey int not null,
mygroup int,
cond int,
value int,
primary key (mykey));

insert into testtable
values (1, 1, 1, 5), (2, 1, 1, 6), (3, 1, 2, 3), (4, 2, 2, 4), (5, 3, 3, 5);

-- returns nothing
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey
left join testtable t3 on t1.mykey=t3.mykey
left join testtable t4 on t1.mykey=t4.mykey
where t2.cond=1
and t3.cond=2
and t4.cond=3
group by 1
order by 1;

-- returns correct sums
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey and t2.cond=1
left join testtable t3 on t1.mykey=t3.mykey and t3.cond=2
left join testtable t4 on t1.mykey=t4.mykey and t4.cond=3
group by 1
order by 1;

mygroup | cond_1 | cond_2 | cond_3
1 | 11 | 3 | 0
2 | 0 | 4 | 0
3 | 0 | 0 | 5

  Posted by joerg schaber on June 11, 2003
I also think that the missing feature of FULL OUTER JOIN is a real drawback to MySQL. However, from MySQL 4 on you can use a workaround using the UNION construct. E.g. at
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html
  Posted by Y G on October 27, 2003
Below is an example of how to left-join multiple tables independently.

SELECT ...
FROM table 1
LEFT JOIN table 2 on (table1.id = table2.id)
LEFT JOIN table 3 on (table1.id2 = table3.id2)
LEFT JOIN table 4 on (table1.id3 = table4.id3)

  Posted by name withheld on November 13, 2003
Martin,
your comment & others helped emensely!
Here's a left-join select that also has a regular join to another table.
I want to get all Plans (& associated SubType info),
but also see which plans user 13 is signed up
for, but only if the expire_date hasn't passed.
This select will show all Plan & SubType info,
but user-info only if the user is signed up,
and the expire-date hasn't passed.

SELECT
*
FROM mt_SubTypes, mt_Plans as t1
LEFT JOIN mt_UserPlans as t2
on (t1.id_plan = t2.id_plan
and t2.expire_date > '2003-11-12'
and t2.id_user = 13)
WHERE
t1.id_subType = mt_SubTypes.id_subType;
  Posted by Cory McHugh on December 22, 2003
This is an example of using a left to get lookup values from a table twice. The reason that an outer join was used instead of an inner join, was that in this case, there may be values that are null inside of the degree table.

SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId
  Posted by Joonas Kekoni on August 19, 2004
The oracle outter join syntax:
select a1.a,a1.b,a2.a,a2.b from a1,a2 where a1.a=a2.b(+);

Is expressed like:
select a1.a,a1.b,a2.a,a2.b from a1 left join a2 on a2.b=a1.a where 1=1;

NOTE: a2 is the optional table:

(PLEASE NOTE you must NOT specify a2 in from line(!!).
The 1=1 is optional,but I added it here so you could see where to add other conditions).

full example:
drop table a1; drop table a2;
create table a1 (a integer,b integer);
create table a2 (a integer,b integer);
insert into a1 (a,b) values (1,2);
insert into a1 (a,b) values (3,5);
insert into a2 (a,b) values (2,1);
insert into a2 (a,b) values (3,5);
select a1.a,a1.b,a2.a,a2.b from a1 left join a2 on a2.b=a1.a where 1=1;
drop table a1; drop table a2;

PS.
I prefer the Oracle syntax and would like to see it supported by MySql too.

  Posted by Fred Mitchell on December 11, 2004
Let's say you are doing a LEFT JOIN with a table that shares a column name in common with another table, and that you are selecting for instances where the join is missing, that is IS NULL.

Normally, the common column name is "wiped out" by the null record, but here is a workaround for it: You simply alias that common column name in the select. For instance,

CREATE TABLE t1 (INT id NOT NULL, ....);
CREATE TABLE t2 (INT id NOT NULL, ....);
...
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

would result in the column 'id' being null on each selected row. Instead, you can do:

SELECT *, t1.id AS id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

And now the 'id' column will be preserved since the alias is evaluated *after* the LEFT JOIN.
  Posted by on December 14, 2004
Note that table_reference in the above grammar may also be a "anonymous table" resulting from a join, as shown below. I don't know if this is intended or by occasion, but for me it works (MySQL 4.0.22):

SELECT products.id
FROM products
LEFT OUTER JOIN (
author2products
INNER JOIN author ON author.id = author2products.authorid
) ON products.id = author2products.productsid

This enables you to OUTER JOIN with the results of an INNER JOIN within one query.
  Posted by Ben Griffin on June 30, 2005
Scenario: A group of companies have a set of publications. Any company in the group can subscribe to any number of publications.
Requirement: Create a query to generate a list of companies, showing the count of publications and subscriptions for each company.

create table company ( cid int not null, cref char(64) not null,primary key (cid));
create table publication ( pid int not null, pcid int not null, pref char(64) not null,primary key (pid));
create table subscription ( scid int not null,spid int not null,primary key (scid,spid));

insert into company values (1,'A Corp');
insert into company values (2,'B Corp');
insert into company values (3,'C Corp');
insert into company values (4,'D Corp');
insert into company values (5,'E Corp');

insert into publication values (10,1,'A News');
insert into publication values (11,1,'A Digest');
insert into publication values (12,1,'A Review');
insert into publication values (20,2,'B News');
insert into publication values (51,5,'E Digest');
insert into publication values (52,5,'E Review');

insert into subscription values (1,10);
insert into subscription values (1,11);
insert into subscription values (1,20);
insert into subscription values (1,51);
insert into subscription values (1,52);
insert into subscription values (2,10);
insert into subscription values (4,10);
insert into subscription values (4,52);

select cref,count(distinct pid) as pubs,count(distinct spid) as subs from company left join publication on cid=pcid left join subscription on cid=scid group by cid order by cref;

+--------+------+------+
| cref | pubs | subs |
+--------+------+------+
| A Corp | 3 | 5 |
| B Corp | 1 | 1 |
| C Corp | 0 | 0 |
| D Corp | 0 | 2 |
| E Corp | 2 | 0 |
+--------+------+------+

  Posted by Mike Nix on August 24, 2005
This checks for an event that should happen yearly, depending on other factors - it could be applied to checking for any event that should occur at regular intervals - ie monthly or weekly - with adjustments to the code for selecting max_calves.

This is a fairly complex example, with lots of joins, aliases and grouping... Its aim is to extract a list of cows which have not calved every year in a given period. It accounts for cows which have not been in the database for the entire period, are not currently on our property (locations.local==1), or which are too young to have calves for the entire period (they can't calve before 2 years old).
There are many actions that signal entry/exit of an animal - eg birth/buy/sell/death. many (or none) of them may be recorded in the history table. As a fallback, animal.yob is the year of birth of the animal.
Cows and their calves are all stored as "animals" in the animals table (the mob field distinguishes between them).

The query returns the number of calves they did have in the time frame (num_calves) and the number of calves they should have had (max_calves).

Tables: (columns not relevant to query have been removed)
create table history (id int(10), date datetime, action int(2) not null, info text, primary key(id,date));
create table actions (id int(2) auto_increment primary key, name char(20), type char(20));
create table mob_types (id int(2) primary key, name char(20));
create table locations (id int(2) auto_increment primary key, name char(20), alive tinyint(1), local tinyint(1));
create table animals (id int(10) auto_increment primary key, yob int(4), tag1 char(10), mother int(10), mob int(2), location int(2));

Foreign keys:
animals.id <- history.id
animals.location -> locations.id
animals.mob -> mobs.id
mobs.type -> mob_types.id
history.action -> actions.id

The SQL to make it happen:
The original formatting is easier to read, but the leading spaces are not preserved here (the site won't let me use html to get it either)
$xxx are the input variables:
$start_year, $end_year: date range to check (inclusive)
$all_animals: 0= alive and on property, 1= everything
$nomiss: 0= those that missed a year, 1=those that didn't miss
$allow_miss: number of misses considered acceptable.

select a1.*,aquire.date,disposal.date,count(a2.id) num_calves,
(IF(IsNull(disposal.date),$end_year,
IF(DATE_FORMAT(disposal.date,'%Y') < $end_year,DATE_FORMAT(disposal.date,'%Y'),$end_year) -
IF(DATE_FORMAT(aquire.date,'%Y')>$start_year,
IF(DATE_FORMAT(aquire.date,'%Y')>(a1.yob+2),DATE_FORMAT(aquire.date,'%Y'),a1.yob+2),
IF(a1.yob>($start_year-2),a1.yob+2,$start_year)
)
+1) as max_calves
from animals as a1
left join animals as a2
on a1.id=a2.mother
and a2.yob>=$start_year
left join history as aquire
on a1.id=aquire.id
and aquire.action in (select id from actions where type='aquire')
left join history as disposal
on a1.id=disposal.id
and disposal.action in (select id from actions where type='disposal')
where a1.mob in (select id from mobs where mobtype in (select id from mob_types where name='Cow'))
and (($all_animals>0)
or a1.location in (select id from locations where alive=1 and local=1))
group by a1.id
having IF($nomiss>0, num_calves>=(max_calves-$allow_miss), num_calves<(max_calves-$allow_miss))
order by a1.location,cast(a1.tag1 as unsigned),a1.tag1;

Speed: with about 6000 records in the animals table, and appropriate indexes, this returns in under half a second on a modern machine (versus several seconds without indexes)

I still need to add support for twins (two events in same year should be counted as one), but I'm not sure how to do that yet (any suggestions?).

  Posted by Normann Aa. Nielsen on November 24, 2005
If you are running older versions of Bugzilla you should be aware that you'll have to do some modification yourself, because of the change in the JOIN-syntax. For a nice to-do, see http://www.chuckcaplan.com/blog/archives/2005/10/bugzilla_and_my.html

That really helped me!
  Posted by Odimar Tomazeli on January 19, 2006
Here a example join 3 tables like that

s_g_a_t
............|
............+-> s_c
.....................|
.....................+-> s_t_i

SELECT distinct sc.TType, si.Id
FROM s_g_a_t st LEFT JOIN s_c sc ON(st.GA = sc.GA and sc.id_app = 'XXX' and sc.Select = 1) INNER JOIN s_t_i si ON (sc.TType = si.DL)
Order by si.Id

  Posted by on June 15, 2006
This is an example for joining tables which are related by 2 fields.

Squeme:

tblA(idA, idB1, idB2)
tblB(idB, Name)
relations: tblA.idB1->tblB.IdB
tblB.idB2->tblB.IdB

and we want to obtain all records of tblA with its related tblB.Name

The sql statment could be like this:

SELECT
tblA.IdA,
tblB.Name AS Name1,
tblB_1.Name AS Name2
FROM
tblA INNER JOIN
tblB tblB_1 ON tblA.IdB1 = tblB_1.IdB
INNER JOIN tblB
ON tblA.IdB2 = tblB.IdB

(tested in MySql v4.0.24)
  Posted by barbarina on September 12, 2006
You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):

with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

with three tables t1, t2, t3:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

  Posted by Tobias Riemenschneider on September 26, 2006
The result of a full outer join betwee tables A and B includes:
- rows from the result of the inner join
- rows from A that don't have corresponding rows in B
- rows from B that don't have corresponding rows in A

Formally, the corresponding SQL statement looks like this:

SELECT *
FROM A JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

Due to the fact that the first union represents a left outer join, the statement can be simplified:

SELECT *
FROM A LEFT JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

  Posted by on March 16, 2007
The example posted on September 12 2006 about how to emulate FULL OUTER JOIN using UNION has a subtle problem. Assume you have 3 tables, each with one single colum "id" and 4 rows.
t0 contains 1,3,5,7,
t1 contains 2,3,6,7,
t2 contains 4,5,6,7
(ti contains j iff 2^i xor j = 1).

The suggested solution:

SELECT * FROM t0 LEFT JOIN t1 ON t0.id = t1.id LEFT JOIN t2 ON t1.id = t2.id UNION
SELECT * FROM t0 RIGHT JOIN t1 ON t0.id = t1.id LEFT JOIN t2 ON t1.id = t2.id UNION
SELECT * FROM t0 RIGHT JOIN t1 ON t0.id = t1.id RIGHT JOIN t2 ON t1.id = t2.id

produces

id id id
1 NULL NULL
3 3 NULL
5 NULL NULL
7 7 7
NULL 2 NULL
NULL 6 6
NULL NULL 4
NULL NULL 5

where "5" appears 2 times. To get the correct result, use only LEFT JOIN, start once with each table, and name the columns:

SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t0 LEFT JOIN t1 USING(id) LEFT JOIN t2 USING(id) UNION
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t1 LEFT JOIN t0 USING(id) LEFT JOIN t2 USING(id) UNION
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t2 LEFT JOIN t1 USING(id) LEFT JOIN t0 USING(id)
ORDER BY COALESCE(id0,id1,id2)

The ORDER BY is optional. The length of the query grows as the square of the number of tables, which is quite bad. FULL OUTER JOIN would be really welcome.

  Posted by Balaji Devarajan on March 18, 2007
I found this union LEFT JOIN AND RIGHT JOIN, display all the columns from the two tables very usefull for me...

mysql> select * from NAME;
+------+------+
| ID | NAME |
+------+------+
| 1 | bala |
| 2 | renu |
+------+------+
mysql> select * from DESCR;
+------+-------+
| ID | DESCR |
+------+-------+
| 2 | BBB |
| 3 | CCC |
+------+-------+
mysql> SELECT t1.ID,t1.NAME,t2.DESCR FROM NAME t1 LEFT JOIN DESCR t2 ON (t1.ID = t2.ID) UNION SELECT t2.ID,t1.NAME,t2.DESCR FROM NAME t1 RIGHT JOIN DESCR t
2 ON (t1.ID = t2.ID);
+------+------+-------+
| ID | NAME | DESCR |
+------+------+-------+
| 1 | bala | NULL |
| 2 | renu | BBB |
| 3 | NULL | CCC |
+------+------+-------+

  Posted by Ted Conn on December 21, 2007
Use group_concat to coalesce fields that are otherwise impossible when using an inner join.

For example:

SELECT T1.a,T1.b,T2.a,GROUP_CONCAT(T2.b ORDER BY T2.b DESC SEPARATOR '|') as d FROM T1 INNER JOIN T2 ON T1.a = T2.b GROUP BY T1.a

When this query might otherwise duplicate rows, adding the group_concat coalesces them as a blob which when printed returns all the values concatenated by the defined separator string!
  Posted by Wiebe Cazemier on March 25, 2008
For those who don't fully understand the concept of joins, I wrote an article which might help.

http://www.halfgaar.net/sql-joins-are-easy
  Posted by Geoffrey De Smet on July 29, 2008
Faking a full outer join through unions doesn't work when you need to do grouping to calculate totals.

For example:

select a.x, a.y, sum(a.price), sum(b.price)
from A a full join B b on a.x = b.x and a.y = b.y
where ...
group by a.x, a.y
order by a.x, a.y

A full join is needed because there are (x,y) combinations that exist only in A or only in B.
A group by is needed because in B the (x,y) combination isn't unique.
  Posted by Jan Brinkmann on September 7, 2011
As the MySQL manual doesn't explain all different join types in detail, you may find help here as well:

- MySQL join tutorials and examples: http://mysqljoin.com
  Posted by Matthew Looman on February 14, 2012
Scott Atkins on January 23 2003 posted about pivoting the rows in a table into the columns, by performing a separate join for each value based column. I've found it easier to use a single join. Then use the CASE statement in the SELECT clause to separate the columns. Given Scott's example, I would use the following syntax:

SELECT st.codename
, SUM(CASE sc.act_id WHEN 1 THEN sc.score ELSE NULL END) AS "Activity 1"
, SUM(CASE sc.act_id WHEN 2 THEN sc.score ELSE NULL END) AS "Activity 2"
, SUM(sc.score) AS "Total"
FROM students AS st
LEFT JOIN scores AS sc ON st.student_id = sc.student_id
WHERE st.codename != ''
GROUP BY st.codename
ORDER BY st.codename

Personally, I think the syntax is easier to read. Additionally, you are eliminating the number of nested loops required to join the same table multiple times.
  Posted by Andrew McNaughton on September 12, 2013
This page needs to make it explicit that a table reference can be of the form schema_name.tbl_name, and that joins between databases are therefore posible.
Sign Up Login You must be logged in to post a comment.