Como trabalhar com subconsultas¶

Uma subconsulta é uma consulta dentro de outra consulta. Subconsultas em uma cláusula FROM ou WHERE são usadas para fornecer dados que serão usados para limitar ou comparar/avaliar os dados retornados pela consulta que as contém.

Neste tópico:

Tipos de subconsultas¶

Subconsultas correlacionadas vs. subconsultas não correlacionadas¶

As subconsultas podem ser classificadas como correlacionadas ou não correlacionadas:

Uma subconsulta correlacionada refere-se a uma ou mais colunas fora da subconsulta. (As colunas são normalmente referenciadas dentro da cláusula WHERE da subconsulta). Uma subconsulta correlacionada pode ser pensada como um filtro na tabela a que se refere, como se a subconsulta fosse avaliada em cada linha da tabela na consulta externa.
Uma subconsulta não correlacionada não tem tais referências de colunas externas. É uma consulta independente, cujos resultados são retornados e utilizados pela consulta externa uma vez (não por linha).

Por exemplo:

-- Uncorrelated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT MAX(x) FROM table2);

-- Correlated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT x FROM table2 WHERE y = table1.c2);

Copy

Subconsultas escalares vs. não escalares¶

Subconsultas também podem ser categorizadas como escalares ou não escalares:

Uma subconsulta escalar retorna um único valor (uma coluna de uma linha). Se nenhuma linha se qualificar para ser retornada, a subconsulta retorna NULL.
Uma subconsulta não escalar retorna 0, 1 ou múltiplas linhas, cada uma das quais pode conter 1 ou múltiplas colunas. Para cada coluna, se não houver valor a retornar, a subconsulta retorna NULL. Se nenhuma linha se qualificar para ser retornada, a subconsulta retorna 0 linhas (não NULLs).

Tipos com suporte pelo Snowflake¶

O Snowflake atualmente suporta os seguintes tipos de subconsultas:

Subconsultas escalares não correlacionadas em qualquer lugar em que uma expressão de valor possa ser usada.
Subconsultas escalares correlacionadas em cláusulas WHERE.
Subconsultas EXISTS, ANY/ALL e IN em cláusulas WHERE. Estas subconsultas podem ser correlacionadas ou não correlacionadas.

Operadores de subconsulta¶

Operadores de subconsultas operam em expressões de consulta aninhadas. Eles podem ser usados para calcular valores que são:

Retornados em uma lista SELECT.
Agrupados em uma cláusula GROUP BY.
Comparados com outras expressões da cláusula WHERE ou HAVING.

Diferenças entre subconsultas correlacionadas e não correlacionadas¶

A consulta a seguir demonstra uma subconsulta não correlacionada em uma cláusula WHERE. A subconsulta obtém o GDP per capita do Brasil, e a consulta externa seleciona todos os empregos (em qualquer país) que pagam menos que o GDP per capita do Brasil. A subconsulta é não correlacionada porque o valor que ela retorna não depende de nenhuma coluna da consulta externa. A subconsulta só precisa ser chamada uma vez durante toda a execução da consulta externa.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT per_capita_GDP
                           FROM international_GDP
                           WHERE name = 'Brazil');

Copy

A próxima consulta demonstra uma subconsulta correlacionada em uma cláusula WHERE. A consulta lista empregos onde a remuneração anual do trabalho é menor que o GDP per capita naquele país. Esta subconsulta é correlacionada porque é chamada uma vez para cada linha na consulta externa e recebe um valor, p.country (nome do país), a partir da linha.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT MAX(per_capita_GDP)
                           FROM international_GDP i
                           WHERE p.country = i.name);

Copy

Nota

A função agregada MAX não é logicamente necessária neste caso porque a tabela international_GDP tem apenas uma linha por país; entretanto, como o servidor não sabe disso, e como o servidor exige que a subconsulta não retorne mais de uma linha, a consulta usa a função agregada para forçar o servidor a reconhecer que a subconsulta retornará apenas uma linha cada vez que for executada.

As funções MIN e AVG também funcionam porque a aplicação de qualquer uma delas a um único valor retorna esse valor inalterado.

Subconsultas escalares¶

Uma subconsulta escalar é uma subconsulta que retorna no máximo uma linha. Uma subconsulta escalar pode aparecer em qualquer lugar em que uma expressão de valor possa aparecer, incluindo a lista SELECT, a cláusula GROUP BY ou um argumento para uma função em uma cláusula WHERE ou HAVING.

Notas de uso¶

Uma subconsulta escalar pode conter apenas um item na lista SELECT.
Se uma subconsulta escalar retorna mais de uma linha, um erro de tempo de execução é gerado.
As subconsultas escalares relacionadas são atualmente com suporte somente se puderem ser determinadas estaticamente para retornar uma linha (por exemplo, se a lista SELECT contiver uma função agregada sem GROUP BY).
Subconsultas escalares não correlacionadas têm suporte em qualquer lugar em que uma expressão de valor seja permitida.
Subconsultas com uma correlação dentro de FLATTEN não são atualmente com suporte.
A cláusula LIMIT / FETCH é permitida somente em subconsultas escalares não correlacionadas.

Exemplos¶

Este exemplo mostra uma subconsulta básica não correlacionada em uma cláusula WHERE:

SELECT employee_id
FROM employees
WHERE salary = (SELECT max(salary) FROM employees);

Copy

Este exemplo mostra uma subconsulta não correlacionada em uma cláusula FROM; esta subconsulta básica retorna um subconjunto das informações na tabela international_GDP. A consulta geral lista empregos em países de “salários altos” onde a remuneração anual do emprego é a mesma que per_capita_GDP naquele país.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p INNER JOIN (SELECT name, per_capita_GDP
                              FROM international_GDP
                              WHERE per_capita_GDP >= 10000.0) AS pcg
    ON pcg.per_capita_GDP = p.annual_wage AND p.country = pcg.name;

Copy

Limitações¶

Embora as subconsultas possam conter uma ampla gama de instruções SELECT, elas têm as seguintes limitações:

Algumas cláusulas não são permitidas dentro das subconsultas ANY/ALL/NOT EXISTS.
O único tipo de subconsulta que permite uma cláusula LIMIT / FETCH é uma subconsulta escalar não correlacionada. Além disso, como uma subconsulta escalar não correlacionada retorna apenas 1 linha, a cláusula LIMIT tem pouco ou nenhum valor prático dentro de uma subconsulta.