Uso de subconsultas no MaxCompute - MaxCompute: Central de Documentação do Alibaba Cloud

Tipos de subconsulta

As subconsultas do MaxCompute dividem-se em duas dimensões independentes:

Correlacionadas vs. não correlacionadas

Uma subconsulta correlacionada referencia uma ou mais colunas da consulta externa e usa os valores da linha atual para filtrar resultados.
Uma subconsulta não correlacionada não faz referência à consulta externa. O MaxCompute a executa independentemente e passa o resultado para a consulta externa.

Escalar vs. não escalar

Uma subconsulta escalar retorna exatamente uma coluna de uma única linha. Use o valor retornado em qualquer lugar onde uma expressão escalar seja válida.
Uma subconsulta não escalar retorna zero ou mais linhas e geralmente é usada com IN, NOT IN, EXISTS ou NOT EXISTS.

Os seguintes tipos de subconsulta são suportados:

Tipo	Cláusula principal	Caso de uso
Subconsulta básica	`FROM`	Usar o resultado de uma consulta como tabela derivada
Subconsulta IN	`WHERE`	Manter linhas cujo valor de coluna corresponda a qualquer valor retornado por uma subconsulta
Subconsulta NOT IN	`WHERE`	Manter linhas cujo valor de coluna não corresponda a nenhum valor retornado por uma subconsulta
Subconsulta EXISTS	`WHERE`	Manter linhas para as quais uma subconsulta correlacionada retorne pelo menos uma linha
Subconsulta NOT EXISTS	`WHERE`	Manter linhas para as quais uma subconsulta correlacionada não retorne nenhuma linha
Subconsulta escalar	`SELECT`, `WHERE`, `HAVING`	Usar um único valor agregado de uma tabela relacionada como escalar

Nota

O MaxCompute converte subconsultas SCALAR, IN, NOT IN, EXISTS e NOT EXISTS em operações JOIN durante a execução. MAPJOIN é um algoritmo eficiente de junção por broadcast. Se o resultado da subconsulta for uma tabela pequena, adicione um hint MAPJOIN à instrução da subconsulta para solicitar explicitamente esse algoritmo.

Dados de exemplo

Os exemplos neste tópico usam as tabelas a seguir. Execute estas instruções para configurar os dados de exemplo.

-- 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.
alter table sale_detail add partition (sale_date='2013', region='china') partition (sale_date='2014', region='shanghai');

-- Insert data.
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);

Para verificar os dados:

set odps.sql.allow.fullscan=true;
select * from sale_detail;

Resultado:

+------------+-------------+-------------+------------+------------+
| 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   |
+------------+-------------+-------------+------------+------------+

Subconsulta básica

Uma subconsulta básica aparece na cláusula FROM e atua como tabela derivada para a consulta externa. Junte-a a outras tabelas assim como faria com uma tabela comum.

Para obter informações sobre a sintaxe JOIN, consulte JOIN.

Sintaxe

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

Também é possível usar uma cláusula WITH (expressão de tabela comum) para nomear a subconsulta antes do SELECT principal:

with <sq_alias_name> as (<select_statement>)
select <select_expr> from <sq_alias_name>;

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_expr`	Sim	Colunas ou colunas de chave de partição a retornar. Aceita o formato `col1_name, col2_name, ...` ou uma expressão regular.
`select_statement`	Sim	Instrução SELECT da subconsulta. Consulte Sintaxe SELECT.
`sq_alias_name`	Não	Alias para o conjunto de resultados da subconsulta. Obrigatório ao referenciar a subconsulta na consulta externa.

Exemplos

Exemplo 1: Selecionar a partir do resultado de uma subconsulta.

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

Resultado:

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

Exemplo 2: Juntar o resultado de uma subconsulta a outra tabela.

-- Create a shop table, then join a subquery against it.
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;

Resultado:

+------------+-------------+-------------+
| 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       |
+------------+-------------+-------------+

Subconsulta IN

Uma subconsulta IN filtra a consulta externa para linhas em que um valor de coluna corresponde a qualquer valor retornado pela subconsulta. Internamente, o MaxCompute converte essa operação em LEFT SEMI JOIN. Para obter mais informações, consulte Semi join.

O MaxCompute suporta três variantes de sintaxe, incluindo correspondência de múltiplas colunas (compatível com PostgreSQL).

Sintaxe

Sintaxe 1 — não correlacionada, coluna única:

select <select_expr1> from <table_name1>
where <select_expr2> in (select <select_expr3> from <table_name2>);

-- Equivalent LEFT SEMI JOIN form:
select <select_expr1> from <table_name1> <alias_name1>
left semi join <table_name2> <alias_name2>
on <alias_name1>.<select_expr2> = <alias_name2>.<select_expr3>;

Nota

Quando select_expr2 é uma coluna de chave de partição, o MaxCompute não converte a subconsulta em LEFT SEMI JOIN. Em vez disso, executa a subconsulta como job separado e compara os resultados com os valores da chave de partição. Partiçõescujos valores-chave não aparecem no resultado da subconsulta são ignoradas; portanto, a poda de partições ainda se aplica.

Sintaxe 2 — correlacionada, coluna única:

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>
);

A condição where que referencia tanto a tabela interna quanto a externa é uma condição correlacionada. O MaxCompute V1.0 não suporta expressões que referenciam tabelas de origem tanto de subconsultas quanto de consultas principais. Já o MaxCompute V2.0 suporta tais expressões. Essas condições tornam-se parte da cláusula ON no SEMI JOIN resultante.

Nota

Quando uma subconsulta IN não pode ser convertida em SEMI JOIN — por exemplo, quando aparece fora de uma cláusula WHERE ou quando a condição da cláusula WHERE não pode ser expressa como JOIN — o MaxCompute a executa como job separado. Condições correlacionadas não são suportadas nesse caminho de execução.

Sintaxe 3 — correspondência de múltiplas colunas:

select <select_expr1> from <table_name1>
where (<col_name1>, <col_name2>) in (
  select <select_expr2>, <select_expr3> from <table_name2>
);

Subconsultas IN de múltiplas colunas eliminam a necessidade de dividir uma consulta em várias subconsultas e economizam uma operação JOIN. A subconsulta pode usar:

Instrução SELECT simples com múltiplas colunas
Funções de agregação (consulte Funções de agregação)
Listas de valores constantes

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_expr1`	Sim	Colunas a retornar da consulta principal (`col1_name, col2_name, ...` ou expressão regular).
`table_name1`, `table_name2`	Sim	Tabela principal e tabela da subconsulta.
`select_expr2`, `select_expr3`	Sim	Colunas de `table_name1` e `table_name2` para comparação.
`col_name`	Sim	Nome da coluna usada em condição correlacionada.

Notas de uso

Valores NULL são automaticamente excluídos do resultado da subconsulta. Uma linha na tabela principal é incluída apenas se seu valor de coluna for encontrado nos resultados não NULL da subconsulta.

Exemplos

Exemplo 1 (Sintaxe 1): Retornar linhas de sale_detail onde total_price aparece na tabela shop.

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

Resultado:

+-----------+-------------+-------------+-----------+----------+
| 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 |
+-----------+-------------+-------------+-----------+----------+

Exemplo 2 (Sintaxe 2): Usar condição correlacionada para filtrar por customer_id entre tabelas.

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
);

Resultado:

+-----------+-------------+-------------+-----------+----------+
| 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 |
+-----------+-------------+-------------+-----------+----------+

Exemplo 3 (Sintaxe 3): Correspondência de múltiplas colunas com SELECT simples, funções de agregação e constantes.

-- Set up sample tables for 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: Multi-column SELECT.
select a, b from t1 where (c, d) in (select a, b from t2 where e = t1.e);

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 2          |
| 3          | 1          |
+------------+------------+

-- Scenario 2: Aggregate functions in the subquery.
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);

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
+------------+------------+

-- Scenario 3: Constant value list.
select a, b from t1 where (c, d) in ((1, 3), (1, 1));

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 2          |
| 3          | 1          |
+------------+------------+

Subconsulta NOT IN

Uma subconsulta NOT IN filtra a consulta externa para linhas em que um valor de coluna não corresponde a nenhum valor retornado pela subconsulta. Internamente, o MaxCompute converte essa operação em LEFT ANTI JOIN. Para obter mais informações, consulte Semi join.

Aviso

A subconsulta NOT IN e o LEFT ANTI JOIN tratam valores NULL de maneira diferente. Se qualquer linha na tabela principal tiver valor NULL na coluna filtrada, toda a expressão NOT IN será avaliada como NULL. A condição WHERE falha e nenhuma linha é retornada — nem mesmo linhas com valores não NULL. Para tratar valores NULL explicitamente, reescreva a consulta como LEFT ANTI JOIN.

Sintaxe

Sintaxe 1 — não correlacionada, coluna única:

select <select_expr1> from <table_name1>
where <select_expr2> not in (select <select_expr2> from <table_name2>);

-- Equivalent LEFT ANTI JOIN form:
select <select_expr1> from <table_name1> <alias_name1>
left anti join <table_name2> <alias_name2>
on <alias_name1>.<select_expr1> = <alias_name2>.<select_expr2>;

Nota

Quando select_expr2 é uma coluna de chave de partição, o MaxCompute não converte a subconsulta em LEFT ANTI JOIN. Ele executa a subconsulta separadamente e ignora partições cujos valores-chave não aparecem no resultado. A poda de partições permanece válida.

Sintaxe 2 — correlacionada, coluna única:

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

A condição where que referencia ambas as tabelas é uma condição correlacionada. O MaxCompute V1.0 não suporta expressões que referenciam tabelas de origem tanto de subconsultas quanto de consultas principais. Já o MaxCompute V2.0 suporta tais expressões. Essas condições tornam-se parte da cláusula ON no ANTI JOIN resultante.

Nota

Quando uma subconsulta NOT IN não pode ser convertida em ANTI JOIN — por exemplo, quando a cláusula WHERE inclui um AND com outras condições — o MaxCompute a executa como job separado. Condições correlacionadas não são suportadas nesse caminho de execução.

Sintaxe 3 — correspondência de múltiplas colunas:

select <select_expr1> from <table_name1>
where (<col_name1>, <col_name2>) not in (
  select <select_expr2>, <select_expr3> from <table_name2>
);

Subconsultas NOT IN de múltiplas colunas eliminam a necessidade de dividir uma consulta em várias subconsultas e economizam uma operação JOIN. A subconsulta pode usar SELECT simples com múltiplas colunas, funções de agregação ou listas de valores constantes.

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_expr1`	Sim	Colunas a retornar da consulta principal.
`table_name1`, `table_name2`	Sim	Tabela principal e tabela da subconsulta.
`select_expr2`, `select_expr3`	Sim	Colunas a comparar entre as duas tabelas.
`col_name`	Sim	Nome da coluna usada em condição correlacionada.

Notas de uso

Valores NULL são automaticamente excluídos do resultado da subconsulta.
Se uma linha na tabela principal tiver valor NULL na coluna filtrada, a expressão NOT IN retornará NULL e a linha será excluída dos resultados, independentemente de outros valores. Use LEFT ANTI JOIN se esse comportamento não for desejado.

Exemplos

Exemplo 1 (Sintaxe 1): Retornar linhas de shop1 onde shop_name não aparece em sale_detail.

-- Create a shop1 table with an extra row not in sale_detail.
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);

Resultado:

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

Exemplo 2 (Sintaxe 2): Subconsulta NOT IN correlacionada.

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
);

Resultado:

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

Exemplo 3: Subconsulta NOT IN que não pode ser convertida em ANTI JOIN. Quando a cláusula WHERE inclui um AND com outros predicados, o MaxCompute não consegue converter para ANTI JOIN e executa job de subconsulta separado.

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;

Resultado:

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

Exemplo 4: NULL na coluna filtrada — nenhuma linha retornada. Este exemplo ilustra o aviso sobre o comportamento de NULL mencionado acima.

-- Create a sale table with a row where shop_name is NULL.
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);

Resultado: Nenhuma linha é retornada porque uma linha em sale tem shop_name NULL, o que faz com que toda a expressão NOT IN seja avaliada como NULL.

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

Exemplo 5 (Sintaxe 3): Correspondência NOT IN de múltiplas colunas.

-- Uses the same t1 and t2 tables from the IN subquery examples.

-- Scenario 1: Multi-column SELECT.
select a, b from t1 where (c, d) not in (select a, b from t2 where e = t1.e);

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 2          | 1          |
| 1          | 1          |
+------------+------------+

-- Scenario 2: 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);

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 3          | 1          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+

-- Scenario 3: Constant value list.
select a, b from t1 where (c, d) not in ((1, 3), (1, 1));

Resultado:

+------------+------------+
| a          | b          |
+------------+------------+
| 1          | 3          |
| 2          | 1          |
| 1          | 1          |
+------------+------------+

Subconsulta EXISTS

Uma subconsulta EXISTS retorna True para uma linha da consulta externa quando a subconsulta retorna pelo menos uma linha correspondente. Use-a para verificar se existe registro relacionado sem considerar os valores reais retornados.

O MaxCompute suporta apenas subconsultas EXISTS na cláusula WHERE com condições correlacionadas. Converta a subconsulta EXISTS em LEFT SEMI JOIN antes da execução.

Sintaxe

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

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_expr`	Sim	Colunas a retornar (`col1_name, col2_name, ...` ou expressão regular).
`table_name1`, `table_name2`	Sim	Tabela principal e tabela da subconsulta.
`col_name`	Sim	Coluna usada na condição correlacionada.

Notas de uso

O MaxCompute suporta subconsultas EXISTS apenas na cláusula WHERE com condição correlacionada.
Para usar subconsulta EXISTS, converta essa cláusula em LEFT SEMI JOIN.

Exemplo

Retornar todas as linhas de sale_detail que possuem customer_id correspondente em shop.

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

-- Equivalent LEFT SEMI JOIN form:
select * from sale_detail a left semi join shop b on a.customer_id = b.customer_id;

Resultado:

+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

Subconsulta NOT EXISTS

Uma subconsulta NOT EXISTS retorna True para uma linha da consulta externa quando a subconsulta não retorna nenhuma linha correspondente. Use-a para encontrar linhas na tabela principal que não possuem registro relacionado em outra tabela.

O MaxCompute suporta apenas subconsultas NOT EXISTS na cláusula WHERE com condições correlacionadas. Converta a subconsulta NOT EXISTS em LEFT ANTI JOIN antes da execução.

Sintaxe

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

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_expr`	Sim	Colunas a retornar (`col1_name, col2_name, ...` ou expressão regular).
`table_name1`, `table_name2`	Sim	Tabela principal e tabela da subconsulta.
`col_name`	Sim	Coluna usada na condição correlacionada.

Notas de uso

O MaxCompute suporta subconsultas NOT EXISTS apenas na cláusula WHERE com condição correlacionada.
Para usar subconsulta NOT EXISTS, converta essa cláusula em LEFT ANTI JOIN.

Exemplo

Retornar todas as linhas de sale_detail que não possuem shop_name correspondente em shop.

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

-- Equivalent LEFT ANTI JOIN form:
select * from sale_detail a left anti join shop b on a.shop_name = b.shop_name;

Resultado: Nenhuma linha é retornada porque cada shop_name em sale_detail possui correspondência em shop.

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

Subconsulta escalar

Uma subconsulta escalar retorna exatamente uma coluna de uma única linha. Use o resultado como valor escalar em lista SELECT, cláusula WHERE ou cláusula HAVING — onde quer que expressão escalar seja válida.

O MaxCompute pode verificar em tempo de compilação que uma subconsulta retorna apenas uma linha quando:

A lista SELECT usa funções de agregação não passadas como parâmetros para função de valor de tabela definida pelo usuário (UDTF).
A subconsulta de agregação não inclui cláusula GROUP BY.

Se o MaxCompute não conseguir determinar estaticamente em tempo de compilação que a subconsulta retorna uma única linha, ele relatará erro de compilação. A verificação não ocorre apenas em tempo de execução.

Se o resultado de saída de uma subconsulta escalar contiver apenas uma linha de dados e um operador MAX ou MIN estiver aninhado fora da subconsulta escalar, o resultado não será alterado.

O MaxCompute também converte subconsultas escalares em operações JOIN sempre que possível. Por exemplo, subconsulta escalar correlacionada em cláusula WHERE que compara com valor escalar é internamente reescrita como LEFT SEMI JOIN com predicado HAVING.

Sintaxe

Sintaxe 1 — escalar correlacionada em cláusula WHERE (comparada com valor escalar):

select <select_expr> from <table_name1>
where (
  select count(*) from <table_name2>
  where <table_name2_colname> = <table_name1>.<colname>
) <scalar_operator> <scalar_value>;

-- Equivalent JOIN form:
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>;

Sintaxe 2 — subconsulta escalar na lista SELECT:

select (<select_statement>) from <table_name>;

Parâmetros

Parâmetro	Obrigatório	Descrição
`select_expr`	Sim	Colunas a retornar da consulta principal.
`table_name1`, `table_name2`	Sim	Tabela principal e tabela da subconsulta.
`col_name`	Sim	Coluna usada na condição correlacionada.
`scalar_operator`	Sim	Operador de comparação: `>`, `<`, `=`, `>=` ou `<=`.
`scalar_value`	Sim	Valor limite escalar para comparação.
`select_statement`	Sim (Sintaxe 2)	Instrução da subconsulta. Deve retornar exatamente uma linha. Consulte Sintaxe SELECT.

Notas de uso

Subconsulta escalar pode referenciar colunas da consulta principal. Em aninhamento de vários níveis, apenas as colunas da consulta principal mais externa podem ser referenciadas pela subconsulta escalar.
Subconsultas escalares de múltiplas colunas são suportadas na lista SELECT e na cláusula WHERE, mas apenas com comparações de igualdade (=). Comparações de maior ou menor que com expressões de múltiplas colunas não são suportadas.

Limitações

O seguinte aninhamento de vários níveis não é suportado porque a subconsulta escalar interna referencia colunas de t1, que está dois níveis acima:

-- This statement fails: inner subquery cannot reference t1 columns.
select * from t1
where (
  select count(*) from t2
  where (select count(*) from t3 where t3.a = t1.a) = 2
) = 3;

-- This statement works: the scalar subquery references only t1 (one level up).
select * from t1 where (select count(*) from t2 where t1.a = t2.a) = 3;

Exemplos

Exemplo 1: Filtrar linhas usando contagem correlacionada.

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

Resultado:

+------------+-------------+-------------+
| 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       |
+------------+-------------+-------------+

Exemplo 2: Subconsultas escalares de múltiplas colunas (apenas igualdade).

-- Set up sample tables.
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: Scalar subquery with multiple columns in the SELECT list (equality only).
-- This fails: 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;

Resultado:

+------------+------------+------------+
| a          | b          | a2         |
+------------+------------+------------+
| 1          | 3          | 1          |
| NULL       | NULL       | 1          |
+------------+------------+------------+

-- Scenario 2: BOOLEAN expression with multi-column equality in the SELECT list.
-- This fails: 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;

Resultado:

+-------+
| _c0   |
+-------+
| true  |
| false |
+-------+

-- Scenario 3: Multi-column equality comparison in a WHERE clause.
-- This fails: 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);

Resultado:

+------------+------------+------------+
| 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);

Resultado:

+------------+------------+------------+
| a          | b          | c          |
+------------+------------+------------+
| 1          | 3          | 4.0        |
| 1          | 3          | 5.0        |
+------------+------------+------------+

Exemplo 3 (Sintaxe 2): Usar subconsulta escalar na lista SELECT. A subconsulta deve retornar exatamente uma linha.

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

Resultado: A única linha da subconsulta é retornada uma vez para cada linha na tabela sale_detail externa.

+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

Limitações

As seguintes limitações aplicam-se a todos os tipos de subconsulta:

EXISTS e NOT EXISTS: O MaxCompute suporta estes operadores apenas na cláusula WHERE com condição correlacionada. Subconsultas EXISTS não correlacionadas não são suportadas.
Subconsultas escalares: A subconsulta deve ser estaticamente determinável como retorno de uma linha em tempo de compilação. Use função de agregação sem GROUP BY para atender a esse requisito. Se o MaxCompute não puder confirmar a saída de uma linha em tempo de compilação, a instrução falhará com erro de compilação.
Aninhamento de subconsultas escalares: Apenas as colunas da consulta principal mais externa podem ser referenciadas dentro de subconsulta escalar aninhada. Referenciar colunas de níveis intermediários de consulta não é suportado.
Comparações escalares de múltiplas colunas: Apenas igualdade (=) é suportada. Comparações de maior ou menor que com expressões de subconsulta de múltiplas colunas não são válidas.
IN/NOT IN sem conversão JOIN: Quando subconsulta IN ou NOT IN aparece fora de cláusula WHERE, ou quando a condição WHERE não pode ser reescrita como condição JOIN, o MaxCompute executa job separado. Condições correlacionadas não são suportadas neste caminho de execução.

Veja também

O uso excessivo de subconsultas ou a criação de subconsultas ineficientes pode desacelerar significativamente as consultas em ambiente de dados de grande escala. Considere estas alternativas:

Substitua subconsultas repetidas por tabelas temporárias ou visualizações materializadas para evitar computação redundante.
Reescreva múltiplas subconsultas correlacionadas como única operação JOIN.

Para obter mais informações, consulte Visualizações materializadas e JOIN.