すべてのプロダクト
Search

このプロダクト

    MaxCompute:MaxCompute SQLでのJOIN操作

    ドキュメントセンター

    MaxCompute:MaxCompute SQLでのJOIN操作

    最終更新日:Jan 17, 2025

    MaxCompute SQL文を記述し、JOIN操作とデータのフィルタリングを同時に実行する場合は、さまざまな種類のJOIN操作でのフィルタ条件の位置に特別な注意を払う必要があります。 計算シーケンスの違いは、異なるクエリ結果につながる可能性があります。 このトピックでは、クエリ結果に対するさまざまなJOIN操作のさまざまな位置でのフィルター条件の影響について説明します。

    概要

    次の表に、MaxCompute SQLがサポートするJOIN操作について説明します。

    API 操作

    説明

    INNER JOIN

    結合条件に基づいて、左テーブルと右テーブルの両方で一致する列値を持つ行を返します。

    LEFT JOIN

    結合条件に基づいて、左側のテーブルのすべての行と右側のテーブルの一致した行を返します。 左側のテーブルの行に右側のテーブルの行が一致しない場合、結果セットの右側のテーブルの列にNULL値が返されます。

    RIGHT JOIN

    結合条件に基づいて、右側のテーブルからすべての行を返し、左側のテーブルから一致した行を返します。 右のテーブルの行に左のテーブルに一致する行がない場合、結果セットの左のテーブルの列にNULL値が返されます。

    FULL JOIN

    結合条件が満たされているかどうかにかかわらず、左側のテーブルと右側のテーブルの両方のすべての行を返します。 結果セットでは、他のテーブルの一致する行がないテーブルの列にNULL値が返されます。

    LEFT SEMI JOIN

    右側のテーブルに一致する行を持つ左側のテーブルの行のみを返します。

    LEFT ANTI JOIN

    右側のテーブルに一致する行がない左側のテーブルの行のみを返します。

    ON句とWHERE句は同じSQL文で使用できます。 たとえば、次のSQL文を考えます。 

    (SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

    上記のSQL文の条件は、次の順序で評価されます。

    1. サブクエリのWHERE句の {subquery_where_condition} 条件

    2. ON句の {on_condition} 条件

    3. JOIN句の後のWHERE句の {where_condition} 条件

    したがって、JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに応じて、異なる結果を返す場合があります。 詳細については、「サンプルシナリオ」をご参照ください。

    テストテーブル

    • テーブルA

      例： 

      CREATE TABLE A AS SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);

      Sample data:

      キー

      ds

      1

      20180101

      2

      20180101

      2

      20180102

    • テーブルB

      例： 

      CREATE TABLE B AS SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);

      Sample data:

      キー

      ds

      1

      20180101

      3

      20180101

      2

      20180102

    • テーブルAとテーブルBのデカルト積

      次のSQL文は、デカルト積の計算に使用されます。

      SET odps.sql.allow.cartesian=true;
SELECT * FROM A,B;

      次の結果が返されます。

      +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 1    | 20180101 |
| 2    | 20180102 | 1    | 20180101 |
| 1    | 20180101 | 3    | 20180101 |
| 2    | 20180101 | 3    | 20180101 |
| 2    | 20180102 | 3    | 20180101 |
| 1    | 20180101 | 2    | 20180102 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

    サンプルシナリオ

    • INNER JOIN

      INNER JOIN操作は、最初にテーブルAとテーブルBの行のデカルト積を取得し、on句に基づいてテーブルAとテーブルBの列値が一致する行を返します。

      結論: INNER JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに関係なく、同じ結果を返します。

      • ケース1: 次の文に示すように、{subquery_where_condition} 句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

        次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

      • ケース2: 次の文に示すように、{on_condition} 句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        表Aと表Bのデカルト積は9つの行を含み、そのうちの1つだけが結合条件を満たす。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

      • ケース3: 次のステートメントに示すように、ON句の後のWHERE句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

        表Aと表Bのデカルト積は9つの行を含み、そのうち3つだけが結合条件を満たす。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

        次に、クエリプロセッサは、A.ds='20180101' and B.ds='20180101' のフィルタ条件に基づいて、先行する結果セットをフィルタリングする。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

    • LEFT JOIN

      LEFT JOIN演算は、最初に表Aと表Bの行のデカルト積を取り、結合条件を満たす表Aのすべての行と表Bの行を返します。 結合条件で、テーブルaの行に対してテーブルBに一致する行が見つからない場合、テーブルAの行は、テーブルBの各列にNULL値を持つ結果セットで返されます。

      結論: LEFT JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに応じて、異なる結果を返す場合があります。

      • テーブルAのフィルター条件が {subquery_where_condition} または {where_condition} で指定されているかどうかに関係なく、操作は同じ結果を返します。

      • テーブルBのフィルター条件が {subquery_where_condition} または {on_condition} で指定されているかどうかに関係なく、操作は同じ結果を返します。

      • ケース1: 次の文に示すように、{subquery_where_condition} 句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

        次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
+------+------+------+------+

      • ケース2: 次の文に示すように、{on_condition} 句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        表Aと表Bのデカルト積は9つの行を含み、そのうちの1つだけが結合条件を満たす。 テーブルAの他の2つの行にはテーブルBの行が一致しないため、テーブルAの2つの行のテーブルBの列にはNULL値が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
+------+------+------+------+

      • ケース3: 次のステートメントに示すように、ON句の後のWHERE句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

        表Aと表Bのデカルト積は9つの行を含み、そのうち3つだけが結合条件を満たす。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

        次に、クエリプロセッサは、A.ds='20180101' and B.ds='20180101' のフィルタ条件に基づいて、先行する結果セットをフィルタリングする。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

    • RIGHT JOIN

      RIGHT JOIN動作は、2つのテーブルが逆の方法で使用されることを除いて、LEFT JOIN動作と同様である。 RIGHT JOIN操作は、結合条件を満たすテーブルBのすべての行とテーブルAの行を返します。

      • 結論: RIGHT JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに応じて、異なる結果を返す場合があります。

      • テーブルBのフィルター条件が {subquery_where_condition} または {where_condition} で指定されているかどうかに関係なく、操作は同じ結果を返します。

      • テーブルAのフィルター条件が {subquery_where_condition} または {on_condition} で指定されているかどうかに関係なく、操作は同じ結果を返します。

    • FULL JOIN

      FULL JOIN演算は、表Aと表Bの行のデカルト積を取り、結合条件が満たされているかどうかにかかわらず、表Aと表Bのすべての行を返します。 結果セットでは、他のテーブルの一致する行がないテーブルの列にNULL値が返されます。

      結論: FULL JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに応じて、異なる結果を返す場合があります。

      • ケース1: 次の文に示すように、{subquery_where_condition} 句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
FULL JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

        次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

      • ケース2: 次の文に示すように、{on_condition} 句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        表Aと表Bのデカルト積は9つの行を含み、そのうちの1つだけが結合条件を満たす。 結果セットでは、表Bの行に一致しない表Aの2つの行については、表Bの列にNULL値が返されます。表Aの行に一致しない表Bの2つの行については、表Aの列にNULL値が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| NULL | NULL | 2    | 20180102 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

      • ケース3: 次のステートメントに示すように、ON句の後のWHERE句でフィルター条件を指定します。 

        SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

        表Aと表Bのデカルト積は9つの行を含み、そのうち3つだけが結合条件を満たす。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |

        テーブルAに一致する行がないテーブルBの行が結果セットに返され、その行のテーブルAの列にはNULL値が含まれます。 次の結果が返されます。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

        次に、クエリプロセッサは、A.ds='20180101' and B.ds='20180101' のフィルタ条件に基づいて、先行する結果セットをフィルタリングする。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

    • LEFT SEMI JOIN

      LEFT SEMI JOIN操作では、テーブルBから行が返されません。したがって、ON句の後のWHERE句でテーブルBのフィルター条件を指定することはできません。

      結論: LEFT SEMI JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに関係なく、同じ結果を返します。

      • ケース1: 次の文に示すように、{subquery_where_condition} 句でフィルター条件を指定します。 

        SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

        次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

      • ケース2: 次の文に示すように、{on_condition} 句でフィルター条件を指定します。 

        SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

      • ケース3: 次のステートメントに示すように、ON句の後のWHERE句でフィルター条件を指定します。 

        SELECT A.*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

        次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

        次に、クエリプロセッサは、A.ds='20180101' のフィルタ条件に基づいて、先行する結果セットをフィルタリングする。 次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

    • LEFT ANTI JOIN

      LEFT ANTI JOIN操作では、テーブルBから行が返されません。したがって、ON句の後のWHERE句でテーブルBのフィルター条件を指定することはできません。 LEFT ANTI JOIN操作は通常、NOT EXISTS構文を置き換えるために使用されます。

      結論: LEFT ANTI JOIN操作は、フィルター条件が {subquery_where_condition} 、{on_condition} 、または {where_condition} で指定されているかどうかに応じて、異なる結果を返す場合があります。

      • テーブルAのフィルター条件が {subquery_where_condition} または {where_condition} で指定されているかどうかに関係なく、操作は同じ結果を返します。

      • テーブルBのフィルター条件が {subquery_where_condition} または {on_condition} で指定されているかどうかに関係なく、操作は同じ結果を返します。

      • ケース1: 次の文に示すように、{subquery_where_condition} 句でフィルター条件を指定します。 

        SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

        次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+

      • ケース2: 次の文に示すように、{on_condition} 句でフィルター条件を指定します。 

        SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+

      • ケース3: 次のステートメントに示すように、ON句の後のWHERE句でフィルター条件を指定します。 

        SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

        次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+

        次に、クエリプロセッサは、A.ds='20180101' のフィルタ条件に基づいて、先行する結果セットをフィルタリングする。 次の結果が返されます。 

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+

    注意事項

    • INNER JOIN操作またはLEFT SEMI JOIN操作の場合、左側のテーブルと右側のテーブルのフィルター条件の指定場所に関係なく、SQL文は同じ結果を返します。

    • LEFT JOIN操作またはLEFT ANTI JOIN操作の場合、左のテーブルのフィルタ条件は、{subquery_where_condition} または {where_condition} で指定されているかどうかにかかわらず、同じように機能します。 右側のテーブルのフィルター条件は、{subquery_where_condition} または {on_condition} で指定されているかどうかにかかわらず、同じ関数です。

    • RIGHT JOIN操作の場合、左側のテーブルのフィルタ条件は、{subquery_where_condition} または {on_condition} で指定されているかどうかにかかわらず同じ機能をします。 右テーブルのフィルタ条件は、{subquery_where_condition} または {where_condition} で指定されているかどうかにかかわらず、同じように機能します。

    • FULL OUTER JOIN操作の場合、フィルター条件は {subquery_where_condition} でのみ指定できます。

    関連ドキュメント

    • MaxComputeでは、特定の基本的なJOIN操作を実行できます。 MaxComputeでサポートされている基本的なJOIN操作の詳細については、「JOIN」および「SEMI JOIN」をご参照ください。

    • 大きなテーブルと小さなテーブルを結合する場合は、MAPJOINヒントを明示的に指定してクエリのパフォーマンスを向上させることができます。 MAPJOIN HINTの詳細については、「MAPJOIN HINT」をご参照ください。

    • 大きなテーブルと中サイズのテーブルを結合する場合は、DISTRIBUTED MAPJOINを使用してクエリのパフォーマンスを向上させることができます。 DISTRIBUTED MAPJOINの詳細については、「DISTRIBUTED MAPJOIN」をご参照ください。

    • 結合する2つのテーブルにホットキー値が含まれている場合、ロングテールの問題が発生する可能性があります。 SKEWJOIN HINTを使用して、2つのテーブルからホットキー値を抽出し、処理速度を高速化できます。 SKEWJOIN HINTの詳細については、「SKEWJOIN HINT」をご参照ください。