You can use a subquery if you want to perform a further query based on the query results of a SELECT statement. This topic describes the definition and usage of subqueries that are supported in MaxCompute.

Description

Subqueries are nested inside a SELECT statement to perform complex data queries. MaxCompute supports the following types of subqueries:

Sample data

This topic provides source data and sample statements for generating source data. This helps you understand how to prepare source data. Sample statements:
-- 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);

Basic subquery

Tables are objects for common queries. You can also use a SELECT statement that is nested inside another SELECT statement to specify an object for a subquery. A subquery in a FROM clause can be used as a table. You can join the subquery with other tables or subqueries. For more information about JOIN operations, see JOIN.
  • Syntax
    • Syntax 1
      select <select_expr> from (<select_statement>) [<sq_alias_name>];
    • Syntax 2
      select (<select_statement>) from <table_name>;
  • Parameters
    • select_expr: required. The value of this parameter is in the format of col1_name, col2_name, Regular expression,.... This format indicates common columns or partition key columns that you want to query or regular expressions that are used for a query.
    • select_statement: required. This parameter specifies a subquery clause. If you use Syntax 2, the subquery result can have only one row. For more information about the syntax, see SELECT syntax.
    • sq_alias_name: optional. This parameter specifies the alias of a subquery.
    • table_name: required. This parameter specifies the name of the table that you want to query.
  • Examples
    • Example 1: Use Syntax 1. The following statement shows an example:
      select * from (select shop_name from sale_detail) a;
      The following result is returned:
      +------------+
      | shop_name  |
      +------------+
      | s1         |
      | s2         |
      | s3         |
      | null       |
      | s6         |
      | s7         |
      +------------+
    • Example 2: Use Syntax 2. The following statement shows an example:
      select (select * from sale_detail where shop_name='s1') from sale_detail;
      The following result is returned:
      +------------+-------------+-------------+------------+------------+
      | 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      |
      +------------+-------------+-------------+------------+------------+
    • Example 3: Use Syntax 1. In this example, a subquery in a FROM clause is used as a table, and the subquery is joined with other tables or subqueries. The following statements show an example:
      -- 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;
      The following result is returned:
      +------------+-------------+-------------+
      | 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 is used in a similar manner to LEFT SEMI JOIN.

  • Syntax
    • Syntax 1
      select <select_expr1> from <table_name1> where <select_expr2> in (select <select_expr2> from <table_name2>);
      -- The preceding statement is equivalent to the following statement with LEFT SEMI JOIN: 
      select <select_expr1> from <table_name1> <alias_name1> left semi join <table_name2> <alias_name2> on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;
      Note If select_expr2 specifies partition key columns, select <select_expr2> from <table_name2> is not converted into LEFT SEMI JOIN. A separate job is started to run a subquery. MaxCompute compares the subquery results with the columns that you specify in select_expr2 in sequence. If the partitions of the table specified by table_name1 contain the columns in select_expr2 and these columns are not included in the results, MaxCompute does not read data from these partitions. This ensures that partition pruning is still valid.
    • Syntax 2
      MaxCompute supports IN SUBQUERY and correlated conditions. where <table_name2_colname> = <table_name1>.<colname> is a correlated condition. MaxCompute V1.0 does not support expressions that reference source tables from both subqueries and outer queries. MaxCompute V2.0 supports such expressions. These expressions are part of the ON condition in SEMI JOIN operations.
      select <select_expr1> from <table_name1> where <select_expr2> in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
      Note MaxCompute supports IN SUBQUERY that does not serve as a JOIN condition. For example, a non-WHERE clause uses IN SUBQUERY, or a WHERE clause uses IN SUBQUERY that cannot be converted into a JOIN condition. In this case, IN SUBQUERY cannot be converted into SEMI JOIN. A separate job must be started to run a subquery. Correlated conditions are not supported.
    • Syntax 3
      IN SUBQUERY supports multi-column subqueries based on the preceding capabilities and limits. This rule also applies to PostgreSQL. If you use Syntax 3 for IN SUBQUERY, you do not need to split a query into subqueries. Multi-column subqueries reduce one JOIN operation and save computing resources. You can use multi-column subqueries in the following ways:
      • Use a simple SELECT statement in which you specify multiple columns for the IN SUBQUERY expression.
      • Use aggregate functions for the IN SUBQUERY expression. For more information about aggregate functions, see Aggregate functions.
      • Use constants for the IN SUBQUERY expression.
  • Parameters
    • select_expr1: required. The value of this parameter is in the format of col1_name, col2_name, Regular expression,.... This format indicates common columns or partition key columns that you want to query or regular expressions that are used for a query.
    • table_name1 and table_name2: required. These parameters specify the names of tables.
    • select_expr2: required. This parameter specifies the names of the columns in the tables specified by table_name1 and table_name2. Columns in the two tables are mapped to each other.
    • col_name: required. This parameter specifies the name of a column in the table.
  • Examples
    • Example 1: Use Syntax 1. The following statement shows an example:
      select * from sale_detail where shop_name in (select shop_name from shop);
      The following result is returned:
      +------------+-------------+-------------+------------+------------+
      | 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      |
      +------------+-------------+-------------+------------+------------+
    • Example 2: Use Syntax 2. The following statement shows an example:
      select * from sale_detail where shop_name in (select shop_name from shop where customer_id = shop.customer_id);
      The following result is returned:
      +-----------+-------------+-------------+-----------+--------+
      | 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  |
      +-----------+-------------+-------------+-----------+--------+
    • Example 3: IN SUBQUERY does not serve as a JOIN condition. The following statement shows an example:
      select * from sale_detail where shop_name in (select shop_name from shop) and total_price > 100.3;

      IN SUBQUERY cannot be converted into SEMI JOIN. This is because the WHERE clause includes an AND operator. A separate job is started to run the subquery.

      The following result is returned:
      +------------+-------------+-------------+------------+------------+
      | shop_name  | customer_id | total_price | sale_date  | region     |
      +------------+-------------+-------------+------------+------------+
      | s6         | c6          | 100.4       | 2014       | shanghai   |
      | s7         | c7          | 100.5       | 2014       | shanghai   |
      +------------+-------------+-------------+------------+------------+
    • Example 4: Multiple columns are specified in a SELECT statement for a subquery. The following statements show an example:
      -- 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 is used in a similar manner to LEFT ANTI JOIN. However, if the values of a row are NULL for a specified column in the table that you want to query, the value of the expression in NOT IN SUBQUERY is NULL. In this case, the WHERE condition is invalid, and no data is returned. This processing logic is different from that of LEFT ANTI JOIN.

  • Syntax
    • Syntax 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>;
      Note If select_expr2 specifies partition key columns, select <select_expr2> from <table_name2> is not converted into LEFT ANTI JOIN. A separate job is started to run a subquery. MaxCompute compares the subquery results with the columns specified in select_expr2 in sequence. If the partitions of the table specified by table_name1 contain the columns in select_expr2 and these columns are not included in the results, MaxCompute does not read data from these partitions. This ensures that partition pruning is still valid.
    • Syntax 2
      MaxCompute supports NOT IN SUBQUERY and correlated conditions. where <table_name2_colname> = <table_name1>.<colname> in a subquery is a correlated condition. MaxCompute V1.0 does not support expressions that reference source tables from both subqueries and outer queries. MaxCompute V2.0 supports such expressions. These expressions are part of the ON condition in ANTI JOIN operations.
      select <select_expr1> from <table_name1> where <select_expr2> not in (select <select_expr2> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
      Note MaxCompute supports NOT IN SUBQUERY that does not serve as a JOIN condition. For example, a non-WHERE clause uses NOT IN SUBQUERY, or a WHERE clause uses NOT IN SUBQUERY that cannot be converted into a JOIN condition. In this case, NOT IN SUBQUERY cannot be converted into ANTI JOIN. A separate job must be started to run a subquery. Correlated conditions are not supported.
    • Syntax 3
      NOT IN SUBQUERY supports multi-column subqueries based on the preceding capabilities and limits. This rule also applies to PostgreSQL. If you use Syntax 3 for NOT IN SUBQUERY, you do not need to split a query into multiple subqueries. Multi-column subqueries reduce one JOIN operation and save computing resources. You can use multi-column subqueries in the following ways:
      • Use a simple SELECT statement in which you specify multiple columns for the NOT IN SUBQUERY expression.
      • Use aggregate functions for the NOT IN SUBQUERY expression. For more information about aggregate functions, see Aggregate functions.
      • Use constants for the NOT IN SUBQUERY expression.
  • Parameters
    • select_expr1: required. The value of this parameter is in the format of col1_name, col2_name, Regular expression,.... This format indicates common columns or partition key columns that you want to query or regular expressions that are used for a query.
    • table_name1 and table_name2: required. These parameters specify the names of tables.
    • select_expr2: required. This parameter specifies the names of the columns in the tables specified by table_name1 and table_name2. Columns in the two tables are mapped to each other.
    • col_name: required. This parameter specifies the name of a column in the table.
  • Examples
    • Example 1: Use Syntax 1. The following statements show an example:
      -- 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);
      The following result is returned:
      +------------+-------------+-------------+
      | shop_name  | customer_id | total_price |
      +------------+-------------+-------------+
      | s8         | c1          | 100.1       |
      +------------+-------------+-------------+
    • Example 2: Use Syntax 2. The following statement shows an example:
      select * from shop1 where shop_name not in (select shop_name from sale_detail where customer_id = shop1.customer_id);
      The following result is returned:
      +------------+-------------+-------------+
      | shop_name  | customer_id | total_price |
      +------------+-------------+-------------+
      | s8         | c1          | 100.1       |
      +------------+-------------+-------------+
    • Example 3: NOT IN SUBQUERY does not serve as a JOIN condition. The following statement shows an example:
      select * from shop1 where shop_name not in (select shop_name from sale_detail) and total_price < 100.3;

      NOT IN SUBQUERY cannot be converted into ANTI JOIN. This is because the WHERE clause includes an AND operator. A separate job is started to run a subquery.

      The following result is returned:
      +------------+-------------+-------------+
      | shop_name  | customer_id | total_price |
      +------------+-------------+-------------+
      | s8         | c1          | 100.1       |
      +------------+-------------+-------------+
    • Example 4: If the values of a row in the table from which you want to query data are NULL, no data is returned. The following statements show an example:
      -- 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);
      
      select * from sale where shop_name not in (select shop_name from sale_detail);
      The following result is returned:
      +------------+-------------+-------------+------------+------------+
      | shop_name  | customer_id | total_price | sale_date  | region     |
      +------------+-------------+-------------+------------+------------+
      +------------+-------------+-------------+------------+------------+
    • Example 5: Multiple columns are specified in a SELECT statement for a subquery. The following statements show an example:
      -- 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

When you use an EXISTS SUBQUERY clause, if the subquery returns at least one row of data, True is returned. If the subquery does not return data, False is returned.

MaxCompute supports only the WHERE subqueries that have correlated conditions. To use an EXISTS SUBQUERY clause, you must convert this clause into LEFT SEMI JOIN.

  • Syntax
    select <select_expr> from <table_name1> where exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
  • Parameters
    • select_expr: required. The value of this parameter is in the format of col1_name, col2_name, Regular expression,.... This format indicates common columns or partition key columns that you want to query or regular expressions that are used for a query.
    • table_name1 and table_name2: required. These parameters specify the names of tables.
    • col_name: required. This parameter specifies the name of a column in the table.
  • Example
    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;
    The following result is returned:
    +------------+-------------+-------------+------------+------------+
    | 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

When you use a NOT EXISTS SUBQUERY clause, if no data is returned, True is returned. Otherwise, False is returned.

MaxCompute supports only the WHERE subqueries that have correlated conditions. To use a NOT EXISTS SUBQUERY clause, you must convert this clause into LEFT ANTI JOIN.

  • Syntax
    select <select_expr> from <table_name1> where not exists (select <select_expr> from <table_name2> where <table_name2_colname> = <table_name1>.<colname>);
  • Parameters
    • select_expr: required. The value of this parameter is in the format of col1_name, col2_name, Regular expression,.... This format indicates common columns or partition key columns that you want to query or regular expressions that are used for a query.
    • table_name1 and table_name2: required. These parameters specify the names of tables.
    • col_name: required. This parameter specifies the name of a column in the table.
  • Example
    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;
    The following result is returned:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    +------------+-------------+-------------+------------+------------+

SCALAR SUBQUERY

If the output result of a SCALAR SUBQUERY clause contains only one row and one column of data, the result can be used as a scalar for data computations.

All SCALAR SUBQUERY clauses whose output result contains only one row and one column of data can be rewritten based on the following syntax. If the output result of SCALAR SUBQUERY contains only one row of data and one MAX or MIN operator is nested outside SCALAR SUBQUERY, the result does not change.

  • Syntax
    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>;
    Note
    • The output result of select count(*) from <table_name2> where <table_name2_colname> = <table_name1>.<colname> is a row set. The output contains only one row and one column of data. In this case, the result can be used as a scalar. In practical application, SCALAR SUBQUERY is converted into JOIN as much as possible.
    • The output result of SCALAR SUBQUERY can be used as a scalar only if you can confirm in the compilation phase that SCALAR SUBQUERY returns only one row and one column of data. If you cannot make this confirmation until the running phase, the compiler reports an error. The compiler can compile the statements that meet the following requirements:
      • The SELECT list of SCALAR SUBQUERY uses aggregate functions that are not included in the parameters of a specified user-defined table-valued function (UDTF).
      • SCALAR SUBQUERY that uses aggregate functions does not include a GROUP BY clause.
    SCALAR SUBQUERY also supports multi-column subqueries based on the preceding capabilities and limits.
    • A SELECT list is a SCALAR SUBQUERY expression in which you specify multiple columns. The expression must be an equality expression.
    • Columns in a SELECT list can be an expression of the BOOLEAN type. Only equivalent comparison is supported.
    • A WHERE clause supports multi-column comparison. Only equivalent comparison is supported.
  • Parameters
    • select_expr: required. The value of this parameter is in the format of col1_name, col2_name, Regular expression,.... This format indicates common columns or partition key columns that you want to query or regular expressions that are used for a query.
    • table_name1 and table_name2: required. These parameters specify the names of tables.
    • col_name: required. This parameter specifies the name of a column in the table.
    • Scalar operator: required. The operators include greater than (>), less than (<), equal to (=), greater than or equal to (>=), and less than or equal to (<=).
    • scalar_value: required. This parameter specifies a scalar value.
  • Limits
    • SCALAR SUBQUERY can reference columns from outer queries. If SCALAR SUBQUERY uses multiple-level nesting, only the outermost column can be referenced.
      -- 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 outer 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; 
    • SCALAR SUBQUERY can be used only in a WHERE clause.
      -- SCALAR SUBQUERY cannot be referenced in a SELECT statement for a subquery. 
      select * from t1 where (select t1.b + count(*) from t2) = 3; 
      -- SCALAR SUBQUERY cannot be referenced in a SELECT statement for an outer query. 
      select (select count(*) from t2 where t2.a = t1.a) from t1; 
  • Examples
    • Example 1: Common usage. The following statement shows an example:
      select * from shop where (select count(*) from sale_detail where sale_detail.shop_name = shop.shop_name) >= 1;
      The following result is returned:
      +------------+-------------+-------------+
      | 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       |
      +------------+-------------+-------------+
    • Example 2: Multiple columns are specified in a SELECT statement for a subquery. The following statements show an example:
      -- 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: Columns in a SELECT list is a SCALAR SUBQUERY expression in which you specify multiple columns. 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: Columns in a SELECT list is 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        |
      +------------+------------+------------+