Usar subconsultas independentes ou correlacionadas
Anteriormente, analisávamos subconsultas independentes; na qual a consulta interna é independente da consulta externa, é executada uma vez e retorna seus resultados para a consulta externa. O T-SQL também suporta 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
Como as subconsultas independentes, as subconsultas correlacionadas são instruções SELECT aninhadas em uma consulta externa. As subconsultas correlacionadas também podem ser escalares ou multivaloradas. Eles geralmente são usados quando a consulta interna precisa fazer referência a um valor na consulta externa.
No entanto, ao contrário das subconsultas independentes, há algumas considerações especiais ao usar subconsultas correlacionadas:
- As 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 independentes, que são processadas uma vez, as subconsultas correlacionadas serão executadas 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 o ID de pedido máximo para o cliente referenciado nessa linha, e a consulta externa verifica se a linha que está olhando é a linha com esse ID de ordem.
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 para manipular 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 com a coluna da tabela externa. Crie um alias para a tabela de origem, como 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 interna e externa ocorre quando o valor externo é referenciado pela consulta interna para comparação. É essa correlação que dá nome à subconsulta.
Trabalhar com o 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 atendam a uma condição especificada, mas em vez de retorná-las, ele retorna TRUE ou FALSE. Esta técnica é útil para validar dados sem incorrer na sobrecarga de recuperar e processar os resultados.
Quando uma subconsulta está relacionada à consulta externa usando o predicado EXISTS, o SQL Server manipula os resultados da subconsulta de uma maneira especial. Em vez de recuperar um valor escalar ou uma lista de vários valores da subconsulta, EXISTS simplesmente verifica se há linhas no resultado.
Conceitualmente, um predicado EXISTS é equivalente a recuperar os resultados, contar as linhas retornadas e comparar a contagem com zero. Compare as seguintes consultas, 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 todas as ocorrências de cada custid encontrado 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 um pedido relevante for encontrado na tabela Sales.SalesOrderHeader . Uma contabilidade completa de cada ocorrência é desnecessária. Observe também que, com o formulário EXISTS, a subconsulta não se restringe a retornar uma única coluna. Aqui, temos SELECT *. As colunas retornadas são irrelevantes porque estamos apenas verificando se alguma linha é retornada, não quais valores estão nessas linhas.
Do ponto de vista do processamento lógico, os dois formulários de consulta são equivalentes. Do ponto de vista do desempenho, o mecanismo de banco de dados pode tratar as consultas de forma diferente, pois as otimiza para execução. Considere testar cada um para seu próprio uso.
Nota
Se você estiver convertendo uma subconsulta usando COUNT(*) em uma usando EXISTS, verifique se a subconsulta usa SELECT * e não SELECT COUNT(*). SELECT COUNT(*) sempre retorna uma linha, então o EXISTS sempre retornará TRUE.
Outra aplicação útil do 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 fizeram 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 EXISTE segue diretamente ONDE. Nenhum nome de coluna (ou outra expressão) o precede, a menos que NOT também seja usado.
- Na subconsulta, use SELECT *. Nenhuma linha é retornada pela subconsulta, portanto, nenhuma coluna precisa ser especificada.