すべてのプロダクト
Search

このプロダクト

    MaxCompute:サブクエリ

    ドキュメントセンター

    MaxCompute:サブクエリ

    最終更新日:Dec 09, 2024

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

    説明

    サブクエリは、複雑なデータクエリを実行するためにステートメント内にネストされます。 MaxComputeは、次のタイプのサブクエリをサポートしています。

    • 基本サブクエリ

      FROM句のサブクエリは、クエリでの複雑なコンピューティングまたはデータ変換の一時テーブルとして使用されます。

    • IN SUBQUERY

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

    • NOT IN SUBQUERY

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

    • EXISTS SUBQUERY

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

    • NOT EXISTS SUBQUERY

      NOT EXISTSサブクエリは、EXISTSサブクエリとは逆に機能します。 メインクエリのレコードは、サブクエリが行を返さない場合にのみ選択されます。 サブクエリで一致しない行をクエリする場合は、NOT EXISTSサブクエリが適用されます。

    • SCALAR SUBQUERY

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

    説明

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

    サンプルデータ

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

    -- Create a partitioned table named 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);

-- Add partitions to the sale_detail table. 
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');

-- Insert data into the sale_detail table. 
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; 
-- 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ステートメントをクエリオブジェクトとして使用することもできます。 この場合、SELECTステートメントはサブクエリと見なされます。 FROM句のサブクエリは、テーブルとして使用できます。 サブクエリを他のテーブルまたはサブクエリと結合できます。 JOIN操作の詳細については、「JOIN」をご参照ください。

    • 構文

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

    • パラメーター

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

      • 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句のサブクエリをテーブルとして使用し、そのサブクエリを他のテーブルまたはサブクエリと結合します。 サンプル文: 

        -- Create a table and join the table with a subquery. 
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 SUBQUERY

    IN SUBQUERYは、LEFT SEMI JOINと同様に使用されます。

    • 構文

      • 構文1

        select<select_expr1>from<table_name1>where<select_expr2>in(select<select_expr3>from<table_name2>);
-- The preceding statement is equivalent to the following statement with 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 SUBQUERYと相関条件をサポートしています。 where <table_name2_colname> = <table_name1>.<colname> は相関条件です。 MaxCompute V1.0は、サブクエリとメインクエリの両方からソーステーブルを参照する式をサポートしていません。 MaxCompute V2.0はこのような式をサポートします。 これらのフィルター条件は、SEMI JOIN操作のON条件の一部です。 

        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 SUBQUERYをサポートしています。 たとえば、non-WHERE句はIN SUBQUERYを使用し、WHERE句はJOIN条件に変換できないIN SUBQUERYを使用します。 この場合、In SUBQUERYはSEMI JOINに変換できません。 サブクエリを実行するには、別のジョブを開始する必要があります。 相関条件はサポートされていません。

      • 構文3

        INサブクエリは、上記の機能と制限に基づいて複数列のサブクエリをサポートします。 このルールはPostgreSQLにも適用されます。 INサブクエリにSyntax 3を使用する場合、クエリをサブクエリに分割する必要はありません。 複数列サブクエリは、1つのJOIN操作を削減し、コンピューティングリソースを節約します。 複数列サブクエリは、次の方法で使用できます。

        • in SUBQUERY式に複数の列を指定する単純なSELECTステートメントを使用します。

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

        • IN SUBQUERY式に定数を使用します。

    • パラメーター

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

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

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

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

    • 使用上の注意

      IN SUBQUERY式を使用すると、サブクエリの返された結果から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ステートメントで複数の列を指定します。 サンプル文: 

        -- Sample data is reconstructed to help you understand this example. 
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);
-- Scenario 1: The IN SUBQUERY expression is a simple SELECT statement in which you specify multiple columns. 
select a, b from t1 where (c, d) in (select a, b from t2 where e = t1.e);
-- The following result is returned: 
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 2          |
| 3          | 1          |
+------------+------------+
-- Scenario 2: The IN SUBQUERY expression uses aggregate functions. 
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);
-- The following result is returned: 
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
+------------+------------+
-- Scenario 3: The IN SUBQUERY expression uses constants. 
select a, b from t1 where (c, d) in ((1, 3), (1, 1));
-- The following result is returned: 
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
| 3          | 1          |
+------------+------------+

    NOT IN SUBQUERY

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

    • 構文

      • 構文1

        select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2>);
-- The preceding statement is equivalent to the following statement with 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で指定された列と順番に比較します。 table_name1で指定されたテーブルのパーティションにselect_expr2の列が含まれていて、これらの列が結果に含まれていない場合、MaxComputeはこれらのパーティションからデータを読み取りません。 これにより、パーティションプルーニングが引き続き有効になります。

      • 構文2

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

        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 SUBQUERYをサポートしています。 たとえば、non-WHERE句はNOT IN SUBQUERYを使用し、WHERE句はNOT IN SUBQUERYを使用し、JOIN条件に変換できません。 この場合、NOT In SUBQUERYはANTI JOINに変換できません。 サブクエリを実行するには、別のジョブを開始する必要があります。 相関条件はサポートされていません。

      • 構文3

        NOT INサブクエリは、上記の機能と制限に基づいて複数列のサブクエリをサポートします。 このルールはPostgreSQLにも適用されます。 NOT INサブクエリにSyntax 3を使用する場合、クエリを複数のサブクエリに分割する必要はありません。 複数列サブクエリは、1つのJOIN操作を削減し、コンピューティングリソースを節約します。 複数列サブクエリは、次の方法で使用できます。

        • NOT in SUBQUERY式に複数の列を指定する単純なSELECTステートメントを使用します。

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

        • NOT IN SUBQUERY式に定数を使用します。

    • パラメーター

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

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

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

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

    • 使用上の注意

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

    • 例

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

        -- Create a table named shop1 and insert data into the table. 
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 SUBQUERYは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の場合、データは返されません。 サンプル文: 

        -- Create a table named sale and insert data into the table. 
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ステートメントで複数の列を指定します。 サンプル文: 

        -- Sample data is reconstructed to help you understand this example. The sample data is the same as that of IN SUBQUERY. 
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);
-- Scenario 1: The NOT IN SUBQUERY expression is a simple SELECT statement in which you specify multiple columns. 
select a, b from t1 where (c, d) not in (select a, b from t2 where e = t1.e);
-- The following result is returned: 
+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 1          |
| 1          | 1          |
+------------+------------+
-- Scenario 2: The NOT IN SUBQUERY expression uses aggregate functions. 
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);
-- The following result is returned: 
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 3          | 1          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+
-- Scenario 3: The NOT IN SUBQUERY expression uses constants. 
select a, b from t1 where (c, d) not in ((1, 3), (1, 1));
-- The following result is returned: 
+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+

    EXISTS SUBQUERY

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

    MaxComputeは、条件が関連付けられているWHEREサブクエリのみをサポートします。 EXISTS SUBQUERY句を使用するには、この句を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, Regular expression,... の形式です。 この形式は、クエリする一般的な列またはパーティションキーの列、またはクエリに使用される正規表現を示します。

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

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

    • 使用上の注意

      EXISTS SUBQUERY句を使用すると、サブクエリの返された結果からnull値が自動的に除外されます。

    • 例

      set odps.sql.allow.fullscan=true;
select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id);
-- The preceding statement is equivalent to the following statement: 
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 SUBQUERY

    NOT EXISTS SUBQUERY句を使用する場合、データが返されない場合はTrueが返されます。 それ以外の場合は、falseが返されます。

    MaxComputeは、条件が関連付けられているWHEREサブクエリのみをサポートします。 NOT EXISTS SUBQUERY句を使用するには、この句を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, Regular expression,... の形式です。 この形式は、クエリする一般的な列またはパーティションキーの列、またはクエリに使用される正規表現を示します。

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

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

    • 使用上の注意

      NOT EXISTS SUBQUERY句を使用すると、サブクエリの返された結果からnull値が自動的に除外されます。

    • 例

      set odps.sql.allow.fullscan=true;
select * from sale_detail where not exists (select * from shop where shop_name = sale_detail.shop_name);
-- The preceding statement is equivalent to the following statement: 
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     |
+------------+-------------+-------------+------------+------------+
+------------+-------------+-------------+------------+------------+

    SCALAR SUBQUERY

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

    • 構文

      • 構文1:

        select <select_expr> from <table_name1> where (<select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname>) <Scalar operator> <scalar_value>;
-- The preceding statement is equivalent to the following statement: 
select <table_name1>.<select_expr> from <table_name1> left semi join (select <colname>, count(*) from <table_name2> group by <colname> having count(*) <Scalar operator> <scalar_value>) <table_name2> on <table_name1>.<colname> = <table_name2>.<colname>;
        説明

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

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

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

          • 集計関数を使用するSCALAR SUBQUERYには、GROUP BY句は含まれません。

      • 構文2:

        select (<select_statement>) from <table_name>;

    • パラメーター

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

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

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

      • スカラー演算子: 必須。 スカラー演算子は、より大きい (>) 、より小さい (<) 、等しい (=) 、より大きいか等しい (>=) 、またはより小さいか等しい (<=) とすることができる。

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

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

    • 制限事項

      • SCALAR SUBQUERYは、メインクエリから列を参照できます。 SCALAR SUBQUERYが複数レベルのネスティングを使用する場合、最も外側の列のみを参照できます。 

        -- Sample statement that you can execute: 
select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3; 
-- Sample statement that you cannot execute. This is because columns from main queries cannot be referenced in a SELECT statement for a subquery. 
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;

        次の応答が返されます。

        +------------+-------------+-------------+
| 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ステートメントで複数の列を指定します。 サンプル文: 

        -- Sample data is reconstructed to help you understand this example. 
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);
-- Scenario 1: The SELECT statement contains a scalar subquery expression in which multiple columns are specified. The expression must be an equality expression. Sample statements that you cannot execute: 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;
-- The following result is returned: 
+------------+------------+------------+
| a          | b          | a2         |
+------------+------------+------------+
| 1          | 3          | 1          |
| NULL       | NULL       | 1          |
+------------+------------+------------+
-- Scenario 2: The SELECT statement contains an expression of the BOOLEAN type. Only equivalent comparison is supported. Sample statements that you cannot execute: 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;
-- The following result is returned: 
+------+
| _c0  |
+------+
| true |
| false |
+------+
-- Scenario 3: A WHERE clause supports multi-column comparison. Only equivalent comparison is supported. Sample statements that you cannot execute: 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);
-- The following result is returned: 
+------------+------------+------------+
| 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);
-- The following result is returned: 
+------------+------------+------------+
| 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;

        次の応答が返されます。

        +------------+-------------+-------------+------------+------------+
| 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」をご参照ください。