すべてのプロダクト
Search

このプロダクト

    MaxCompute:サブクエリ

    ドキュメントセンター

    MaxCompute:サブクエリ

    最終更新日:May 30, 2025

    クエリの結果に基づいてさらにクエリを実行する場合、サブクエリを使用できます。たとえば、集計値を個別に計算する場合、レコードが存在するかどうかを確認する場合、クエリによって返されるデータをフィルタリングする場合、更新または削除操作に関連付ける場合、JOIN 操作を簡略化して値を取得する場合、クエリの結果を主クエリの派生テーブルとして使用する場合、またはクエリの結果をソートまたはグループ化および行ごとの比較の基準として使用する場合に、サブクエリを使用できます。このトピックでは、MaxCompute でサポートされているサブクエリの定義と使用方法について説明します。

    説明

    サブクエリは、複雑なデータクエリを実行するために文の中にネストされています。MaxCompute は、次の種類のサブクエリをサポートしています。

    • 基本サブクエリ

      FROM 句のサブクエリは、クエリでの複雑な計算やデータ変換のための一時テーブルとして使用されます。

    • IN サブクエリ

      サブクエリによって返される値のグループと一致させる場合、WHERE 句で IN サブクエリを使用できます。IN サブクエリは、指定された条件に一致する行からデータをクエリする場合に適用できます。

    • NOT IN サブクエリ

      NOT IN サブクエリは、あるコレクションを別のコレクションから除外するために使用されます。WHERE 句で NOT IN サブクエリを使用すると、サブクエリの条件に一致する行が主クエリの結果から削除されます。

    • EXISTS サブクエリ

      EXISTS サブクエリは、サブクエリが何らかの行を返すかどうかを確認するために主クエリで使用されます。EXISTS サブクエリは、返されるコンテンツに関係なく、サブクエリにレコードが存在するかどうかを確認する場合に適用できます。

    • NOT EXISTS サブクエリ

      NOT EXISTS サブクエリは、EXISTS サブクエリとは反対の働きをします。主クエリのレコードは、サブクエリが行を返さない場合にのみ選択されます。NOT EXISTS サブクエリは、サブクエリで一致しない行をクエリする場合に適用できます。

    • スカラサブクエリ

      スカラサブクエリは、1 つの行から正確に 1 つの列の値を返すサブクエリです。ほとんどの場合、SELECT 文または WHERE 句または HAVING 句でスカラサブクエリを使用できます。スカラサブクエリは、特定の集計値を計算したり、行から列値を取得したりする場合に適用できます。

    説明

    スカラ、IN、NOT IN、EXISTS、NOT EXISTS などの一部のサブクエリは、実行中に JOIN 操作に変換できます。MAPJOIN は効率的な JOIN アルゴリズムです。サブクエリの結果が小さなテーブルである場合、サブクエリ文でヒントを使用して MAPJOIN アルゴリズムを明示的に指定できます。

    サンプルデータ

    このトピックの例をよりよく理解するために、サンプルのソースデータが提供されています。サンプル文:

    -- sale_detail という名前のパーティションテーブルを作成します。
create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);

-- sale_detail テーブルにパーティションを追加します。
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');

-- sale_detail テーブルにデータを挿入します。
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);

    sale_detail テーブルのデータをクエリします。サンプル文:

    set odps.sql.allow.fullscan=true;
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 文をクエリ オブジェクトとして使用することもできます。この場合、SELECT 文はサブクエリと見なされます。FROM 句のサブクエリは、テーブルとして使用できます。サブクエリを他のテーブルまたはサブクエリと join できます。JOIN 操作の詳細については、「JOIN」をご参照ください。

    • 構文

      select <select_expr> from (<select_statement>) [<sq_alias_name>];

    • パラメーター

      • select_expr: 必須。このパラメーターの値は、col1_name, col2_name, 正規表現,... の形式です。この形式は、クエリする一般的な列またはパーティションキー列、あるいはクエリに使用される正規表現を示します。

      • select_statement: 必須。このパラメーターは、サブクエリ句を指定します。構文の詳細については、「SELECT 構文」をご参照ください。

      • sq_alias_name: オプション。このパラメーターは、サブクエリのエイリアスを指定します。

      • table_name: 必須。このパラメーターは、クエリするテーブルの名前を指定します。

    • 例

      • 例 1: サブクエリ構文文:

        set odps.sql.allow.fullscan=true;
select * from (select shop_name from sale_detail) a;

        次の結果が返されます。

        +------------+
| shop_name  |
+------------+
| s1         |
| s2         |
| s3         |
| null       |
| s6         |
| s7         |
+------------+

      • 例 2: この例では、FROM 句のサブクエリがテーブルとして使用され、サブクエリは他のテーブルまたはサブクエリと joined されます。サンプル文:

        -- テーブルを作成し、テーブルをサブクエリと結合します。
create table shop as select shop_name,customer_id,total_price 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;

        次の結果が返されます。

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| null       | c5          | NULL        |
| s6         | c6          | 100.4       |
| s7         | c7          | 100.5       |
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

    IN サブクエリ

    IN サブクエリ は、LEFT SEMI JOIN と同様の方法で使用されます。

    • 構文

      • 構文 1

        select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>);
-- 前述の文は、LEFT SEMI JOIN を使用した次の文と同等です。
select<select_expr1>from<table_name1><alias_name1>leftsemijoin<table_name2><alias_name2>on<alias_name1>.<select_expr2>=<alias_name2>.<select_expr3>;
        説明

        select_expr2 がパーティションキー列を指定する場合、select <select_expr2> from <table_name2> は LEFT SEMI JOIN に変換されません。サブクエリを実行するために別のジョブが開始されます。MaxCompute は、サブクエリの結果を select_expr2 で指定した列と順番に比較します。table_name1 で指定されたテーブルのパーティションに select_expr2 の列が含まれていて、これらの列が結果に含まれていない場合、MaxCompute はこれらのパーティションからデータを読み取りません。これにより、パーティションプルーニングが引き続き有効になります。

      • 構文 2

        MaxCompute は、IN サブクエリ と相関条件をサポートしています。where <table_name2_colname> = <table_name1>.<colname> は相関条件です。MaxCompute V1.0 は、サブクエリと主クエリの両方からソーステーブルを参照する式をサポートしていません。MaxCompute V2.0 は、このような式をサポートしています。これらのフィルター条件は、ON 条件の一部です SEMI JOIN 操作。

        select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>where
<table_name1>.<col_name>=<table_name2>.<col_name>);
        説明

        MaxCompute は、JOIN 条件として機能しない IN サブクエリ をサポートしています。たとえば、WHERE 以外 の句で IN サブクエリを使用したり、WHERE 句で JOIN 条件に変換できない IN サブクエリを使用したりします。この場合、IN サブクエリは SEMI JOIN に変換できません。サブクエリを実行するために別のジョブを開始する必要があります。相関条件はサポートされていません。

      • 構文 3

        IN サブクエリは、前述の機能と制限に基づいて複数列のサブクエリをサポートしています。このルールは PostgreSQL にも適用されます。IN サブクエリに構文 3 を使用する場合、クエリをサブクエリに分割する必要はありません。複数列のサブクエリは、JOIN 操作を 1 つ減らし、計算リソースを節約します。複数列のサブクエリは、次の方法で使用できます。

        • IN サブクエリ 式に複数の列を指定する単純な SELECT 文を使用します。

        • IN サブクエリ 式に集計関数を使用します。集計関数の詳細については、「集計関数」をご参照ください。

        • IN サブクエリ 式に定数を使用します。

    • パラメーター

      • select_expr1: 必須。このパラメーターの値は、col1_name, col2_name, 正規表現,... の形式です。このパラメーターは、クエリする一般的な列またはパーティションキー列、あるいはクエリに使用される正規表現を指定します。

      • table_name1 および table_name2: 必須。パラメーターはテーブルの名前を指定します。

      • select_expr2 および select_expr3: 必須。パラメーターは、table_name1 および table_name2 で指定されたテーブルの列の名前を指定します。2 つのテーブルの列は相互にマッピングされます。

      • col_name: 必須。このパラメーターは、テーブルの列の名前を指定します。

    • 使用上の注意

      IN サブクエリ 式を使用する場合、null 値はサブクエリの返される結果から自動的に除外されます。

    • 例

      • 例 1: 構文 1 を使用します。サンプル文:

        set odps.sql.allow.fullscan=true;
select * from sale_detail where total_price in (select total_price from shop);

        次の結果が返されます。

        +-----------+-------------+-------------+-----------+--------+
| 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  |
| s6        | c6          | 100.4       | 2014      | shanghai |
| s7        | c7          | 100.5       | 2014      | shanghai |
+-----------+-------------+-------------+-----------+--------+

      • 例 2: 構文 2 を使用します。サンプル文:

        set odps.sql.allow.fullscan=true;
select * from sale_detail where total_price in (select total_price from shop where customer_id = shop.customer_id);

        次の結果が返されます。

        +-----------+-------------+-------------+-----------+--------+
| 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  |
| s6        | c6          | 100.4       | 2014      | shanghai |
| s7        | c7          | 100.5       | 2014      | shanghai |
+-----------+-------------+-------------+-----------+--------+

      • 例 3: サブクエリの SELECT 文に複数の列が指定されています。サンプル文:

        -- この例を理解するために、サンプルデータを再構築します。
create table if not exists t1(a bigint,b bigint,c bigint,d bigint,e bigint);
create table if not exists t2(a bigint,b bigint,c bigint,d bigint,e bigint);
insert into table t1 values (1,3,2,1,1),(2,2,1,3,1),(3,1,1,1,1),(2,1,1,0,1),(1,1,1,0,1);
insert into table t2 values (1,3,5,0,1),(2,2,3,1,1),(3,1,1,0,1),(2,1,1,0,1),(1,1,1,0,1);
-- シナリオ 1: IN サブクエリ式は、複数の列を指定する単純な SELECT 文です。
select a, b from t1 where (c, d) in (select a, b from t2 where e = t1.e);
-- 次の結果が返されます。
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 2          |
| 3          | 1          |
+------------+------------+
-- シナリオ 2: IN サブクエリ式は集計関数を使用します。
select a, b from t1 where (c, d) in (select max(a), b from t2 where e = t1.e group by b having max(a) > 0);
-- 次の結果が返されます。
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
+------------+------------+
-- シナリオ 3: IN サブクエリ式は定数を使用します。
select a, b from t1 where (c, d) in ((1, 3), (1, 1));
-- 次の結果が返されます。
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
| 3          | 1          |
+------------+------------+

    NOT IN サブクエリ

    NOT IN サブクエリ は、LEFT ANTI JOIN と同様の方法で使用されます。ただし、クエリするテーブルの指定された列の行の値が NULL の場合、NOT IN サブクエリ の式の値は NULL になります。この場合、WHERE 条件は無効になり、データは返されません。この処理ロジックは、LEFT ANTI JOIN の処理ロジックとは異なります。

    • 構文

      • 構文 1

        select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2>);
-- 前述の文は、LEFT ANTI JOIN を使用した次の文と同等です。
select <select_expr1> from <table_name1> <alias_name1> left anti join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;
        説明

        select_expr2 がパーティションキー列を指定する場合、select <select_expr2> from <table_name2> は LEFT ANTI JOIN に変換されません。サブクエリを実行するために別のジョブが開始されます。MaxCompute は、サブクエリの結果を select_expr2 で指定された列と順番に比較します。tabletable_name1 で指定されたテーブルのパーティションに select_expr2 の列が含まれていて、これらの列が結果に含まれていない場合、MaxCompute はこれらのパーティションからデータを読み取りません。これにより、パーティションプルーニングが引き続き有効になります。

      • 構文 2

        MaxCompute は、NOT IN サブクエリ と相関条件をサポートしています。where <table_name2_colname> = <table_name1>.<colname> は相関条件です。MaxCompute V1.0 は、サブクエリと主クエリの両方からソーステーブルを参照する式をサポートしていません。MaxCompute V2.0 は、このような式をサポートしています。これらの式は、ON 条件の一部です ANTI JOIN 操作。

        select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
        説明

        MaxCompute は、JOIN 条件として機能しない NOT IN サブクエリ をサポートしています。たとえば、WHERE 以外 の句で NOT IN サブクエリを使用したり、WHERE 句で JOIN 条件に変換できない NOT IN サブクエリを使用したりします。この場合、NOT IN サブクエリは ANTI JOIN に変換できません。サブクエリを実行するために別のジョブを開始する必要があります。相関条件はサポートされていません。

      • 構文 3

        NOT IN サブクエリは、前述の機能と制限に基づいて複数列のサブクエリをサポートしています。このルールは PostgreSQL にも適用されます。NOT IN サブクエリに構文 3 を使用する場合、クエリを複数のサブクエリに分割する必要はありません。複数列のサブクエリは、JOIN 操作を 1 つ減らし、計算リソースを節約します。複数列のサブクエリは、次の方法で使用できます。

        • NOT IN サブクエリ 式に複数の列を指定する単純な SELECT 文を使用します。

        • NOT IN サブクエリ 式に集計関数を使用します。集計関数の詳細については、「集計関数」をご参照ください。

        • NOT IN サブクエリ 式に定数を使用します。

    • パラメーター

      • select_expr1: 必須。このパラメーターの値は、col1_name, col2_name, 正規表現,... の形式です。このパラメーターは、クエリする一般的な列またはパーティションキー列、あるいはクエリに使用される正規表現を指定します。

      • table_name1 および table_name2: 必須。パラメーターはテーブルの名前を指定します。

      • select_expr2 および select_expr3: 必須。パラメーターは、table_name1 および table_name2 で指定されたテーブルの列の名前を指定します。2 つのテーブルの列は相互にマッピングされます。

      • col_name: 必須。このパラメーターは、テーブルの列の名前を指定します。

    • 使用上の注意

      NOT IN サブクエリ 式を使用する場合、null 値はサブクエリの返される結果から自動的に除外されます。

    • 例

      • 例 1: 構文 1 を使用します。サンプル文:

        -- shop1 という名前のテーブルを作成し、テーブルにデータを挿入します。
create table shop1 as select shop_name,customer_id,total_price from sale_detail;
insert into shop1 values ('s8','c1',100.1);

select * from shop1 where shop_name not in (select shop_name from sale_detail);

        次の結果が返されます。

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s8         | c1          | 100.1       |
+------------+-------------+-------------+

      • 例 2: 構文 2 を使用します。サンプル文:

        set odps.sql.allow.fullscan=true;
select * from shop1 where shop_name not in (select shop_name from sale_detail where customer_id = shop1.customer_id);

        次の結果が返されます。

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s8         | c1          | 100.1       |
+------------+-------------+-------------+

      • 例 3: NOT IN サブクエリ は JOIN 条件として機能しません。サンプル文:

        set odps.sql.allow.fullscan=true;
select * from shop1 where shop_name not in (select shop_name from sale_detail) and total_price < 100.3;

        NOT IN サブクエリは ANTI JOIN に変換できません。これは、WHERE 句に AND 演算子が含まれているためです。サブクエリを実行するために別のジョブが開始されます。

        次の結果が返されます。

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s8         | c1          | 100.1       |
+------------+-------------+-------------+

      • 例 4: クエリするテーブルの行の値が NULL の場合、データは返されません。サンプル文:

        -- sale という名前のテーブルを作成し、テーブルにデータを挿入します。
create table if not exists sale
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string, region string);
alter table sale add partition (sale_date='2013', region='china');
insert into sale partition (sale_date='2013', region='china') values ('null','null',null),('s2','c2',100.2),('s3','c3',100.3),('s8','c8',100.8);

set odps.sql.allow.fullscan=true;
select * from sale where shop_name not in (select shop_name from sale_detail);

        次の結果が返されます。

        +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
+------------+-------------+-------------+------------+------------+

      • 例 5: サブクエリの SELECT 文に複数の列が指定されています。サンプル文:

        -- この例を理解するために、サンプルデータを再構築します。サンプルデータは IN サブクエリと同じです。
create table if not exists t1(a bigint,b bigint,c bigint,d bigint,e bigint);
create table if not exists t2(a bigint,b bigint,c bigint,d bigint,e bigint);
insert into table t1 values (1,3,2,1,1),(2,2,1,3,1),(3,1,1,1,1),(2,1,1,0,1),(1,1,1,0,1);
insert into table t2 values (1,3,5,0,1),(2,2,3,1,1),(3,1,1,0,1),(2,1,1,0,1),(1,1,1,0,1);
-- シナリオ 1: NOT IN サブクエリ式は、複数の列を指定する単純な SELECT 文です。
select a, b from t1 where (c, d) not in (select a, b from t2 where e = t1.e);
-- 次の結果が返されます。
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 1          |
| 1          | 1          |
+------------+------------+
-- シナリオ 2: NOT IN サブクエリ式は集計関数を使用します。
select a, b from t1 where (c, d) not in (select max(a), b from t2 where e = t1.e group by b having max(a) > 0);
-- 次の結果が返されます。
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 3          | 1          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+
-- シナリオ 3: NOT IN サブクエリ式は定数を使用します。
select a, b from t1 where (c, d) not in ((1, 3), (1, 1));
-- 次の結果が返されます。
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+

    EXISTS サブクエリ

    EXISTS サブクエリ 句を使用する場合、サブクエリが少なくとも 1 行のデータを返す場合は True が返されます。サブクエリがデータを返さない場合は、False が返されます。

    MaxCompute は、相関条件を持つ WHERE サブクエリのみをサポートしています。EXISTS サブクエリ 句を使用するには、この句を LEFT SEMI JOIN に変換する必要があります。

    • 構文

      select <select_expr> from <table_name1> where exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);

    • パラメーター

      • select_expr: 必須。このパラメーターの値は、col1_name, col2_name, 正規表現,... の形式です。この形式は、クエリする一般的な列またはパーティションキー列、あるいはクエリに使用される正規表現を示します。

      • table_name1 および table_name2: 必須。パラメーターはテーブルの名前を指定します。

      • col_name: 必須。このパラメーターは、テーブルの列の名前を指定します。

    • 例

      set odps.sql.allow.fullscan=true;
select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id);
-- 前述の文は、次の文と同等です。
select * from sale_detail a left semi join shop b on a.customer_id = b.customer_id;

      次の結果が返されます。

      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

    NOT EXISTS サブクエリ

    NOT EXISTS サブクエリ 句を使用する場合、データが返されない場合は True が返されます。それ以外の場合は、False が返されます。

    MaxCompute は、相関条件を持つ WHERE サブクエリのみをサポートしています。NOT EXISTS サブクエリ 句を使用するには、この句を LEFT ANTI JOIN に変換する必要があります。

    • 構文

      select <select_expr> from <table_name1> where not exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);

    • パラメーター

      • select_expr: 必須。このパラメーターの値は、col1_name, col2_name, 正規表現,... の形式です。この形式は、クエリする一般的な列またはパーティションキー列、あるいはクエリに使用される正規表現を示します。

      • table_name1 および table_name2: 必須。パラメーターはテーブルの名前を指定します。

      • col_name: 必須。このパラメーターは、テーブルの列の名前を指定します。

    • 例

      set odps.sql.allow.fullscan=true;
select * from sale_detail where not exists (select * from shop where shop_name = sale_detail.shop_name);
-- 前述の文は、次の文と同等です。
select * from sale_detail a left anti join shop b on a.shop_name = b.shop_name;

      次の結果が返されます。

      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
+------------+-------------+-------------+------------+------------+

    スカラサブクエリ

    スカラサブクエリ句の出力結果に 1 行 1 列のデータのみが含まれている場合、その結果はデータ計算のスカラーとして使用できます。スカラサブクエリの出力結果に 1 行のデータのみが含まれていて、1 つの MAX または MIN 演算子がスカラサブクエリの外側にネストされている場合、結果は変わりません。スカラサブクエリは複数列の使用もサポートしています。たとえば、SELECT 句には複数列のスカラサブクエリ式を含めることができ、等値式のみをサポートします。SELECT 句はブール式にすることができ、等値比較のみをサポートします。WHERE 句は複数列の比較をサポートし、等値比較のみをサポートします。

    • 構文

      • 構文 1:

        select <select_expr> from <table_name1> where (<select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>) <スカラー演算子> <scalar_value>;
-- 前述の文は、次の文と同等です。
select <table_name1>.<select_expr> from <table_name1> left semi join (select <colname>, count(*) from <table_name2> group by <colname> having count(*) <スカラー演算子> <scalar_value>) <table_name2> on <table_name1>.<colname> = <table_name2>.<colname>;
        説明

        • select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname> の出力結果は行セットです。出力には、1 行 1 列のデータのみが含まれています。この場合、結果はスカラーとして使用できます。実際のアプリケーションでは、スカラサブクエリは可能な限り JOIN に変換されます。

        • スカラサブクエリの出力結果は、スカラサブクエリが 1 行 1 列のデータのみを返すことをコンパイルフェーズで確認できる場合にのみ、スカラーとして使用できます。実行フェーズまで確認できない場合、コンパイラはエラーを報告します。コンパイラは、次の要件を満たす文をコンパイルできます。

          • スカラサブクエリの SELECT リストは、指定されたユーザー定義テーブル値関数 (UDTF) のパラメーターに含まれていない集計関数を使用します。

          • 集計関数を使用するスカラサブクエリには、GROUP BY 句が含まれていません。

      • 構文 2:

        select (<select_statement>) from <table_name>;

    • パラメーター

      • select_expr: 必須。このパラメーターの値は、col1_name, col2_name, 正規表現,... の形式です。この形式は、クエリする一般的な列またはパーティションキー列、あるいはクエリに使用される正規表現を示します。

      • table_name1 および table_name2: 必須。パラメーターはテーブルの名前を指定します。

      • col_name: 必須。このパラメーターは、テーブルの列の名前を指定します。

      • スカラー演算子: 必須。スカラー演算子は、大なり記号 (>)、小なり記号 (<)、等号 (=)、大なりイコール (>=)、小なりイコール (<=) です。

      • scalar_value: 必須。このパラメーターはスカラー値を指定します。

      • select_statement: 必須。このパラメーターはサブクエリ文を指定します。サブクエリ文が構文 2 に従っている場合、サブクエリの結果は 1 行のみを含む必要があります。構文の詳細については、「SELECT 構文」をご参照ください。

    • 制限

      • スカラサブクエリ は主クエリからの列を参照できます。スカラサブクエリ が複数レベルのネストを使用する場合、参照できるのは一番外側の列のみです。

        -- 実行できるサンプル文:
select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3;
-- 実行できないサンプル文。これは、主クエリからの列をサブクエリの SELECT 文で参照できないためです。
select * from t1 where (select count(*) from t2 where (select count(*) from t3 where t3.a = t1.a) = 2) = 3;

    • 例

      • 例1: 一般的な使用法。サンプルステートメント

        set odps.sql.allow.fullscan=true;
select * from shop where (select count(*) from sale_detail where sale_detail.shop_name = shop.shop_name) >= 1;

        例 1: 一般的な使用方法。サンプル文:

        +------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
| null       | c5          | NULL        |
| s6         | c6          | 100.4       |
| s7         | c7          | 100.5       |
+------------+-------------+-------------+

      • 例 2: サブクエリに対して SELECT 文で複数の列が指定されています。サンプルステートメント:

        -- サンプルデータは、この例を理解しやすいように再構成されています。
create table if not exists ts(a bigint,b bigint,c double);
create table if not exists t(a bigint,b bigint,c double);
insert into table ts values (1,3,4.0),(1,3,3.0);
insert into table t values (1,3,4.0),(1,3,5.0);
-- シナリオ 1: SELECT 文に、複数の列が指定されているスカラサブクエリ式が含まれています。式は等価式である必要があります。実行できないサンプルステートメント: select (select a, b from t where c > ts.c) as (a, b), a from ts;
select (select a, b from t where c = ts.c) as (a, b), a from ts;
-- 次の結果が返されます。
+------------+------------+------------+
| a          | b          | a2         |
+------------+------------+------------+
| 1          | 3          | 1          |
| NULL       | NULL       | 1          |
+------------+------------+------------+
-- シナリオ 2: SELECT 文に BOOLEAN 型の式が含まれています。等価比較のみがサポートされています。実行できないサンプルステートメント: select (a,b) > (select a,b from ts where c = t.c) from t;
select (a,b) = (select a,b from ts where c = t.c) from t;
-- 次の結果が返されます。
+------+
| _c0  |
+------+
| true |
| false |
+------+
-- シナリオ 3: WHERE 句は複数列の比較をサポートしています。等価比較のみがサポートされています。実行できないサンプルステートメント: select * from t where (a,b) > (select a,b from ts where c = t.c);
select * from t where c > 3.0 and (a,b) = (select a,b from ts where c = t.c);
-- 次の結果が返されます。
+------------+------------+------------+
| a          | b          | c          |
+------------+------------+------------+
| 1          | 3          | 4.0        |
+------------+------------+------------+
select * from t where c > 3.0 or (a,b) = (select a,b from ts where c = t.c);
-- 次の結果が返されます。
+------------+------------+------------+
| a          | b          | c          |
+------------+------------+------------+
| 1          | 3          | 4.0        |
| 1          | 3          | 5.0        |
+------------+------------+------------+

      • 例3: 構文2のサブクエリ構文を使用する。サンプルステートメント:

        set odps.sql.allow.fullscan=true; // フルスキャンを許可する設定
select (select * from sale_detail where shop_name='s1') from sale_detail; // sale_detail テーブルから shop_name が 's1' のデータを抽出

        以下の結果が返されます:

        +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

    参照資料

    多数のサブクエリ、またはサブクエリを不適切に使用すると、特にビッグデータ環境ではクエリが遅くなる可能性があります。一時テーブルまたはマテリアライズドビューをサブクエリの代わりに使用するか、複数のサブクエリを JOIN 操作に再構築して、クエリの効率を向上させることができます。詳細については、「マテリアライズドビューの推奨事項と管理」および「JOIN」をご参照ください。