Este é o segundo de uma série de artigos sobre subconsultas. Neste artigo, discutimos subconsultas na lista de colunas da instrução SELECT. Outros artigos discutem seus usos em outras cláusulas.
Todos os exemplos desta lição são baseados no Microsoft SQL Server Management Studio e no banco de dados AdventureWorks2012. Você pode começar usando essas ferramentas gratuitas usando meu Guia de introdução ao SQL Server.
Usando subconsultas na instrução Select
Quando uma subconsulta é colocada na lista de colunas, ela é usada para retornar valores únicos. Nesse caso, você pode pensar na subconsulta como uma expressão de valor único. O resultado retornado não é diferente da expressão “2 + 2.” Obviamente, as subconsultas também podem retornar texto, mas você entendeu!
Ao trabalhar com subconsultas, a instrução principal às vezes é chamada de consulta externa. As subconsultas são colocadas entre parênteses, o que as torna mais fáceis de identificá-las .
Tenha cuidado ao usar subconsultas. Elas podem ser divertidas de usar, mas à medida que você adiciona mais à sua consulta, elas podem começar a desacelerar sua consulta.
Subconsulta simples para calcular a média
Vamos começar com uma consulta simples para mostrar SalesOrderDetail e compará-la com a média geral SalesOrderDetail LineTotal. A instrução SELECT que usaremos é:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Esta consulta retorna resultados como:
A subconsulta, que é mostrada em vermelho acima, é executada primeiro para obter o LineTotal médio.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Esse resultado é então inserido de volta na lista de colunas e a consulta continua. Há várias coisas que quero destacar :
- As subconsultas são colocadas entre parênteses .
- Quando subconsultas são usadas em uma instrução SELECT, elas podem retornar apenas um valor. Isso deve fazer sentido, simplesmente selecionando uma coluna retorna um valor para uma linha, e precisamos seguir o mesmo padrão.
- Em geral, a subconsulta é executada apenas uma vez para toda a consulta e seu resultado reutilizado . Isso ocorre porque o resultado da consulta não varia para cada linha retornada.
- É importante usar aliases para os nomes das colunas para melhorar a legibilidade.
Subconsulta simples na expressão
Como você pode esperar, o resultado de uma subconsulta pode ser usado em outras expressões. Com base no exemplo anterior, vamos usar a subconsulta para determinar o quanto nosso LineTotal varia da média.
A variação é simplesmente o LineTotal menos o total da linha média. Na subconsulta a seguir, coloquei em azul. Aqui está a fórmula para a variação:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
A instrução SELECT entre parênteses é a subconsulta. Como o exemplo anterior, esta consulta será executada uma vez, retornará um valor numérico, que é subtraído de cada valor LineTotal.
Aqui está a consulta em sua forma final:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Aqui está o resultado:
Ao trabalhar com subconsultas em instruções de seleção, normalmente eu construo e teste a subconsulta primeiro. As instruções SELECT podem se complicar muito rapidamente. É melhor construí-los pouco a pouco. Construir e testar as várias peças separadamente, realmente ajuda na depuração.
Consultas correlacionadas
Existem maneiras de incorporar os valores da consulta externa nas cláusulas da subconsulta. Esses tipos de consultas são chamados de subconsultas correlacionadas, uma vez que os resultados da subconsulta estão conectados, de alguma forma, a valores na consulta externa. As consultas correlacionadas às vezes são chamadas de consultas sincronizadas.
Se você está tendo problemas para saber o que significa correlato, verifique esta definição do Google:
Correlacionar: “tenha um relacionamento ou conexão mútua, em que uma coisa afeta ou depende de outra. ”
Um uso típico para uma subconsulta correlacionada é usar uma das colunas da consulta externa na cláusula WHERE da consulta interna. Isso é senso comum em muitos casos que você deseja restringir a consulta interna a um subconjunto de dados.
Exemplo de subconsulta correlacionada
Forneceremos um exemplo de subconsulta correlacionada relatando cada SalesOrderDetail LineTotal e o Average LineTotal para as vendas gerais Pedido.
Essa solicitação difere significativamente de nossos exemplos anteriores, pois a média que estamos calculando varia para cada pedido de venda.
É aqui que as subconsultas correlacionadas entram em jogo. Podemos usar um valor da consulta externa e incorporá-lo aos critérios de filtro da subconsulta.
Vamos dar uma veja como calculamos o total da linha média. Para fazer isso, juntei uma ilustração que mostra a instrução SELECT com subconsulta.
Para elaborar mais sobre o diagrama. A instrução SELECT consiste em duas partes, a consulta externa e a subconsulta. A consulta externa é usada para recuperar todas as linhas SalesOrderDetail.A subconsulta é usada para encontrar e resumir as linhas de detalhes do pedido de vendas para um SalesOrderID específico.
Se eu fosse verbalizar as etapas que vamos pegar, eu os resumiria como:
- Obtenha o SalesOrderID.
- Retorne o LineTotal médio de todos os itens SalesOrderDetail onde o SalesOrderID corresponde.
- Continue para o próximo SalesOrderID na consulta externa e repita as etapas 1 e 2.
A consulta que você pode executar no banco de dados AdventureWork2012 é:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Aqui estão os resultados da consulta:
Existem alguns itens a serem apontados.
- Você pode ver que usei aliases de coluna para ajudar a tornar os resultados da consulta mais fáceis de ler.
- Também usei um alias de tabela, SOD, para o consulta externa. Isso torna possível usar os valores da consulta externa na subconsulta. Caso contrário, a consulta não está correlacionada!
- Usar os apelidos de tabela torna claro quais colunas são de cada tabela.
Dividindo a subconsulta correlacionada
Agora, vamos tentar dividir isso usando SQL.
Para começar, vamos supor que vamos apenas obter nosso exemplo para SalesOrderDetailID 20. O SalesOrderID correspondente é 43661.
É fácil obter o LineTotal médio para este item
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Isso retorna o valor 2181,765240.
Agora que temos a média, podemos conecte-o em nossa consulta
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Usando subconsultas, isso se torna
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
A consulta final é :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Subconsulta Correlacionada com uma Tabela Diferente
Uma subconsulta Correlacionada, ou qualquer subconsulta, pode usar uma tabela diferente que a consulta externa. Isso pode ser útil quando você está trabalhando com uma tabela “pai”, como SalesOrderHeader, e deseja incluir no resultado um resumo das linhas filhas, como as de SalesOrderDetail.
Vamos retornar o OrderDate, TotalDue e número de linhas de detalhe do pedido de vendas. Para fazer isso, podemos usar o seguinte diagrama para nos orientar:
Para fazer isso, incluiremos uma subconsulta correlacionada em nossa instrução SELECT para retornar o COUNT de linhas SalesOrderDetail. Garantiremos que estamos contando o item SalesOrderDetail correto filtrando pelo SalesOrderID da consulta externa.
Aqui está a instrução SELECT final:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Os resultados são:
Algumas coisas a serem observadas neste exemplo são:
- A subconsulta está selecionando dados de uma tabela diferente da consulta externa.
- Usei tabela e aliases de coluna para facilitar a leitura do SQL e dos resultados.
- Certifique-se de verificar duas vezes k sua cláusula where! Se você esquecer de incluir o nome da tabela ou apelidos na cláusula WHERE da subconsulta, a consulta não será correlacionada.
Subconsultas correlacionadas versus junções internas
É importante para entender que você pode obter os mesmos resultados usando uma subconsulta ou junção. Embora ambos retornem os mesmos resultados, há vantagens e desvantagens em cada método!
Considere o último exemplo em que contamos itens de linha para itens SalesHeader.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Esta mesma consulta pode ser feita usando INNER JOIN junto com GROUP BY como
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
Qual é mais rápido?
Você verá que muitas pessoas dirão para evitar subconsultas porque são mais lentas. Eles argumentarão que a subconsulta correlacionada deve “executar” uma vez para cada linha retornada na consulta externa, enquanto o INNER JOIN só precisa fazer uma passagem pelos dados.
Eu mesmo? Eu digo verificar o plano de consulta. Segui meu próprio conselho para ambos os exemplos acima e descobri que os planos eram os mesmos!
Isso não quer dizer que os planos mudariam se houvesse mais dados, mas meu ponto é que você não deve apenas fazer suposições. A maioria dos otimizadores de SQL DBMS são realmente bons em descobrir a melhor maneira de executar sua consulta. Eles pegam suas sintaxes, como uma subconsulta ou INNER JOIN, e as usam para criar um plano de execução real.
Qual é mais fácil de ler?
Dependendo do que você está acostumado com, você pode achar o exemplo INNER JOIN mais fácil de ler do que a consulta correlacionada. neste exemplo, gosto da subconsulta correlacionada porque parece mais direta. É mais fácil para mim ver o que está sendo contado.
Na minha opinião, INNER JOIN é menos direto. Primeiro, você deve verificar se todas as linhas de detalhes de vendas estão sendo retornadas à mão e depois resumidas. Você não entende mesmo antes de ler a declaração inteira.
Qual é o melhor?
Me diga o que você achou. Gostaria de saber se você prefere usar a subconsulta correlacionada ou o exemplo INNER JOIN.