MaxComputeでは、JOIN操作を使用してテーブルを結合し、結合条件とクエリ条件を満たすデータを返すことができます。 このトピックでは、次のJOIN操作について説明します。LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN、INNER JOIN、NATURAL JOIN、暗黙のJOIN、および複数のJOIN操作。
説明
MaxComputeは、次のタイプのJOIN操作をサポートしています。
LEFT OUTER JOINLEFT JOINとも呼ばれます。 LEFT OUTER 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句を使用する場合、JOIN操作は共通フィールドを1回だけ返します。暗黙のJOIN操作
JOINキーワードを指定しなくても、暗黙的なJOIN操作を実行できます。複数のJOIN操作
MaxComputeは複数の
JOIN操作をサポートしています。 括弧 () を使用して、JOIN操作の優先度を指定できます。 括弧 () で囲まれたJOIN操作の方が優先度が高くなります。
説明
SQL文にWHERE句が含まれていて、WHERE句の前にJOIN句を使用している場合、JOIN操作が最初に実行されます。 次に、JOIN操作から得られた結果は、WHERE句で指定された条件に基づいてフィルタリングされます。 最終的な結果は、左側のテーブルのすべての行ではなく、2つのテーブルの交差です。
制限事項
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 partitioned tables sale_detail and sale_detail_jt.
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 partitioned tables sale_detail and sale_detail_jt.
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 in the sale_detail and sale_detail_jt table. Sample statement:
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 |
+------------+-------------+-------------+------------+------------+
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 that you want to join.
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: 右外側の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 statements. 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 | +------------+-------------+-------------+------------+------------+例7: 複数のJOIN操作。 JOIN操作の優先度を指定するには、かっこ () を使用します。 サンプル文:
-- The full table scan feature must be enabled for partition 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つのテーブルの共通部分です。