MaxComputeでは、JOIN操作を使用してテーブルを結合し、結合条件とクエリ条件を満たすデータを返すことができます。 このトピックでは、次のJOIN操作について説明します。LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、INNER JOIN、NATURAL JOIN、implicit JOIN、および複数のJOIN操作。
概要
MaxComputeは、次のタイプのJOIN操作をサポートしています。
LEFT OUTER JOINLEFT JOINとも呼ばれます。 LEFT OUTER JOINは、右側のテーブルの行と一致しない行を含む、左側のテーブルのすべての行を返します。説明JOIN操作では、ほとんどの場合、左側のテーブルは大きなテーブルであり、右側のテーブルは小さなテーブルです。 右側のテーブルの一部の行の値が重複している場合は、複数の連続したLEFT JOIN操作を実行しないことを推奨します。 複数の連続したLEFT JOIN操作を実行すると、データの膨張が発生し、ジョブが中断されることがあります。RIGHT OUTER JOINRIGHT JOINとも呼ばれます。 RIGHT OUTER JOINは、左側のテーブルの行と一致しない行を含む、右側のテーブルのすべての行を返します。FULL OUTER JOINFULL JOINとも呼ばれます。 FULL OUTER JOINは、左右両方のテーブルのすべての行を返します。INNER JOININNERキーワードは省略できます。INNER JOINは、左右のテーブルに一致がある場合、データ行を返します。NATURAL JOINNATURAL JOINオペレーションでは、2つのテーブルを結合するために使用されるフィールドは、2つのテーブルの共通フィールドに基づいて決定される。 MaxComputeはOUTER NATURAL JOINをサポートしています。USING句を使用する場合、NATURAL JOIN操作は共通フィールドを1回だけ返します。Implicit 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:
select * from sale_detail;
+------------+-------------+-------------+------------+------------+
| 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 |
+------------+-------------+-------------+------------+------------+
select * from sale_detail_jt;
+------------+-------------+-------------+------------+------------+
| 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;例
例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: RIGHT 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 right outer join sale_detail b on a.shop_name=b.shop_name;次の応答が返されます。
+------------+------------+ | ashop | bshop | +------------+------------+ | NULL | s3 | | NULL | s6 | | NULL | null | | s2 | s2 | | NULL | s7 | | s1 | s1 | +------------+------------+例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: implicit 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 | +------------+-------------+-------------+------------+------------+例7: 複数の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 | +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+例8:
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つのテーブルの共通部分です。