Subconsultas (subconsultas)

Você pode usar uma instrução SELECT ou uma consulta para retornar registros que serão usados como critérios para outra instrução ou consulta SELECT. A consulta externa é chamada de consulta pai e a consulta interna é chamada de subconsulta. A finalidade de uma subconsulta é retornar resultados para a consulta externa. Em outras palavras, a instrução de consulta interna deve retornar a coluna ou colunas usadas nos critérios da consulta externa.

A forma mais simples de uma subconsulta é uma consulta que retorna apenas uma coluna. A consulta pai pode usar os resultados dessa subconsulta com o “=”. Sintaxe:

SELECT <ColumnName> FROM <table> WHERE <ColumnName> = (SELECT <ColumnName> FROM <Table> WHERE <ColumnName> = <Condition>)

Em uma subconsulta, a instrução SELECT mais interna é executada primeiro e seus resultados são passados como critérios para a instrução SELECT externa.

Considere uma situação em que é necessário determinar as datas de vencimento e entrega dos pedidos mais recentes.

Por exemplo:

SELECT DueDate,ShipDate FROM Sales.SalesOrderHeader WHERE Sales.SalesOrderHeader.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)

Aqui, uma subconsulta foi usada para obter a saída desejada. A consulta ou subconsulta interna recupera a data do pedido mais recente. Esse resultado é então passado para uma consulta externa, mostrando a data de vencimento e a data de entrega de todos os pedidos, pedidos que foram atendidos naquele dia específico.

Com base nos resultados retornados pela consulta interna, uma subconsulta pode ser classificada como uma subconsulta escalar ( scalar ) ou uma consulta multivalorada ( multivalorada ).
As seguintes descrições estão disponíveis:

• A subconsulta escalar retorna um único valor. Aqui, a consulta externa deve ser gravada para processar um único resultado.
• A subconsulta com vários valores retorna o mesmo resultado que uma tabela de coluna única. Aqui, a consulta externa deve ser escrita para lidar com vários resultados possíveis.

Trabalhando com consultas de vários valores

Se o operador “=” for usado com uma subconsulta, a subconsulta deverá retornar um único valor escalar. Se mais de um valor for retornado, ocorrerá um erro e a consulta não será processada. Nesses casos, as palavras-chave ANY, ALL, IN e EXISTS podem ser usadas com a cláusula WHERE da instrução SELECT quando a consulta retorna uma coluna, mas uma ou mais linhas.

Essas palavras-chave, também conhecidas como predicados, são usadas com consultas de vários valores. Por exemplo, considere que todos os nomes e sobrenomes dos funcionários com o cargo de ‘Gerente de Pesquisa e Desenvolvimento’ devem ser exibidos. Aqui, a consulta interna pode retornar mais de uma linha, pois pode haver vários funcionários com esse cargo. Para garantir que a consulta externa possa usar os resultados da consulta interna, a palavra-chave IN deverá ser usada.

Por exemplo:

SELECT FirstName,LastName FROM Person.Person WHERE Person.Person.BusinessEntityID IN (SELECT BusinessEntityID FROM HumanResources.Employee WHERE JobTitle='Research and Development Manager');

Qual das palavras-chave SOME ou ANY é avaliada como verdadeira se o resultado for uma consulta interna contendo uma linha igual à comparação. Compara um valor escalar com uma coluna de valores. SOME e ANY são equivalentes, ambos retornam o mesmo resultado. Raramente são usados.

Algumas diretrizes ao usar subconsultas:

• Tipos de dados ntext, text, image não podem ser usados em SELECT em subconsultas
• A lista SELECT da subconsulta introduzida com o operador de comparação pode ter apenas uma expressão ou nome de coluna.
• Subconsultas ao trabalhar com operadores de comparação com palavras-chave ANY ou ALL não podem usar cláusulas GROUP BY e HAVING
• Você não pode usar a palavra-chave DISTINCT com subconsultas que contenham uma cláusula GROUP BY .
• Você pode especificar ORDER BY somente quando TOP também for especificado ao usar a subconsulta

Além da subconsulta escalar e multivalorada, você também pode escolher entre subconsultas independentes e subconsultas correlacionadas. Eles estão definidos da seguinte forma:

• A subconsulta autônoma é escrita como uma consulta autônoma, sem nenhuma dependência da consulta externa. Uma subconsulta independente é processada uma vez quando a consulta externa é executada e seus resultados são passados para a consulta externa.
• As subconsultas relacionadas referem-se a uma ou mais colunas da consulta externa e, portanto, dependem da consulta externa. As subconsultas relacionadas não podem ser executadas separadamente da consulta externa.

A palavra-chave EXISTS é usada com a subconsulta para verificar a existência da linha retornada pela subconsulta. Na verdade, ele não retorna dados e retorna TRUE ou FALSE

A sintaxe da subconsulta que contém a palavra-chave EXISTS :

SELECT <ColumnName> FROM <table> WHERE [NOT] EXISTS (subquery_statement)

Por exemplo:

use AdventureWorks2019 go SELECT FirstName,LastName FROM Person.Person AS p WHERE EXISTS (SELECT * FROM HumanResources.Employee AS e WHERE JobTitle='Research and Development Manager' AND p.BusinessEntityID=e.BusinessEntityID )

Aqui, a subconsulta interna recupera todos os registros que correspondem ao cargo de ‘Gerente de Pesquisa e Desenvolvimento’ e o BusinessEntityID tem um BusinessEntityID que corresponde a esse registro na tabela Person. Se não houver registros que correspondam a essas duas condições, a subconsulta interna não retornará nenhuma linha. No entanto, o código abaixo retornará duas linhas porque as condições fornecidas foram atendidas.

Da mesma forma, você pode usar a palavra-chave NOT EXISTS , que é a cláusula negativa de EXISTS .

Subconsultas aninhadas (subconsultas aninhadas)

Uma subconsulta definida em outra subconsulta é chamada de subconsultas aninhadas . Considerando a situação em que você deseja recuperar e exibir os nomes de pessoas do Canadá no adventureworks2019, não há uma maneira direta de recuperar essas informações porque a tabela Sales.SalesTerritory não está relacionada à tabela Person.Person. Portanto, uma subconsulta aninhada pode ser a solução.

Por exemplo:

SELECT LastName, FirstName FROM Person.Person WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE TerritoryID IN (SELECT TerritoryID FROM Sales.SalesTerritory WHERE Name='Canada'))

Consultas correlacionadas (consultas relacionadas)

Em Consultas Múltiplas contendo subconsultas, a subconsulta é avaliada apenas uma vez para fornecer os valores exigidos pela consulta pai. Isso ocorre porque, na maioria das consultas, a subconsulta não se refere à consulta pai, portanto, o valor na subconsulta permanece o mesmo.
No entanto, se a subconsulta fizer referência à consulta pai, a subconsulta deverá ser reavaliada para cada iteração na consulta pai. Isso ocorre porque os critérios de pesquisa na subconsulta dependem do valor de um registro específico na consulta pai.
Quando uma subconsulta recebe parâmetros da consulta pai, ela é chamada de subconsulta relacionada. Considere uma situação em que você deseja recuperar todos os BusinessEntityIDs de pessoas cujas informações de contato foram modificadas pela última vez após 2012. Para fazer isso, você pode usar a subconsulta relacionada da seguinte maneira:

SELECT a.BusinessEntityID FROM Person.BusinessEntityContact a WHERE a.ContactTypeID IN (SELECT c.ContactTypeID FROM Person.ContactType c WHERE YEAR (a.ModifiedDate) >= 12)

No snippet de código, a consulta interna recupera os IDs de tipo de contato para todas as pessoas cujas informações de contato foram modificadas pela última vez após 2012. Esses resultados são então passados para a consulta externa, que resulta em Isso corresponde ao ContactTypeID na tabela Person.BusinessEntityContact.