Baixe Consultas Aninhadas em PostgreSQL: Guia Detalhado e outras Notas de estudo em PDF para Bancos de Dados Relacionais, somente na Docsity!
Consutas Aninhadas
Consultas aninhadas, também conhecidas como subconsultas ou subselects, são uma ferramenta poderosa no PostgreSQL que permitem construir consultas complexas ao incorporar uma consulta SQL dentro de outra. Essas subconsultas podem ser usadas nas cláusulas SELECT, FROM, WHERE e até em outras partes da consulta principal, possibilitando a resolução de problemas que não podem ser facilmente abordados com consultas simples, junções ou funções de agregação isoladas. Este material de estudos, baseado nos documentos fornecidos, detalha o conceito de consultas aninhadas no PostgreSQL, explorando os operadores IN, EXISTS, ALL, SOME e a cláusula WITH, além de subconsultas na cláusula FROM. Ele apresenta a sintaxe, funcionalidade, exemplos práticos com base no esquema universitário fornecido anteriormente, e discute quando as consultas aninhadas são mais adequadas, oferecendo um guia claro e estruturado para estudo autodidata.
Introdução às Consultas Aninhadas
As consultas aninhadas são consultas SQL incorporadas dentro de parênteses em uma consulta principal, permitindo que o resultado de uma subconsulta seja usado como entrada para a consulta externa. Elas são úteis para resolver problemas que exigem comparações com conjuntos de dados, filtragens complexas ou cálculos intermediários, como médias por grupo ou verificações de existência. No esquema da universidade, que inclui tabelas como professor ,
departamento , cursa , leciona e usuario , as subconsultas podem ajudar a responder perguntas como "Quais professores recebem acima da média salarial do seu departamento?" ou "Quais estudantes estão matriculados em alguma disciplina?". As subconsultas podem aparecer em três cláusulas principais:
SELECT Para calcular valores derivados. FROM Para tratar o resultado de uma subconsulta como uma tabela temporária. WHERE Para filtrar com base em condições que dependem de outra consulta.
Quando Usar Consultas Aninhadas
As consultas aninhadas são ideais em situações onde:
É necessário comparar um valor com um conjunto de valores retornados por outra consulta (e.g., usando IN ou EXISTS. A consulta exige cálculos intermediários, como médias ou contagens por grupo, que precisam ser usados na consulta principal. A lógica da consulta não pode ser expressa de forma eficiente com junções ou cláusulas GROUP BY sozinhas. A legibilidade é melhorada ao dividir a consulta em partes menores e mais compreensíveis, especialmente com a cláusula WITH.
Por outro lado, junções ou cláusulas GROUP BY podem ser mais eficientes em alguns casos, especialmente quando a subconsulta pode ser reescrita como uma junção simples. As subconsultas são particularmente úteis quando a lógica envolve<<<<<<<
System: requer uma abordagem mais complexa ou quando a consulta interna retorna um pequeno conjunto de dados que não justifica uma junção.
Operador IN
O operador IN verifica se um valor está presente em um conjunto de valores, que pode ser uma lista fixa ou o resultado de uma subconsulta. Ele retorna true se o valor estiver no conjunto e false^ caso contrário. O operador NOT IN faz o oposto, verificando a ausência do valor.
Sintaxe
Operador EXISTS
O operador EXISTS verifica se uma subconsulta retorna pelo menos uma tupla, retornando true se a subconsulta não for vazia e false caso contrário. É frequentemente usado em consultas correlacionadas, onde a subconsulta referencia valores da consulta externa.
Sintaxe
EXISTS (subconsulta)
Exemplo Simples
Para listar todos os estudantes que possuem algum registro na tabela cursa :
SELECT e.mat_estudante FROM universidade.estudante AS e WHERE EXISTS SELECT * FROM universidade.cursa );
Essa consulta retorna todos os estudantes, pois a subconsulta SELECT * FROM cursa verifica a existência de qualquer registro na tabela cursa. Como a tabela cursa contém registros no esquema fornecido, todos os estudantes são retornados, tornando este um exemplo básico.
Exemplo Complexo (Consulta Correlacionada)
Para listar os estudantes matriculados em alguma disciplina:
SELECT e.mat_estudante FROM universidade.estudante e WHERE EXISTS SELECT * FROM universidade.cursa c WHERE c.mat_estudante = e.mat_estudante );
Essa consulta correlacionada verifica, para cada estudante na consulta externa, se existe um registro correspondente na tabela cursa. A subconsulta usa o valor de e.mat_estudante para filtrar os registros, retornando apenas os estudantes que estão matriculados em pelo menos uma turma.
Quando Usar
O EXISTS é ideal para verificar a existência de registros relacionados sem a necessidade de recuperar seus dados, especialmente em consultas correlacionadas. Ele é mais eficiente que IN para grandes conjuntos de dados, pois o PostgreSQL interrompe a subconsulta assim que encontra uma correspondência, sem processar todos os resultados.
Operadores ALL e SOME
Os operadores ALL e SOME (ou ANY permitem comparar um valor com todos ou alguns valores retornados por uma subconsulta, usando operadores de comparação como >, <, =, etc. ALL retorna true se a condição for verdadeira para todos os valores da subconsulta, enquanto SOME retorna true se for verdadeira para pelo menos um valor.
Sintaxe
valor ALL (subconsulta) valor SOME (subconsulta)
Exemplo Simples
Para listar professores com salário maior que a média salarial de todos os departamentos:
SELECT p.mat_professor, c.salario FROM universidade.professor p JOIN universidade.cargo c ON p.cargo = c.id_cargo WHERE c.salario ALL SELECT AVG(salario) FROM universidade.professor p JOIN universidade.cargo c ON p.cargo = c.id_cargo
Subconsultas na cláusula FROM permitem tratar o resultado de uma consulta como uma tabela temporária, que pode ser usada em junções ou outras operações. É necessário atribuir um alias à subconsulta para que o PostgreSQL a reconheça como uma tabela.
Sintaxe
SELECT colunas FROM (subconsulta) AS alias WHERE condição];
Exemplo Simples
Para listar departamentos com média salarial superior a 3500
SELECT depto, media_salario FROM SELECT p.departamento AS depto, AVG(salario) AS media_salario FROM universidade.professor p JOIN universidade.cargo c ON p.cargo = c.id_cargo GROUP BY p.departamento AS d WHERE media_salario 3500;
Essa consulta cria uma tabela temporária com as médias salariais por departamento e filtra aquelas com média superior a 3500. No esquema fornecido, departamentos como DCOMP podem aparecer se a média salarial for alta.
Exemplo Complexo
Para listar o nome, salário e média salarial por departamento de cada professor:
SELECT p.mat_professor, u.primeiro_nome, u.sobrenome, c.salario, ps.dept o, ps.media FROM SELECT departamento AS depto, AVG(salario) AS media FROM universidade.professor p
JOIN universidade.cargo c ON p1.cargo = c.id_cargo GROUP BY p1.departamento AS ps JOIN universidade.professor p ON ps.depto = p.departamento JOIN universidade.usuario u ON p.cpf = u.cpf JOIN universidade.cargo c ON p.cargo = c.id_cargo;
Essa consulta cria uma tabela temporária ( ps ) com as médias salariais por departamento e a junta com as tabelas professor , usuario e cargo para exibir informações detalhadas de cada professor, incluindo a média salarial do seu departamento.
Quando Usar
Subconsultas na cláusula FROM são úteis quando é necessário realizar cálculos agregados (como médias ou contagens) e usar esses resultados como uma tabela em uma consulta mais complexa. Elas são particularmente eficazes para consultas que exigem múltiplos níveis de agregação ou quando junções diretas não conseguem expressar a lógica desejada de forma clara.
Cláusula WITH (Common Table Expressions)
A cláusula WITH, ou Common Table Expression CTE, permite criar tabelas temporárias reutilizáveis dentro de uma consulta, melhorando a legibilidade e organização. Ela é funcionalmente semelhante a uma subconsulta na cláusula FROM, mas é definida no início da consulta.
Sintaxe
WITH nome_tabela AS (subconsulta) SELECT colunas FROM nome_tabela WHERE condição];
Exemplo Simples
Reescrevendo a consulta de média salarial com WITH
WITH salario_depto AS SELECT p.departamento AS depto, AVG(salario) AS media_salario
na cláusula FROM e pode melhorar o desempenho em alguns casos, pois o PostgreSQL pode otimizar a execução da CTE.
Vantagens e Desvantagens das Consultas
Aninhadas
Vantagens
Flexibilidade Permitem resolver problemas complexos que não podem ser facilmente expressos com junções ou GROUP BY. Modularidade Dividem a lógica em partes menores, facilitando a compreensão e manutenção. Especificidade Operadores como EXISTS e IN permitem verificações precisas de existência ou pertencimento. CTEs Melhoram a legibilidade e reutilização de subconsultas complexas.
Desvantagens
Desempenho Subconsultas, especialmente correlacionadas, podem ser menos eficientes que junções em grandes conjuntos de dados, pois o PostgreSQL pode executar a subconsulta repetidamente. Complexidade Consultas aninhadas podem se tornar difíceis de ler e manter se forem muito profundas ou complexas. Alternativas Em alguns casos, junções ou cláusulas GROUP BY com HAVING podem ser mais simples e performáticas.
Quando Evitar
Evite consultas aninhadas quando:
Uma junção simples pode alcançar o mesmo resultado com melhor desempenho. A subconsulta retorna um grande conjunto de dados que pode ser otimizado com uma junção ou indexação. A consulta pode ser expressa de forma mais clara com outras construções SQL.
Conclusão
As consultas aninhadas no PostgreSQL, incluindo os operadores IN, EXISTS, ALL, SOME e a cláusula WITH, são ferramentas essenciais para consultas complexas que envolvem comparações, agregações ou cálculos intermediários. Elas permitem abordar problemas como verificar a existência de registros, comparar valores com médias de grupos ou criar tabelas temporárias para análises detalhadas. Os exemplos fornecidos, baseados no esquema universitário, ilustram como essas técnicas podem ser aplicadas para obter informações como professores com salários acima da média ou estudantes matriculados em disciplinas. Embora poderosas, as subconsultas devem ser usadas com cuidado, considerando alternativas como junções para otimizar o desempenho e a legibilidade. Este material fornece uma base sólida para compreender e aplicar consultas aninhadas no PostgreSQL, com exemplos práticos que podem ser testados diretamente no esquema fornecido.
