MaxComputeでは、JOIN操作を使用してテーブルを結合し、結合条件とクエリ条件を満たすデータを返すことができます。 このトピックでは、次のJOIN操作について説明します。LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、INNER JOIN、NATURAL JOIN、暗黙のJOIN、および複数のJOIN操作。
概要
MaxComputeは、次のタイプのJOIN操作をサポートしています。
左外側の参加LEFT JOINとも呼ばれます。 LEFT OUTER JOINは、右側のテーブルの行と一致しない行を含む、左側のテーブルのすべての行を返します。説明JOIN操作では、ほとんどの場合、左側のテーブルは大きなテーブルであり、右側のテーブルは小さなテーブルです。 右側のテーブルの一部の行の値が重複している場合は、複数の連続したLEFT JOIN操作を実行しないことを推奨します。 複数の連続したLEFT JOIN操作を実行すると、データの膨張が発生し、ジョブが中断されることがあります。右外側の参加RIGHT JOINとも呼ばれます。 RIGHT OUTER JOINは、左側のテーブルの行と一致しない行を含む、右側のテーブルのすべての行を返します。FULL OUTER JOINFULL JOINとも呼ばれます。 FULL OUTER JOINは、左右両方のテーブルのすべての行を返します。INNER JOININNERキーワードは省略できます。INNER JOINは、左右のテーブルに一致がある場合、データ行を返します。自然な参加NATURAL JOINオペレーションでは、2つのテーブルを結合するために使用されるフィールドは、2つのテーブルの共通フィールドに基づいて決定される。 MaxComputeはOUTER NATURAL JOINをサポートしています。USING句を使用する場合、NATURAL JOIN操作は共通フィールドを1回だけ返します。暗黙のJOIN操作
JOINキーワードを指定しなくても、暗黙的な
JOIN操作を実行できます。複数のJOIN操作
MaxComputeは複数の
JOIN操作をサポートしています。 括弧 () を使用して、JOIN操作の優先度を指定できます。 括弧 () で囲まれたJOIN操作の方が優先度が高くなります。
SQL文に
WHERE句が含まれていて、WHERE句の前にJOIN句を使用している場合、JOIN操作が最初に実行されます。 次に、JOIN操作から得られた結果は、WHERE句で指定された条件に基づいてフィルタリングされます。 最終的な結果は、テーブル内のすべての行ではなく、2つのテーブルの共通部分です。odps.task.sql.outerjoin.ppdパラメーターを使用して、
OUTER JOIN ON句のJOIN以外の条件をJOIN操作の入力データとして使用するかどうかを制御できます。 このパラメーターは、プロジェクトまたはセッションレベルで設定できます。このパラメーターを
falseに設定した場合、ON句のJOIN以外の条件は、JOIN操作のサブクエリのWHERE句の条件と見なされます。 これは非標準的な動作です。WHERE句でJOIN以外の条件を指定することを推奨します。このパラメーターを
falseに設定した場合、次のSQL文は同等です。 このパラメーターをtrueに設定した場合、2つのSQL文は同等ではありません。
SELECT A.*, B.* FROM A LEFT JOIN B ON A.c1 = B.c1 and A.c2='xxx'; SELECT A.*, B.* FROM (SELECT * FROM A WHERE c2='xxx') A LEFT JOIN B ON A.c1 = B.c1;
使用上の注意
JOIN操作では、JOIN操作のフィルタ条件キーがnullではないが自動的に追加されて計算されます。 結合キーの値がnullである行は、join操作後に除外されます。
制限事項
JOIN操作を実行するときは、次の制限に注意してください。
MaxComputeは
CROSS JOINをサポートしていません。 CROSS JOIN操作では、ON句で条件を指定せずに2つのテーブルを結合します。andを使用して、equi-joinsと結合条件を使用する必要があります。 非equi結合を使用するか、MAPJOIN操作でorを使用して複数の条件を組み合わせることができます。 詳細については、「MAPJOIN」をご参照ください。
構文
<table_reference> JOIN <table_factor> [<join_condition>]
| <table_reference> {LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER|NATURAL} JOIN <table_reference> <join_condition>table_reference: 必須です。
JOIN操作が実行される左側のテーブルのクエリ文。 このパラメーターの値は、table_name [alias] | table_query [alias] |...形式です。table_factor: 必須です。 正しいテーブルまたは
JOIN操作が実行されるテーブルのクエリ文。 このパラメーターの値は、table_name [alias] | table_subquery [alias] |...形式です。join_condition: オプションです。
JOIN条件は、1つ以上の等式の組み合わせです。 このパラメーターの値は、on equality_expression [and equality_expression]...形式です。equision_expressionは等式式です。
WHERE句でパーティションプルーニング条件が指定されている場合、パーティションプルーニングは親テーブルと子テーブルの両方で有効になります。 ON句でパーティションプルーニング条件が指定されている場合、パーティションプルーニングは子テーブルでのみ有効になります。 その結果、親テーブルに対してテーブル全体のスキャンが実行されます。 詳細については、「パーティションプルーニングが有効かどうかの確認」をご参照ください。
サンプルデータ
サンプルソースデータは、このトピックの例をよりよく理解するために提供されています。 次のステートメントは、sale_detailテーブルとsale_detail_jtテーブルを作成し、テーブルにデータを挿入する方法を示しています。
-- Create two partitioned tables named sale_detail and sale_detail_jt.
CREATE TABLE if NOT EXISTS sale_detail
(
shop_name STRING,
customer_id STRING,
total_price DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);
CREATE TABLE if NOT EXISTS sale_detail_jt
(
shop_name STRING,
customer_id STRING,
total_price DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);
-- Add partitions to the two tables.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
ALTER TABLE sale_detail_jt ADD PARTITION (sale_date='2013', region='china');
-- Insert data into the tables.
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
INSERT INTO sale_detail_jt PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);
-- Query data from the sale_detail and sale_detail_jt tables. Sample statements:
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
-- The following result is returned:
+------------+-------------+-------------+------------+------------+
| shop_name | customer_id | total_price | sale_date | region |
+------------+-------------+-------------+------------+------------+
| s1 | c1 | 100.1 | 2013 | china |
| s2 | c2 | 100.2 | 2013 | china |
| s3 | c3 | 100.3 | 2013 | china |
| null | c5 | NULL | 2014 | shanghai |
| s6 | c6 | 100.4 | 2014 | shanghai |
| s7 | c7 | 100.5 | 2014 | shanghai |
+------------+-------------+-------------+------------+------------+
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt;
-- The following result is returned:
+------------+-------------+-------------+------------+------------+
| shop_name | customer_id | total_price | sale_date | region |
+------------+-------------+-------------+------------+------------+
| s1 | c1 | 100.1 | 2013 | china |
| s2 | c2 | 100.2 | 2013 | china |
| s5 | c2 | 100.2 | 2013 | china |
+------------+-------------+-------------+------------+------------+
-- Create a table for the JOIN operation.
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;例
次の例は、サンプルデータに基づくJOINの使用方法を示しています。
例1: LEFT OUTER JOIN。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a LEFT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;次の応答が返されます。
+------------+------------+ | ashop | bshop | +------------+------------+ | s2 | s2 | | s1 | s1 | | s5 | NULL | +------------+------------+例2: 右外側のJOIN。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a RIGHT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;次の応答が返されます。
+------------+------------+ | ashop | bshop | +------------+------------+ | s1 | s1 | | s2 | s2 | | NULL | s3 | | NULL | null | | NULL | s6 | | NULL | s7 | +------------+------------+例3: FULL OUTER JOIN。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;次の応答が返されます。
+------------+------------+ | ashop | bshop | +------------+------------+ | NULL | s3 | | NULL | s6 | | s2 | s2 | | NULL | null | | NULL | s7 | | s1 | s1 | | s5 | NULL | +------------+------------+例4: INNER JOIN。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a INNER JOIN sale_detail b ON a.shop_name=b.shop_name;次の応答が返されます。
+------------+------------+ | ashop | bshop | +------------+------------+ | s2 | s2 | | s1 | s1 | +------------+------------+例5: NATURAL JOIN。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Perform a NATURAL JOIN operation. SELECT * FROM sale_detail_jt NATURAL JOIN sale_detail; -- The preceding statement is equivalent to the following statement: SELECT sale_detail_jt.shop_name AS shop_name, sale_detail_jt.customer_id AS customer_id, sale_detail_jt.total_price AS total_price, sale_detail_jt.sale_date as sale_date, sale_detail_jt.region as region from sale_detail_jt INNER JOIN sale_detail ON sale_detail_jt.shop_name=sale_detail.shop_name AND sale_detail_jt.customer_id=sale_detail.customer_id and sale_detail_jt.total_price=sale_detail.total_price AND sale_detail_jt.sale_date=sale_detail.sale_date AND sale_detail_jt.region=sale_detail.region;次の応答が返されます。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s1 | c1 | 100.1 | 2013 | china | | s2 | c2 | 100.2 | 2013 | china | +------------+-------------+-------------+------------+------------+例6: 暗黙のJOIN。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Perform an implicit JOIN operation. SELECT * FROM sale_detail_jt, sale_detail WHERE sale_detail_jt.shop_name = sale_detail.shop_name; -- The preceding statement is equivalent to the following statement: SELECT * FROM sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name;次の応答が返されます。
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2 | +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+ | s2 | c2 | 100.2 | 2013 | china | s2 | c2 | 100.2 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | s1 | c1 | 100.1 | 2013 | china | +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+例7: 複数のJOIN操作。 優先度は指定されていません。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. SELECT a.* FROM sale_detail_jt a FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name FULL OUTER JOIN sale_detail c ON a.shop_name=c.shop_name;次の応答が返されます。
+------------+-------------+-------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | +------------+-------------+-------------+------------+------------+ | s5 | c2 | 100.2 | 2013 | china | | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | | s1 | c1 | 100.1 | 2013 | china | | NULL | NULL | NULL | NULL | NULL | | s2 | c2 | 100.2 | 2013 | china | | NULL | NULL | NULL | NULL | NULL | +------------+-------------+-------------+------------+------------+例8: 複数のJOIN操作。 JOIN操作の優先度を指定するには、かっこ () を使用します。 サンプル文:
-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Perform multiple JOIN operations. Use parentheses () to specify the priority. SELECT * FROM shop JOIN (sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name) ON shop.shop_name=sale_detail_jt.shop_name;次の応答が返されます。
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+ | shop_name | customer_id | total_price | sale_date | region | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2 | shop_name3 | customer_id3 | total_price3 | sale_date3 | region3 | +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+ | s2 | c2 | 100.2 | 2013 | china | s2 | c2 | 100.2 | 2013 | china | s2 | c2 | 100.2 | 2013 | china | | s1 | c1 | 100.1 | 2013 | china | s1 | c1 | 100.1 | 2013 | china | s1 | c1 | 100.1 | 2013 | china | +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+例9:
JOINとWHEREを使用して、2つのテーブルでリージョンがchinaでshop_nameフィールドの値が同じレコードの数を照会します。 sale_detailテーブル内のすべてのレコードが保持されます。 サンプル文:-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. SET odps.sql.allow.fullscan=true; -- Execute the following SQL statement: SELECT a.shop_name ,a.customer_id ,a.total_price ,b.total_price FROM (SELECT * FROM sale_detail WHERE region = "china") a LEFT JOIN (SELECT * FROM sale_detail_jt WHERE region = "china") b ON a.shop_name = b.shop_name;次の応答が返されます。
+------------+-------------+-------------+--------------+ | shop_name | customer_id | total_price | total_price2 | +------------+-------------+-------------+--------------+ | s1 | c1 | 100.1 | 100.1 | | s2 | c2 | 100.2 | 100.2 | | s3 | c3 | 100.3 | NULL | +------------+-------------+-------------+--------------+不正使用のサンプルステートメント:
SELECT a.shop_name ,a.customer_id ,a.total_price ,b.total_price FROM sale_detail a LEFT JOIN sale_detail_jt b ON a.shop_name = b.shop_name WHERE a.region = "china" AND b.region = "china";次の応答が返されます。
+------------+-------------+-------------+--------------+ | shop_name | customer_id | total_price | total_price2 | +------------+-------------+-------------+--------------+ | s1 | c1 | 100.1 | 100.1 | | s2 | c2 | 100.2 | 100.2 | +------------+-------------+-------------+--------------+返される結果は、sale_detailテーブルのすべての行ではなく、2つのテーブルの共通部分です。