サブクエリの基本定義

通常の SELECT 操作では、複数のテーブルからデータが読み込まれます。例として、select column_1, column_2 … from table_name などが挙げられます。  しかし、クエリオブジェクトは、次のように別の SELECT 操作で記述することもできます。
select * from (select shop_name from sale_detail) a;
注 サブクエリでは、別名を指定する必要があります。
FROM 句では、サブクエリをテーブルとして使用して、他のテーブルやサブクエリとの JOIN 操作を実行できます。例：
create table shop as select * from sale_detail;
select a.shop_name, a.customer_id, a.total_price from
(select * from shop) a join sale_detail on a.shop_name = sale_detail.shop_name;

IN サブクエリ/NOT IN サブクエリ

IN サブクエリは、LEFT SEMI JOIN と同様の処理を行います。

例:
SELECT * from mytable1 where id in (select id from mytable2);
-- 上の文は下の文と同じです。
SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;

現在、MaxCompute では、IN サブクエリと相関条件の両方に対応しています。

例:
SELECT * from mytable1 where id in (select id from mytable2 where value = mytable1.value);

サブクエリ内の where value = mytable1.value は、相関条件です。 MaxCompute の以前のバージョンでは、サブクエリと外部クエリの両方でソーステーブルを参照する式があると、エラーが返されました。 現在、MaxCompute は、このような式にも対応しています。 実際、このようなフィルタリング条件は、SEMI JOIN の ON 条件の一部です。

NOT IN サブクエリは、LEFT ANTI JOIN と同様の処理を行います。 ただし、大きな違いが 1 つあります。

例:
SELECT * from mytable1 where id not in (select id from mytable2);
-- mytable2 で、どの ID も NULL でなければ、上の文は下の文と同じです。
SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;

mytable2 に ID が NULL の列がある場合、NOT IN 式は NULL になるため、WHERE 条件は無効となり、データは返されません。 これが LEFT ANTI JOIN と異なる点です。

MaxCompute 1.0 では、 [NOT] IN サブクエリが JOIN 条件として機能しなければ (例：WHERE 文以外)、使用できます。WHERE 文の中であっても、JOIN 条件ヘの変換時にエラーが発生します。 MaxCompute 2.0 では、 引き続きこの機能を使用できます。 ただし、[NOT] IN サブクエリを SEMI JOIN に変換できないので、サブクエリを実行するには、別のジョブを開始する必要があります。 [NOT] IN サブクエリは、相関条件には対応していません。

例:
SELECT * from mytable1 where id in (select id from mytable2) OR value > 0;

WHERE 句に OR が含まれるので、[NOT] IN サブクエリは SEMI JOIN に変換できません。 サブクエリを実行するには、別のジョブを開始する必要があります。

また、パーティションテーブルを特別に処理します。
SELECT * from sales_detail where ds in (select dt from sales_date);

ds がパーティション列の場合、select dt from sales_date は SEMI JOIN を変換せずに、サブクエリを実行するためのジョブを開始します。 実行後、結果は ds と 1 つずつ比較されます。 sales_detail の ds 値が、戻された結果の中にない場合、パーティションプルーニングの有効性を保証するために、パーティションは読み取られません。

EXISTS サブクエリ/NOT EXISTS サブクエリ

EXISTS サブクエリでは、少なくとも 1 つのデータ行がサブクエリ内にあれば、TRUE が返されます。1 行もない場合には、FALSE が返されます。 NOT EXISTS サブクエリは、これと全く逆です。

現在、MaxCompute では、相関 WHERE 条件を含むサブクエリのみ使用できます。 EXISTS サブクエリや NOT EXISTS サブクエリは、LEFT SEMI JOIN、LEFT ANTI JOIN に変換されてから実行されます。

例:
SELECT * from mytable1 where exists (select * from mytable2 where id = mytable1.id);
-- 上の文は下の文と同じです。
Select * From mytable1 a left semi join mytable2 B on A. ID = B. ID;
また、
SELECT * from mytable1 where not exists (select * from mytable2 where id = mytable1.id);
-- 上の文は下の文と同じです。
SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;