サブクエリの基本定義
column_1, column_2 … from table_name
などが挙げられます。
しかし、クエリオブジェクトは、次のように別の SELECT 操作で記述することもできます。select * from (select shop_name from sale_detail) a;
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;