Subconsultas

Autor

Douglas Braga

Uma subconsulta (ou subquery) é uma consulta SQL aninhada dentro de outra consulta. Ela pode aparecer na cláusula WHERE, na cláusula FROM ou na cláusula SELECT. Subconsultas permitem expressar perguntas complexas que não podem ser respondidas com uma única consulta simples.

IN e NOT IN

O predicado IN verifica se um valor pertence ao conjunto retornado por uma subconsulta.

Alunos matriculados em disciplinas de Engenharia de Software:

-- Passo 1 (subconsulta): ids das disciplinas de ENS (id_curso = 311)
-- Passo 2 (consulta principal): alunos que cursaram essas disciplinas
SELECT DISTINCT a.nome, a.id_curso
FROM   aluno a
WHERE  a.id_aluno IN (
    SELECT md.id_aluno
    FROM   matricula_disciplina md
    JOIN   disciplina d ON d.id_disciplina = md.id_disciplina
    WHERE  d.id_curso = 311
)
ORDER BY a.nome;
5 records
nome id_curso
Diego Correia 311
Isabela Freitas 311
Juliana Nunes 311
Larissa Araújo 311
Matheus Silva 311

NOT IN: disciplinas que nenhum aluno cursou:

SELECT id_disciplina, nome_disciplina
FROM   disciplina
WHERE  id_disciplina NOT IN (
    SELECT DISTINCT id_disciplina
    FROM   matricula_disciplina
)
ORDER BY id_disciplina;
8 records
id_disciplina nome_disciplina
1110601 Economia Brasileira
1110602 Orçamento e Finanças Públicas
1110801 Monografia
1110802 Desenvolvimento Econômico
2110801 Tecnologia Educacional - Design
2110802 Estatística Aplicada
3110801 Bases da Eng. de Software 8
3110802 Projeto Aplicado 8 - Sistema em Tempo Real

NOT IN: disciplinas sem nenhum pré-requisito:

SELECT id_disciplina, nome_disciplina, id_curso, semestre
FROM   disciplina
WHERE  id_disciplina NOT IN (
    SELECT id_disciplina
    FROM   prereq
)
ORDER BY id_curso, semestre;
Displaying records 1 - 10
id_disciplina nome_disciplina id_curso semestre
1110201 História Econômica Geral 111 2
1110202 Culturas Digitais 111 2
1110402 Macroeconomia 2 111 4
1110802 Desenvolvimento Econômico 111 8
2110201 Desenvolvimento Humano 211 2
2110202 Didática Geral 211 2
2110402 Fundamentos e Orientações Metodológicas - Ed. Infantil - BNCC 211 4
2110602 Educação do Campo, Indígena e Quilombola 211 6
2110802 Estatística Aplicada 211 8
3110201 Bases da Eng. de Software 2 311 2

SOME e ALL

SOME e ALL comparam um valor com cada elemento do conjunto retornado pela subconsulta.

  • valor > SOME (subconsulta): verdadeiro se o valor for maior que pelo menos um elemento do conjunto.
  • valor > ALL (subconsulta): verdadeiro se o valor for maior que todos os elementos do conjunto.

O predicado = SOME é equivalente a IN.

Disciplinas com carga horária maior que pelo menos uma disciplina de PED (> SOME):

SELECT id_disciplina, nome_disciplina, id_curso, carga_horaria
FROM   disciplina
WHERE  carga_horaria > SOME (
    SELECT carga_horaria
    FROM   disciplina
    WHERE  id_curso = 211
)
  AND id_curso <> 211
ORDER BY carga_horaria;
Displaying records 1 - 10
id_disciplina nome_disciplina id_curso carga_horaria
1110202 Culturas Digitais 111 60
1110201 História Econômica Geral 111 75
1110401 Microeconomia 2 111 75
1110601 Economia Brasileira 111 75
1110602 Orçamento e Finanças Públicas 111 75
1110802 Desenvolvimento Econômico 111 90
1110402 Macroeconomia 2 111 95
1110801 Monografia 111 100
3110801 Bases da Eng. de Software 8 311 100
3110202 Projeto Aplicado 2 - site WEB 311 120

Disciplinas com carga horária maior que TODAS as disciplinas de ECO (> ALL):

SELECT id_disciplina, nome_disciplina, id_curso, carga_horaria
FROM   disciplina
WHERE  carga_horaria > ALL (
    SELECT carga_horaria
    FROM   disciplina
    WHERE  id_curso = 111
)
ORDER BY carga_horaria;
7 records
id_disciplina nome_disciplina id_curso carga_horaria
3110402 Projeto Aplicado 4 - blockchain 311 120
3110202 Projeto Aplicado 2 - site WEB 311 120
3110602 Projeto Aplicado 6 - Machine Learning 311 120
3110201 Bases da Eng. de Software 2 311 150
3110601 Bases da Eng. de Software 6 311 150
3110802 Projeto Aplicado 8 - Sistema em Tempo Real 311 160
3110401 Bases da Eng. de Software 4 311 180

EXISTS e NOT EXISTS

EXISTS verifica se a subconsulta retorna ao menos uma linha. É frequentemente usado com subconsultas correlacionadas, em que a subconsulta referencia colunas da consulta externa.

EXISTS: disciplinas que têm ao menos um pré-requisito:

SELECT d.id_disciplina, d.nome_disciplina, d.id_curso
FROM   disciplina d
WHERE  EXISTS (
    SELECT 1
    FROM   prereq p
    WHERE  p.id_disciplina = d.id_disciplina
)
ORDER BY d.id_curso, d.id_disciplina;
Displaying records 1 - 10
id_disciplina nome_disciplina id_curso
1110401 Microeconomia 2 111
1110601 Economia Brasileira 111
1110602 Orçamento e Finanças Públicas 111
1110801 Monografia 111
2110401 Eletiva I 211
2110601 Eletiva II 211
2110801 Tecnologia Educacional - Design 211
3110401 Bases da Eng. de Software 4 311
3110402 Projeto Aplicado 4 - blockchain 311
3110601 Bases da Eng. de Software 6 311

EXISTS: cursos que têm pelo menos uma disciplina com carga horária acima de 150 h:

SELECT c.id_curso, c.nome_curso
FROM   curso c
WHERE  EXISTS (
    SELECT 1
    FROM   disciplina d
    WHERE  d.id_curso = c.id_curso   -- correlação com a consulta externa
      AND  d.carga_horaria > 150
);
1 records
id_curso nome_curso
311 Engenharia de Software

NOT EXISTS: alunos que não têm nenhuma reprovação:

SELECT a.nome, a.id_curso
FROM   aluno a
WHERE  NOT EXISTS (
    SELECT 1
    FROM   matricula_disciplina md
    WHERE  md.id_aluno = a.id_aluno
      AND  md.aprovado = 0
)
ORDER BY a.id_curso, a.nome;
Displaying records 1 - 10
nome id_curso
Beatriz Carvalho 111
Camila Teixeira 111
Gabriel Ribeiro 111
Lucas Santos 111
Rodrigo Lima 111
Amanda Moreira 211
Carolina Lima 211
Letícia Souza 211
Rafael Costa 211
Vinícius Pereira 211

Subconsulta no FROM

Uma subconsulta pode ser usada como se fosse uma tabela na cláusula FROM. Ela deve receber um alias.

Cursos com carga horária média por disciplina acima de 70 h:

-- A subconsulta calcula a média por curso; a consulta externa filtra
SELECT media_por_curso.id_curso,
       ROUND(media_por_curso.media, 2) AS media_carga
FROM (
    SELECT id_curso, AVG(carga_horaria) AS media
    FROM   disciplina
    GROUP BY id_curso
) AS media_por_curso
WHERE  media_por_curso.media > 70
ORDER BY media_por_curso.media DESC;
2 records
id_curso media_carga
311 137.50
111 80.63

WITH: Expressões de Tabela Comuns (CTE)

A cláusula WITH define uma CTE (Common Table Expression), que é uma subconsulta nomeada e reutilizável na consulta principal. CTEs tornam consultas complexas mais legíveis.

Cursos com carga horária total e número de disciplinas:

-- Define a CTE com estatísticas por curso
WITH stats_curso AS (
    SELECT id_curso,
           COUNT(*)         AS n_disciplinas,
           SUM(carga_horaria)  AS carga_total,
           AVG(carga_horaria)  AS carga_media
    FROM   disciplina
    GROUP BY id_curso
)
-- Usa a CTE junto com a tabela curso para obter o nome completo
SELECT c.nome_curso, s.n_disciplinas, s.carga_total,
       ROUND(s.carga_media, 1) AS carga_media
FROM   stats_curso s
JOIN   curso c USING (id_curso)
ORDER BY s.carga_total DESC;
3 records
nome_curso n_disciplinas carga_total carga_media
Engenharia de Software 8 1100 137.5
Ciências Econômicas 8 645 80.6
Pedagogia 8 510 63.8

Vantagens das CTEs com WITH:

  • Melhoram a legibilidade ao dar nomes descritivos a subconsultas complexas.
  • Permitem reutilizar a mesma subconsulta várias vezes na consulta principal.
  • Em alguns SGBDs, podem ser usadas recursivamente para consultas hierárquicas (WITH RECURSIVE).

Para Praticar

-- Subconsulta escalar no SELECT: mostrar a diferença do salário em relação à média
SELECT nome, id_escola, salario,
       ROUND(salario - (SELECT AVG(salario) FROM professor), 2) AS dif_media
FROM   professor
ORDER BY dif_media DESC;
7 records
nome id_escola salario dif_media
Helena Carvalho 31 6700 1585.71
Carla Pinto 11 6600 1485.71
Bruno Teixeira 11 6500 1385.71
Felipe Araujo 21 6400 1285.71
Gustavo Costa 31 3300 -1814.29
Eduarda Souza 21 3200 -1914.29
Igor Melo 31 3100 -2014.29 
-- ALL: professores com salário maior que TODOS os professores de 20h
SELECT nome, id_escola, salario
FROM   professor
WHERE  salario > ALL (
    SELECT salario
    FROM   professor
    WHERE  ch_semanal = 20
)
ORDER BY salario DESC;
4 records
nome id_escola salario
Helena Carvalho 31 6700
Carla Pinto 11 6600
Bruno Teixeira 11 6500
Felipe Araujo 21 6400 
-- WITH RECURSIVE: encontrar todos os pré-requisitos transitivos de 3110801
-- (pré-requisitos diretos e indiretos — cadeia completa)
WITH RECURSIVE cadeia AS (
    -- Caso base: pré-requisitos diretos de 3110801
    SELECT id_prereq AS disciplina, 1 AS nivel
    FROM   prereq
    WHERE  id_disciplina = 3110801
    UNION
    -- Caso recursivo: pré-requisitos dos pré-requisitos
    SELECT p.id_prereq, c.nivel + 1
    FROM   prereq p
    JOIN   cadeia c ON p.id_disciplina = c.disciplina
)
SELECT c.disciplina AS prereq_transitivo, c.nivel, d.nome_disciplina
FROM   cadeia c
JOIN   disciplina d ON d.id_disciplina = c.disciplina
ORDER BY c.nivel, c.disciplina;
5 records
prereq_transitivo nivel nome_disciplina
3110601 1 Bases da Eng. de Software 6
3110401 2 Bases da Eng. de Software 4
3110402 2 Projeto Aplicado 4 - blockchain
3110201 3 Bases da Eng. de Software 2
3110202 3 Projeto Aplicado 2 - site WEB

WITH RECURSIVE permite escrever consultas que percorrem estruturas hierárquicas ou grafos — como a cadeia de pré-requisitos. O PostgreSQL suporta esse recurso; outros SGBDs podem ter sintaxe ligeiramente diferente. O resultado mostra todos os pré-requisitos (diretos e indiretos) de 3110801 com o nível de profundidade na cadeia.