Usando subconsultas de linha única

Descrevendo tipos de subconsultas

Existem muitos tipos de subconsultas. Aqui estão os principais tipos:

Subconsultas de linha única Uma subconsulta de linha única retorna o valor de uma única linha de dados em seu resultado. Subconsultas de linha única podem incluir dados de várias colunas ou dados de uma única coluna.

Subconsultas com várias linhas Uma subconsulta com várias linhas retorna zero, uma ou mais linhas no resultado. Não é garantido o retorno de várias linhas, mas isso pode ser feito e, portanto, a consulta pai deve ser estruturada para receber várias linhas, apenas para garantir a possibilidade de um erro de execução. Por exemplo, uma consulta pai pode usar um sinal de igual para comparar seus próprios valores de retorno com os valores retornados de uma subconsulta. Um sinal de igual realiza uma comparação com um único valor; portanto, no caso da subconsulta, apenas uma linha deve ser retornada. Mas se a subconsulta retornar várias linhas, a consulta pai (e, portanto, a consulta como um todo) falhará com um erro de execução – isso depende se a subconsulta realmente retorna várias linhas. Talvez não agora, mas certamente pode a qualquer momento. Portanto, a consulta pai a uma subconsulta de várias linhas geralmente usa um operador de comparação (como IN) que permite que várias linhas de valores sejam retornadas.

Subconsultas de várias colunas As subconsultas de várias colunas retornam mais de uma coluna no resultado. Isso requer que a consulta pai receba várias colunas da subconsulta e envolve considerações especiais de sintaxe, como acabamos de discutir. Uma subconsulta de várias colunas pode ser uma subconsulta de linha única ou uma subconsulta de várias linhas.

Subconsultas correlacionadas Uma subconsulta correlacionada é uma subconsulta que especifica colunas que pertencem a tabelas que também são referenciadas pela consulta pai. Em uma série multinível de subconsultas, subconsultas dentro de subconsultas e assim por diante, a consulta pai correlacionada pode ser qualquer número de níveis superior à subconsulta. A correlação envolve mais do que a subconsulta acessando apenas as mesmas tabelas e colunas por meio de sua própria chamada direta à tabela; em vez disso, a subconsulta correlacionada realiza análise linha por linha em cooperação com a consulta pai, acessando dados e referenciando esses dados em suas próprias expressões para coordenar o processamento de linhas com o pai correlacionado. Subconsultas correlatas podem existir nas instruções SELECT, UPDATE e DELETE. Uma subconsulta correlacionada também pode ser uma subconsulta de linha única, linha múltipla ou coluna múltipla.

Subconsultas escalares Se uma subconsulta de linha única consiste em apenas uma coluna de saída, é conhecida como subconsulta escalar. Subconsultas escalares podem ser usadas em quase qualquer local em que uma expressão possa ser usada, o que não é verdade para outras formas de subconsultas. Uma subconsulta escalar também pode ser correlacionada.

Exemplos de subconsultas com linha única.

Você está encarregado de listar todos e quaisquer representantes de vendas cujas vendas para 2016 superaram as vendas médias da equipe de vendas no ano anterior de 2017. Uma maneira de fazer isso é obter primeiro as vendas médias para 2017.

select avg(total_sales) avg_sales
from sales_data
where year = 2017;

AVG_SALES
---------
   478.75

Em seguida, use os resultados para procurar representantes de vendas em 2018 que obtiveram resultados acima da média de 2017.

select rep
from sales_data
where year = 2018
 and total_sales > 478.75

REP
-----
  Lyn

Essa é uma maneira de obter a resposta. Mas uma subconsulta teria encontrado a resposta em uma única consulta.

select rep
from sales_data
where year = 2018
 and total_sales > ( select avg(total_sales) avg_sales
                     from sales_data
                     where year = 2017;
                    )

Você pode usar subconsultas para vários propósitos.

  • Para preencher uma tabela usando zero a mais linhas de origem em uma instrução INSERT ou fazer praticamente o mesmo em uma instrução CREATE TABLE.
  • Para criar uma VIEW.
  • Para especificar valores atribuídos a uma instrução UPDATE.
  • Realizar comparações com resultados agregados.
  • Para criar visualizações embutidas: uma subconsulta na cláusula FROM de uma instrução SELECT. Não há limite para quantas subconsultas profundas você pode acessar em uma exibição embutida; você pode ter subconsultas de subconsultas de subconsultas nesse contexto.
  • Como uma alternativa para expressões.
  • Para aninhar outras consultas. Uma subconsulta na cláusula WHERE de uma instrução SELECT é conhecida como subconsulta aninhada porque está aninhada na consulta maior (a própria instrução SELECT). Você pode aninhar até 255 subconsultas dessa maneira.
  • Para ter uma subconsulta correlacionada dentro de um SELECT, UPDATE ou DELETE

Consultas correlacionadas

Uma subconsulta correlacionada é uma consulta integrada a uma consulta pai. As subconsultas correlacionadas incluem referências a elementos de uma consulta pai e, portanto, não existem como consultas independentes, como os exemplos que você viu até agora. Até agora, qualquer uma das subconsultas que você viu poderia ter sido executada por conta própria. Esse não é o caso de subconsultas correlacionadas. Vejamos um exemplo:

Observe a subconsulta que começa na linha 3 e continua até (e inclui) a linha 5. Em particular, observe o segundo ROOM_STYLE no final da linha 5. Veja o alias da tabela de A? Essa é uma referência a uma coluna da consulta pai – não à subconsulta. Consulte a linha 2 para confirmar que a tabela da consulta pai está com o alias do prefixo A.

Veja exemplo de um update:

Aqui está um exemplo de uma subconsulta correlacionada usada na cláusula SET de uma instrução UPDATE:

Vejamos agora delete com subconsulta correlacionada:

A instrução DELETE pode ser usada com uma subconsulta correlacionada na cláusula WHERE para determinar quais linhas excluir de uma determinada tabela. A sintaxe é semelhante à sintaxe de subconsulta correlacionada para instruções SELECT e UPDATE.

Usando várias subconsultas de linha

Podemos realizar várias subconsultas como por exemplo:

select * from dual where dummy in(
     select dummy from dual where dummy in(
          select dummy from dual
   ));

A consulta acima não tem muito sentido, mas é apenas para ilustrar que é uma consulta válida que podemos usar múltiplas subconsultas.