Utilize subconsultas autossuficientes ou correlacionadas
Anteriormente, examinamos subconsultas autossuficientes; nas quais a consulta interna é independente da consulta externa, é executada uma vez e retorna seus resultados para a consulta externa. O T-SQL também dá suporte a subconsultas correlacionadas, nas quais a consulta interna faz referência à coluna na consulta externa e é executada conceitualmente uma vez por linha.
Trabalhando com subconsultas correlacionadas
Assim como subconsultas autossuficientes, subconsultas correlacionadas são instruções SELECT aninhadas em uma consulta externa. As subconsultas correlacionadas também podem ser subconsultas escalares ou com valores múltiplos. Normalmente, elas são usadas quando a consulta interna precisa fazer referência a um valor na consulta externa.
No entanto, ao contrário das subconsultas autossuficientes, há algumas considerações especiais ao usar subconsultas correlacionadas:
- Subconsultas correlacionadas não podem ser executadas separadamente da consulta externa. Essa restrição complica o teste e a depuração.
- Ao contrário das subconsultas autossuficientes, que são processadas uma vez, as subconssultas correlacionadas serão executados várias vezes. Logicamente, a consulta externa é executada primeiro e, para cada linha retornada, a consulta interna é processada.
O exemplo a seguir usa uma subconsulta correlacionada para retornar o pedido mais recente para cada cliente. A subconsulta refere-se à consulta externa e faz referência ao seu valor CustomerID em sua cláusula WHERE. Para cada linha na consulta externa, a subconsulta localiza a ID de pedido máxima para o cliente referenciado nessa linha, e a consulta externa verifica se a linha para a qual está olhando é a linha com essa ID de pedido.
SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
(SELECT MAX(SalesOrderID)
FROM SalesLT.SalesOrderHeader AS o2
WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;
Escrevendo subconsultas correlacionadas
Para escrever subconsultas correlacionadas, considere as seguintes diretrizes:
- Escreva a consulta externa para aceitar o resultado de retorno apropriado da consulta interna. Se a consulta interna for escalar, você poderá usar operadores de igualdade e comparação, como =, <, >, e <> na cláusula WHERE. Se a consulta interna puder retornar vários valores, use um predicado IN. Planeje lidar com resultados NULL.
- Identifique a coluna da consulta externa que será referenciada pela subconsulta correlacionada. Declare um alias para a tabela que é a origem da coluna na consulta externa.
- Identifique a coluna da tabela interna que será comparada à coluna da tabela externa. Crie um alias para a tabela de origem, como você fez para a consulta externa.
- Escreva a consulta interna para recuperar valores de sua origem, com base no valor de entrada da consulta externa. Por exemplo, use a coluna externa na cláusula WHERE da consulta interna.
A correlação entre as consultas internas e externas ocorre quando o valor externo é referenciado pela consulta interna para comparação. É essa correlação que fornece o nome da subconsulta.
Trabalhando com EXISTS
Além de recuperar valores de uma subconsulta, o T-SQL fornece um mecanismo para verificar se algum resultado seria retornado de uma consulta. O predicado EXISTS determina se existem linhas que atendem a uma condição especificada, mas, em vez de retorná-las, retorna TRUE ou FALSE. Essa técnica é útil para validar dados sem incorrer na sobrecarga de recuperação e processamento dos resultados.
Quando uma subconsulta está relacionada à consulta externa usando o predicado EXISTS, o SQL Server manipula os resultados da subconsulta de forma especial. Em vez de recuperar um valor escalar ou uma lista de valores múltiplos da subconsulta, EXISTS simplesmente verifica se há alguma linha no resultado.
Conceitualmente, um predicado EXISTS é equivalente a recuperar os resultados, contar as linhas retornadas e comparar a contagem com zero. Compare as consultas a seguir, que retornarão detalhes sobre os clientes que fizeram pedidos:
A primeira consulta de exemplo usa COUNT em uma subconsulta:
SELECT CustomerID, CompanyName, EmailAddress
FROM Sales.Customer AS c
WHERE
(SELECT COUNT(*)
FROM Sales.SalesOrderHeader AS o
WHERE o.CustomerID = c.CustomerID) > 0;
A segunda consulta, que retorna os mesmos resultados, usa EXISTS:
SELECT CustomerID, CompanyName, EmailAddress
FROM Sales.Customer AS c
WHERE EXISTS
(SELECT *
FROM Sales.SalesOrderHeader AS o
WHERE o.CustomerID = c.CustomerID);
No primeiro exemplo, a subconsulta deve contar cada ocorrência de cada CustID encontrada na tabela Sales.SalesOrderHeader e comparar os resultados da contagem com zero, simplesmente para indicar que o cliente fez pedidos.
Na segunda consulta, EXISTS retorna TRUE para um CustID assim que uma ordem relevante tiver sido encontrada na tabela Sales.SalesOrderHeader. A contabilidade completa de cada ocorrência é desnecessária. Observe também que, com o formulário EXISTS, a subconsulta não está restrita a retornar uma única coluna. Aqui, temos SELECT *. As colunas retornadas são irrelevantes porque estamos verificando apenas se alguma linha é retornada, não quais valores estão nessas linhas.
Da perspectiva do processamento lógico, os dois formulários de consulta são equivalentes. De uma perspectiva de desempenho, o mecanismo de banco de dados pode tratar as consultas de maneira diferente ao otimizá-las para execução. Considere testar cada um para seu próprio uso.
Observação
Se você está convertendo uma subconsulta que usa COUNT(*) em uma que usa EXISTS, verifique se a subconsulta usa SELECT * e não SELECT COUNT(*). SELECT COUNT(*) sempre retorna uma linha, portanto, EXISTS sempre retornará TRUE.
Outra aplicação útil de EXISTS é negar a subconsulta com NOT, como no exemplo a seguir, que retornará qualquer cliente que nunca tenha feito um pedido:
SELECT CustomerID, CompanyName, EmailAddress
FROM SalesLT.Customer AS c
WHERE NOT EXISTS
(SELECT *
FROM SalesLT.SalesOrderHeader AS o
WHERE o.CustomerID = c.CustomerID);
O SQL Server não precisará retornar dados sobre os pedidos relacionados para clientes que tenham feito pedidos. Se um custid for encontrado na tabela Sales.SalesOrderHeader, NOT EXISTS será avaliado como FALSE e a avaliação será concluída rapidamente.
Para escrever consultas que usam EXISTS com subconsultas, considere as seguintes diretrizes:
- A palavra-chave EXISTS vem logo após WHERE. Nenhum nome de coluna (ou outra expressão) a precede, a menos que NOT também seja usado.
- Na subconsulta, use SELECT*. Nenhuma linha é retornada pela subconsulta, portanto, nenhuma coluna precisa ser especificada.