Consultas Aninhadas
Referência: CRIVELINI, Wagner. Usando linguagem SQL para geração de relatórios. SQL Magazine, ed. 94, ano 12, p. 15 - 21.
As consultas aninhadas podem ser em:
Visões.
Consultas aninhadas na clausula WHERE.
Consultas aninhadas na clausula FROM.
Consultas aninhadas na clausula SELECT.
Na teoria de banco de dados, uma visão (view) consiste de uma consulta armazenada acessível como uma tabela virtual em um banco de dados relacional ou um conjunto de documentos em um banco de dados relacional ou um conjunto de documentos em um banco de dados orientado a documentos, composto pelo conjunto de resultados de uma consulta.
As visões funcionam como declarações de SELECT precompiladas, que são executadas quando o objeto correspondente é solicitado.
Visões podem oferecer vantagens sobre tabelas nos seguintes aspectos:
Visões podem representar apenas um subconjunto dos dados contidos em uma tabela;
Visões podem juntar e simplificar várias tabelas em uma única tabela virtual;
Visões podem funcionar como tabelas agregadas, através dos mecanismos de agregação do banco de dados (sum, average, ext.) e apresenta os resultados calculados como parte dos dados;
Visões podem esconder a complexidade dos dados, por exemplo, uma visão poderia ser chamar Vendas2000 ou Vendas2011, particionando transparentemente a tabela básica real;
Visões ocupam muito pouco espaço de armazenamento, o banco de dados contém apenas definições de uma visão e não uma copia de todos os dados que ela apresenta;
Dependendo do SGBD utilizado, visões podem fornecer a segurança extra;
Dependendo do SGBD utilizado, visões podem fornecer a segurança extra;
Visões podem limitar o grau de exposição de uma ou mais tabelas para o mundo exterior;
Os usuários do banco de dados podem manipular visões aninhadas, assim, uma visão pode agregar dados de outras visões;
O padrão SQL ANSI 2003 não permite que uma cláusula ORDER BY em uma consulta SQL na instrução CREATE VIEW assim como não é permitido na instrução CRAETE TABLE.
No entanto, alguns SGBs (como Oracle e SQL Server) permitem uma visão a ser criada com uma cláusula ORDER BY em uma subquery, afetando como os dados são exibidos.
Para os administradores de banco de dados (DBAs), as visões são uns excelentes recursos para gerenciamento da segurança dos dados.
Você pode usar subconsultas para os seguintes fins:
Definir um conjunto de linhas que precisam ser inseridos em uma tabela;
Definir um conjunto de resultados que será usado para criação de uma visão;
Definir um ou mais valores de uma instrução de atualização (UPDATE);
Fornecimento de valores para as clausulas WHERE, HAVING e START WITH para instruções SELECT, UPDATE e DELETE.
Consultas aninhadas na clausula FROM.
Em muitos relatórios, é necessário cruzar os dados de uma consulta com os dados de outro objeto, como uma tabela ou visão. Nestes casos, uma solução é usar consultas aninhadas na clausula FROM. Para decidir se você deve criar a consulta aninhada no FROM ou em outra clausula qualquer, você tem que analisar se a consulta precisa retornar campos que serão usados na clausula SELECT. Neste caso, ela tem que fazer parte da clausula FROM.
E por que é necessário usarmos uma consulta aninhada na clausula FROM? Ora, porque precisamos destes dois campos para calcularmos a participação dos produtos da demanda total.
Exemplo:
SELECT S.Produto, S.Demanda, Q.DemandaTotal, S.Demanda/Q.DemandaTotal * 100 AS Participacao
FROM viewDemandaProdutosMesPassado S,
(SELECT SUM(X.Demanda) as DemandaTotal FROM viewsDemandaProdutoMesPassado X) Q
ORDER BY S.Demanda DESC
FETCH FIRST 5 ROWS ONLY;
Veja que usamos a visão viewDemandaMesPassado duas vezes na cláusulas FROM, uma diretamente e a segunda como parte de um subconsulta.
Consultas aninhadas na clausula SELECT.
Em geral, usamos a subconsulta nesta cláusula quando precisamos executá-la tomando como parâmetro valores que são retornados pela consulta mais externa. Ou seja, a subconsulta na clausula SELCT é reexecutada para registros da consulta externa! Portanto, fica evidente que essa estratégia é um tanto perigosa e tem potencial para “derrubar” o seu servidor de banco de dados.
Lembre-se: A contagem, isto é, a subconsulta, é repetida para cada registro da consulta externa.
De maneira geral, quando você imaginar que precisa usar consultas aninhadas na clausula SELECT, fique atento a três condições:
Se sua subconsulta precisa mesmo interagir com os registros da consulta externa;
Atendida a condição anterior, verifique se esta subconsulta pode ou não ser substituída por uma junção de tabelas;
Se passar nos dois primeiros testes, fique atento com os números de registros retornados pela consulta externa.
Em muitos relatórios, é necessário cruzar os dados de uma consulta com os dados de outro objeto, como uma tabela ou visão. Nestes casos, uma solução é usar consultas aninhadas na clausula FROM. Para decidir se você deve criar a consulta aninhada no FROM ou em outra clausula qualquer, você tem que analisar se a consulta precisa retornar campos que serão usados na clausula SELECT. Neste caso, ela tem que fazer parte da clausula FROM.
E por que é necessário usarmos uma consulta aninhada na clausula FROM? Ora, porque precisamos destes dois campos para calcularmos a participação dos produtos da demanda total.
Exemplo:
SELECT S.Produto, S.Demanda, Q.DemandaTotal, S.Demanda/Q.DemandaTotal * 100 AS Participacao
FROM viewDemandaProdutosMesPassado S,
(SELECT SUM(X.Demanda) as DemandaTotal FROM viewsDemandaProdutoMesPassado X) Q
ORDER BY S.Demanda DESC
FETCH FIRST 5 ROWS ONLY;
Veja que usamos a visão viewDemandaMesPassado duas vezes na cláusulas FROM, uma diretamente e a segunda como parte de um subconsulta.
Consultas aninhadas na clausula SELECT.
Em geral, usamos a subconsulta nesta cláusula quando precisamos executá-la tomando como parâmetro valores que são retornados pela consulta mais externa. Ou seja, a subconsulta na clausula SELCT é reexecutada para registros da consulta externa! Portanto, fica evidente que essa estratégia é um tanto perigosa e tem potencial para “derrubar” o seu servidor de banco de dados.
Lembre-se: A contagem, isto é, a subconsulta, é repetida para cada registro da consulta externa.
De maneira geral, quando você imaginar que precisa usar consultas aninhadas na clausula SELECT, fique atento a três condições:
Se sua subconsulta precisa mesmo interagir com os registros da consulta externa;
Atendida a condição anterior, verifique se esta subconsulta pode ou não ser substituída por uma junção de tabelas;
Se passar nos dois primeiros testes, fique atento com os números de registros retornados pela consulta externa.
É uma consulta envolvida por outra consulta. Também é chamada de consulta interna.
Uma subconsulta deve retornar um valor único. IN é a exceção. Na maioria das vezes, subconsultas precisam retornar um só valor para funcionar.
Subconsulta correcalcionada é quando a consulta interna depende da consulta externa.
SELECT mc.primeiro_nome, mc.sobrenome
FROM meus_contatos As mc
WHERE 3 = (SELECT COUNT(*) FROM contato_interesse
WHEER id.contato = mc.id_contato);
Consulta Correlacionada com NOT EXISTS.
SELECT mc.primeiro_nome primeironome,
mc.sobrenome sobrenome,
mc.e-mail email
FROM meus_contatos mc
WHERE NOT EXISTS
(SELECT * FROM emprego_atual ea
WHERE mc.id_contato = ea.id_contato);
Subconsulta não correlacionada usa IN e NOT IN para testar se os valores retornados na subconsulta são integrantes de um conjunto (ou não). consulta interna funciona sozinha sem se referir a nada da consulta externa, podendo ser executada de forma independente.
SELECT mc.primeiro_nome, mc.sobrenome, ea.cargo
FROM emprego_atual ea NATURAL JOIN meus_contatos mc
WHERE ea.cargo NOT IN (SELECT cargo FROM lista_empregos);
Eemplo1:
Consulta Externa:
SELECT mc.primeiro_nome,
mc. sobrenome,
mc.telefone,
ea.cargo
FROM emprego_atual AS ea NATURAL JOIN meus_contatos AS mc
WHERE ea.cargo IN (‘Cozinheiro’, ‘Cabelereiro’, ‘Garçom’, ‘Web Designer’, ‘Programador Web’);
Consulta Interna:
SELECT cargo lista_empregos
GROUP BY cargo ORDER BY cargo;
Consulta Externa + Consulta Interna = Consulta com Subconsulta
SELECT mc.primeiro_nome,
mc.sobrenome,
mc.telefone,
ea.cargo
FROM emprego_atual As ea NATURAL JOIN meus_contatos as mc
WHERE ea.cargo IN (SELECT cargo FROM lista_emprego);
Exemplo2:
Query com subconsulta:
SELECT sobrenome, primeiro_nome
FROM meus_contatos
WHERE cep = (SELECT cep FROM c_e_p
WHERE cidade = ‘Menphis’
AND estado = ‘TN’)
Query sem subconsulta:
SELECT sobrenome, primeiro_nome
FROM meus_contatos mc
NATURAL JOIN c_e_p
WHERE cidade = ‘Menphis’
AND estado = ‘TN’)
Responda Pergunta:
Quem ganha mais dinheiro de toda a tabela “meus_contatos”?
SELECT MAX(salario) FROM emprego_atual;
Obs.: Retorna o maior valor da coluna indicada entre parêntese.
SELECT mc.primeiro_nome,
mc.sobrenome
FROM meus_contatos As mc;
Obs.: Retorna o primeiro nome e sobrenome.
SELECT mc.primeiro_nome,
mc.sobrenome,
ea.salario
FROM meus_contatos AS mc NATURAL JOIN emprego_atual AS ea;
Obs.: Relacionando as duas consultas. Retorna o salario de cada um.
SELECT mc.primeiro_nome,
mc.sobrenome,
ea.salario
FROM meus_contatos AS mc NATURAL JOIN emprego_atual AS ea;
WHERE ea.salario = (SELECT MAX(salario) FROM emprego_atual);
Obs.: Adicionando a clausula WHERE para conecta-las.
Uma subconsulta como uma coluna no comando SELECT. Se uma subconsulta e utilizada como a expressão de uma coluna em um comando SELECT, ela poderá apenas retornar o valor de uma coluna.
SELECT mc.primeiro_nome, mc.sobrenome,
(SELECT estado
FROM c_e_p
WHERE mc.cep = cep) As estado
FROM meus_contatos mc;
Exemplo3:
Escreva uma consulta para obter o salário médio para o cargo de Programador em Web de sua tabela emprego_atual.
SELECT AVG(salario) FROM emprego_atual WHERE cargo = ‘Programador em Web”;
Escreva uma consulta para obter o primeiro nome, sobrenome e salário de todos os Programadores em Web de sua tabela emprego_atual.
SELECT mc.primeiro_nome, mc.sobreno, ea.salario
FROM meus_contatos mc NATURAL JOIN emprego_atual ea
WHERE ea.cargo = ‘Programador em Web’;
Combine as duas consultas. Utilize a subconsulta como parte do comando SELECT para uma lista de colunas.
SELECT mc.primeiro_nome, mc.sobreno, ea.salario,
ea.salario – (SELECT AVG(salario) FROM emprego_atual WHERE ea.cargo = ‘Programador em Web’)
FROM meus_contatos mc NATURAL JOIN emprego_atual ea
WHERE ea.cargo = ‘Programador em Web’;
Exemplos4:
Liste qualquer pessoa que viva no mesmo CEP que a pessoa que atualmente possui o maior salário.
SELECT mc.primeiro_nome, mc.sobrenome
FROM meus_contatos mc
WHERE cep IN (SELECT mc.cep FROM meus_contatos NATURAL JOIN
Emprego_atual ea WHERE ea.salario (SELECT MAX(salario) FROM emprego_atual ea));
Escreva uma consulta que retorne o e-mail das pessoas que tenham, ao menos, um interesse, mas que não se encontram na tabela emprego atual.
SELECT mc.e-mail FROM meus_contatos mc
WHERE EXISTS
(SELECT * FROM contato_interesse ci WHERE (mc.id_contato = ci.id_contato)
AND NOT EXISTS
(SELECT * FROM emprego_atual ea
WHERE mc.id_contato = ea.id.contato);