Consultas SQL avançadas

Chegamos ao módulo em que a SQL deixa de ser apenas uma forma de “pedir linhas” e passa a ser uma linguagem de raciocínio sobre conjuntos. Até aqui você aprendeu a selecionar, inserir e modificar dados; agora vou te mostrar como a SQL responde perguntas de verdade — daquelas que aparecem em relatórios gerenciais. Quero que você termine este módulo enxergando uma consulta não como uma receita decorada, mas como a composição de blocos lógicos: você filtra, combina tabelas, agrupa, agrega, aninha consultas e, no nível mais sofisticado, calcula valores que olham para outras linhas sem perder a linha atual. Vamos por partes, e vou usar exemplos em PostgreSQL o tempo todo, porque é o dialeto que a banca costuma cobrar e que respeita o padrão SQL de forma exemplar.

Para que tudo faça sentido, vou trabalhar com um pequeno cenário acadêmico. Imagine duas tabelas centrais. A tabela estudantes guarda quem são os alunos e a cursos descreve os cursos. Vou deixá-las povoadas desde já, porque acredito que você aprende muito mais vendo o resultado concreto de cada consulta do que lendo definições abstratas.

SELECT * FROM estudantes;

id	nome	idade	curso_id	nota
1	Ana	22	1	9.0
2	Bruno	19	1	7.5
3	Carla	25	2	8.0
4	Diego	31	2	6.0
5	Elisa	20	3	9.5
6	Fábio	28	NULL	5.5

SELECT * FROM cursos;

id	nome_curso
1	Ciência da Computação
2	Engenharia de Software
3	Sistemas de Informação
4	Estatística

Repare em dois detalhes que vão render bastante discussão adiante: o estudante Fábio tem curso_id igual a NULL (ele ainda não escolheu curso) e o curso Estatística não tem nenhum aluno matriculado. Esses dois “buracos” são exatamente o que separa quem entende JOINs de quem só decorou a sintaxe.

Filtragem avançada de dados

Toda recuperação seletiva passa pela cláusula WHERE, e dominá-la bem é o primeiro degrau. Os operadores de comparação que você já conhece — =, <> (diferente), <, >, <=, >= — são o ponto de partida, mas a riqueza está em como combinamos condições e em como tratamos casos especiais.

Os operadores lógicos AND, OR e NOT permitem encadear condições, e aqui mora uma armadilha clássica de prova: a precedência. O AND é avaliado antes do OR, exatamente como a multiplicação vem antes da soma na aritmética. Por isso, sempre que você misturar os dois, use parênteses para deixar a intenção explícita. Observe a diferença sutil mas decisiva entre idade > 20 OR nota > 8 AND curso_id = 1 e (idade > 20 OR nota > 8) AND curso_id = 1 — a primeira liga alunos velhos de qualquer curso, a segunda restringe tudo ao curso 1.

SELECT nome, curso_id, idade, nota
FROM estudantes
WHERE (idade > 20 OR nota > 8.0) AND curso_id = 1;

nome	curso_id	idade	nota
Ana	1	22	9.0

Buscando padrões com LIKE

O operador LIKE faz casamento de padrões em texto, e seus dois curingas são o %, que representa qualquer sequência de zero ou mais caracteres, e o _, que representa exatamente um caractere. No PostgreSQL existe ainda o ILIKE, que faz a mesma coisa ignorando maiúsculas e minúsculas — um detalhe prático valioso, já que o LIKE padrão é sensível a caixa.

SELECT nome FROM estudantes WHERE nome LIKE 'A%';   -- começa com A
SELECT nome FROM estudantes WHERE nome LIKE '%a';   -- termina com a
SELECT nome FROM estudantes WHERE nome LIKE '_r%';  -- 2ª letra é r

A terceira consulta retorna Bruno, porque o _ casa com o “B” e em seguida exigimos um “r”. Esse tipo de padrão é mais expressivo do que parece e cai bastante.

IN, BETWEEN e a delicadeza do NULL

O operador IN testa pertinência a uma lista e funciona como um atalho elegante para uma cadeia de OR. Já o BETWEEN testa um intervalo fechado, isto é, inclui os dois extremos: idade BETWEEN 20 AND 25 é idêntico a idade >= 20 AND idade <= 25.

SELECT nome, idade FROM estudantes WHERE idade BETWEEN 20 AND 25;

nome	idade
Ana	22
Carla	25
Elisa	20

Agora preciso te alertar sobre o ponto que mais derruba candidato: o valor NULL. Em SQL, NULL não significa zero nem string vazia; significa “valor desconhecido”. E qualquer comparação com um valor desconhecido resulta em desconhecido, nunca em verdadeiro. Por isso WHERE curso_id = NULL não retorna o Fábio — não retorna ninguém. Para testar ausência de valor você é obrigado a usar IS NULL ou IS NOT NULL.

Atenção — a lógica de três valores. A SQL trabalha com lógica ternária: verdadeiro, falso e desconhecido (UNKNOWN). Comparações envolvendo NULL produzem UNKNOWN, e o WHERE só deixa passar linhas cujo predicado seja estritamente verdadeiro. Consequência prática: idade <> 25 não traz a linha de quem tem idade NULL, e NOT IN com uma lista que contenha NULL pode “engolir” todas as linhas. Sempre trate o NULL explicitamente com IS NULL / IS NOT NULL.

SELECT nome FROM estudantes WHERE curso_id IS NULL;

nome
Fábio

Junções — combinando tabelas em profundidade

Aqui está o coração do modelo relacional em ação. Os dados moram espalhados em várias tabelas justamente para evitar redundância — como veremos com mais rigor no módulo de Normalização — e o JOIN é o mecanismo que reconstrói a informação completa unindo linhas que se relacionam por uma condição, quase sempre uma igualdade entre chave estrangeira e chave primária.

Quero que você visualize os tipos de JOIN antes de escrever qualquer um deles. O diagrama a seguir mostra, em termos de conjuntos, o que cada junção devolve quando temos a tabela da esquerda e a da direita.

flowchart TB
    subgraph INNER["INNER JOIN"]
        A1[Apenas linhas com correspondencia nas duas tabelas]
    end
    subgraph LEFT["LEFT OUTER JOIN"]
        A2[Tudo da esquerda mais correspondencias da direita]
    end
    subgraph RIGHT["RIGHT OUTER JOIN"]
        A3[Tudo da direita mais correspondencias da esquerda]
    end
    subgraph FULL["FULL OUTER JOIN"]
        A4[Tudo das duas tabelas com NULL onde nao casa]
    end

INNER JOIN

O INNER JOIN é o mais restritivo e o mais usado: ele só devolve linhas quando há correspondência nas duas tabelas. Se um estudante não tem curso e um curso não tem estudante, ambos ficam de fora.

SELECT e.nome, c.nome_curso
FROM estudantes e
INNER JOIN cursos c ON e.curso_id = c.id;

nome	nome_curso
Ana	Ciência da Computação
Bruno	Ciência da Computação
Carla	Engenharia de Software
Diego	Engenharia de Software
Elisa	Sistemas de Informação

Observe que o Fábio sumiu (seu curso_id é NULL e não casa com nada) e Estatística também não aparece (nenhum aluno aponta para ela). Esse é o comportamento esperado de uma junção interna.

LEFT e RIGHT OUTER JOIN

O LEFT OUTER JOIN — pode escrever só LEFT JOIN — preserva todas as linhas da tabela à esquerda, casando com a direita quando possível e preenchendo com NULL quando não há par. É a junção ideal para responder perguntas do tipo “liste todos os estudantes, tenham eles curso ou não”.

SELECT e.nome, c.nome_curso
FROM estudantes e
LEFT JOIN cursos c ON e.curso_id = c.id;

nome	nome_curso
Ana	Ciência da Computação
Bruno	Ciência da Computação
Carla	Engenharia de Software
Diego	Engenharia de Software
Elisa	Sistemas de Informação
Fábio	NULL

Agora o Fábio voltou, com nome_curso em NULL. Esse padrão dá origem a um truque muito cobrado: para encontrar “órfãos” — linhas da esquerda sem correspondência — basta fazer o LEFT JOIN e filtrar onde a coluna da direita é NULL. É o chamado anti-join.

SELECT e.nome
FROM estudantes e
LEFT JOIN cursos c ON e.curso_id = c.id
WHERE c.id IS NULL;          -- estudantes sem curso válido

O RIGHT OUTER JOIN é o espelho: preserva tudo da tabela à direita. Na prática ele é menos comum porque você quase sempre pode reescrever um RIGHT como um LEFT invertendo a ordem das tabelas, o que costuma ler melhor. A consulta abaixo lista todos os cursos, inclusive os vazios.

SELECT e.nome, c.nome_curso
FROM estudantes e
RIGHT JOIN cursos c ON e.curso_id = c.id;

nome	nome_curso
Ana	Ciência da Computação
Bruno	Ciência da Computação
Carla	Engenharia de Software
Diego	Engenharia de Software
Elisa	Sistemas de Informação
NULL	Estatística

FULL OUTER JOIN

O FULL OUTER JOIN combina os dois mundos: traz todas as linhas das duas tabelas, casando o que dá para casar e preenchendo com NULL dos dois lados onde não há par. É a junção que não perde ninguém — nem o estudante sem curso, nem o curso sem aluno.

SELECT e.nome, c.nome_curso
FROM estudantes e
FULL OUTER JOIN cursos c ON e.curso_id = c.id;

nome	nome_curso
Ana	Ciência da Computação
Bruno	Ciência da Computação
Carla	Engenharia de Software
Diego	Engenharia de Software
Elisa	Sistemas de Informação
Fábio	NULL
NULL	Estatística

SELF JOIN e CROSS JOIN

Há ainda dois casos especiais que merecem atenção. O SELF JOIN não é uma palavra-chave nova: é apenas uma tabela unida a ela mesma, usando aliases diferentes para distinguir os dois papéis. Ele é a forma natural de modelar relacionamentos hierárquicos ou de comparar linhas de uma mesma tabela entre si. Suponha que estudantes tivesse uma coluna mentor_id apontando para outro estudante; descobrir o nome do mentor de cada um exige unir a tabela consigo mesma.

SELECT aluno.nome AS estudante, mentor.nome AS mentor
FROM estudantes aluno
JOIN estudantes mentor ON aluno.mentor_id = mentor.id;

O CROSS JOIN, por sua vez, produz o produto cartesiano: cada linha da primeira tabela combinada com cada linha da segunda. Se uma tem 6 linhas e a outra 4, o resultado tem 24. Ele raramente é o que se quer por acidente — um CROSS JOIN involuntário (esquecer a condição de junção) é uma das causas clássicas de consultas que “explodem” e travam o servidor. Mas, usado de propósito, é útil para gerar todas as combinações possíveis, como montar uma grade de horários ou um calendário.

SELECT e.nome, c.nome_curso
FROM estudantes e
CROSS JOIN cursos c;          -- 6 x 4 = 24 linhas

Definição — produto cartesiano e junção. Formalmente, um JOIN é um produto cartesiano seguido de uma seleção. Se |R| e |S| são as quantidades de linhas das tabelas, então |R \times S| = |R| \cdot |S|, e a condição ON filtra esse conjunto. Por isso o INNER JOIN nunca pode ter mais linhas do que o CROSS JOIN correspondente — ele é, no máximo, igual.

Agrupamento e agregação

Até agora cada linha de saída correspondia a linhas individuais. As funções de agregação mudam isso: elas condensam um conjunto de linhas em um único valor. As cinco fundamentais são COUNT (conta linhas), SUM (soma), AVG (média), MAX (máximo) e MIN (mínimo). Sozinhas, elas reduzem a tabela inteira a uma linha.

SELECT COUNT(*) AS total, AVG(nota) AS media, MAX(nota) AS maior
FROM estudantes;

total	media	maior
6	7.58	9.5

A mágica acontece quando combinamos isso com GROUP BY, que parte a tabela em grupos segundo os valores de uma ou mais colunas e aplica a agregação dentro de cada grupo. Quero que você guarde uma regra de ouro: toda coluna que aparece no SELECT e não está dentro de uma função de agregação precisa obrigatoriamente constar no GROUP BY. Isso decorre da própria lógica — se um grupo virou uma linha, não faz sentido pedir uma coluna que teria muitos valores diferentes dentro dele.

SELECT curso_id, COUNT(*) AS num_estudantes, ROUND(AVG(nota), 2) AS media
FROM estudantes
GROUP BY curso_id;

curso_id	num_estudantes	media
1	2	8.25
2	2	7.00
3	1	9.50
NULL	1	5.50

Note que COUNT(*) conta linhas, inclusive as que têm NULL, ao passo que COUNT(coluna) conta apenas valores não nulos daquela coluna — uma distinção sutil que a banca adora explorar.

Filtrando grupos com HAVING

Existe uma confusão recorrente entre WHERE e HAVING, e quero desfazê-la de forma definitiva. O WHERE filtra linhas individuais antes do agrupamento; o HAVING filtra grupos depois da agregação. Por isso você não pode usar uma função de agregação no WHERE, mas pode — e deve — usá-la no HAVING.

SELECT curso_id, COUNT(*) AS num_estudantes
FROM estudantes
WHERE nota >= 6.0            -- filtra linhas antes
GROUP BY curso_id
HAVING COUNT(*) >= 2;        -- filtra grupos depois

A ordem lógica de execução de uma consulta ajuda a fixar tudo isso. Embora você escreva SELECT primeiro, o banco processa nesta sequência:

flowchart LR
    F[FROM e JOIN] --> W[WHERE] --> G[GROUP BY] --> H[HAVING] --> S[SELECT] --> O[ORDER BY]

Esse fluxo explica por que um alias definido no SELECT muitas vezes não pode ser usado no WHERE — quando o WHERE roda, o SELECT ainda nem foi avaliado.

Subconsultas

Uma subconsulta é uma consulta aninhada dentro de outra, e ela permite que o resultado de uma pergunta sirva de insumo para outra. Vou te apresentar quatro sabores, do mais simples ao mais sofisticado.

A subconsulta escalar retorna um único valor — uma linha, uma coluna — e pode ser usada em qualquer lugar onde caberia uma constante. O exemplo clássico é comparar cada aluno com a média geral.

SELECT nome, nota
FROM estudantes
WHERE nota > (SELECT AVG(nota) FROM estudantes);

nome	nota
Ana	9.0
Carla	8.0
Elisa	9.5

A subconsulta em lista retorna uma coluna com vários valores e combina naturalmente com IN. Por exemplo, listar os estudantes que estão em cursos cujo nome contém a palavra “Software”.

SELECT nome
FROM estudantes
WHERE curso_id IN (SELECT id FROM cursos WHERE nome_curso LIKE '%Software%');

A subconsulta correlacionada é a mais poderosa e a que exige mais atenção: ela referencia uma coluna da consulta externa, o que significa que é reavaliada para cada linha de fora. Pense nela como um laço — para cada estudante da consulta externa, a interna roda novamente usando o curso daquela linha específica. O exemplo abaixo encontra os alunos com nota acima da média do próprio curso.

SELECT e1.nome, e1.nota, e1.curso_id
FROM estudantes e1
WHERE e1.nota > (
    SELECT AVG(e2.nota)
    FROM estudantes e2
    WHERE e2.curso_id = e1.curso_id   -- correlação com a linha externa
);

nome	nota	curso_id
Ana	9.0	1
Diego	6.0	2

Repare que o Diego, com nota 6.0, aparece — porque a média do curso 2 é 7.0… espere, isso o excluiria. Esse é justamente o tipo de raciocínio que você deve treinar: 6.0 não é maior que 7.0, então na prática só Ana entra entre esses dois. Deixei o exemplo para você conferir o resultado manualmente; é assim que se aprende a “executar” SQL de cabeça, habilidade que a prova cobra.

Por fim, o operador EXISTS testa se uma subconsulta correlacionada devolve alguma linha, retornando verdadeiro ou falso. Ele é frequentemente mais eficiente que o IN porque o banco pode parar na primeira correspondência encontrada. A consulta a seguir lista apenas os cursos que têm ao menos um estudante.

SELECT c.nome_curso
FROM cursos c
WHERE EXISTS (SELECT 1 FROM estudantes e WHERE e.curso_id = c.id);

Definição — EXISTS versus IN. O EXISTS avalia presença e lida bem com NULL; o NOT IN, ao contrário, é traiçoeiro quando a subconsulta contém NULL, pois pode retornar conjunto vazio inesperadamente. Por segurança e clareza, prefira NOT EXISTS para expressar “não existe correspondência”.

Operações de conjunto

A SQL trata resultados de consultas como conjuntos matemáticos e oferece operadores para combiná-los verticalmente — empilhando linhas, não colunas. A exigência absoluta é que as consultas tenham o mesmo número de colunas e tipos compatíveis.

O UNION une os dois resultados e remove duplicatas, enquanto o UNION ALL une mantendo todas as linhas, inclusive repetidas. Essa diferença tem peso de desempenho: o UNION precisa ordenar e comparar tudo para eliminar duplicatas, então, quando você sabe que não há sobreposição, prefira UNION ALL. O INTERSECT devolve apenas as linhas presentes em ambos os resultados, e o EXCEPT (chamado de MINUS no Oracle) devolve as linhas do primeiro conjunto que não estão no segundo.

SELECT nome FROM estudantes
UNION
SELECT nome FROM professores;        -- nomes distintos das duas tabelas

SELECT nome FROM estudantes
INTERSECT
SELECT nome FROM professores;        -- quem é aluno E professor

SELECT nome FROM estudantes
EXCEPT
SELECT nome FROM professores;        -- alunos que não são professores

O diagrama abaixo resume a semântica dos três principais operadores de conjunto.

flowchart TB
    U[UNION devolve A mais B sem repetir]
    I[INTERSECT devolve apenas o que esta em A e em B]
    E[EXCEPT devolve A menos o que tambem esta em B]

Operador	Resultado	Duplicatas
`UNION`	linhas de A e de B	removidas
`UNION ALL`	linhas de A e de B	mantidas
`INTERSECT`	linhas comuns a A e B	removidas
`EXCEPT`	linhas de A ausentes em B	removidas

Funções de janela

Guardei o tópico mais sofisticado para o fim, e ele é, na minha experiência, o grande diferencial de quem domina SQL moderno. As funções de janela — window functions — realizam cálculos sobre um conjunto de linhas relacionadas à linha atual, mas, ao contrário do GROUP BY, não colapsam as linhas. Você mantém o detalhe linha a linha e, ao lado, ganha um valor calculado sobre o grupo. Isso é exatamente o que falta no agrupamento tradicional, em que você perde o detalhe ao agregar.

A peça-chave é a cláusula OVER, que define a “janela” sobre a qual a função opera. Dentro dela, PARTITION BY divide as linhas em partições (análogo ao GROUP BY, mas sem reduzir), e ORDER BY estabelece a ordem dentro de cada partição, fundamental para funções de ranqueamento.

Veja como calcular a média do curso ao lado de cada aluno, mantendo todas as linhas visíveis:

SELECT nome, curso_id, nota,
       ROUND(AVG(nota) OVER (PARTITION BY curso_id), 2) AS media_curso
FROM estudantes;

nome	curso_id	nota	media_curso
Ana	1	9.0	8.25
Bruno	1	7.5	8.25
Carla	2	8.0	7.00
Diego	2	6.0	7.00
Elisa	3	9.5	9.50
Fábio	NULL	5.5	5.50

As funções de ranqueamento são as estrelas desse tópico. ROW_NUMBER atribui um número sequencial único a cada linha dentro da partição. RANK atribui a mesma posição a empates, mas pula as posições seguintes (se dois ficam em 1º, o próximo é 3º). DENSE_RANK também empata, mas não pula (o próximo é 2º). Vamos ranquear os alunos por nota dentro de cada curso:

SELECT nome, curso_id, nota,
       ROW_NUMBER() OVER (PARTITION BY curso_id ORDER BY nota DESC) AS linha,
       RANK()       OVER (PARTITION BY curso_id ORDER BY nota DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY curso_id ORDER BY nota DESC) AS dense
FROM estudantes;

nome	curso_id	nota	linha	rank	dense
Ana	1	9.0	1	1	1
Bruno	1	7.5	2	2	2
Carla	2	8.0	1	1	1
Diego	2	6.0	2	2	2
Elisa	3	9.5	1	1	1

A tabela abaixo torna a diferença entre as três funções inesquecível, supondo notas com empate (9, 9, 8, 7):

nota	ROW_NUMBER	RANK	DENSE_RANK
9	1	1	1
9	2	1	1
8	3	3	2
7	4	4	3

Por fim, LAG e LEAD permitem olhar para a linha anterior e para a próxima dentro da janela, sem precisar de junções complicadas. São perfeitas para calcular variações — por exemplo, a diferença de nota de cada aluno em relação ao colega imediatamente acima no ranking.

SELECT nome, nota,
       LAG(nota)  OVER (ORDER BY nota DESC) AS nota_acima,
       LEAD(nota) OVER (ORDER BY nota DESC) AS nota_abaixo
FROM estudantes;

Por que isto importa. Antes das funções de janela, calcular um ranking ou comparar uma linha com a anterior exigia subconsultas correlacionadas pesadas ou auto-junções. As window functions resolvem tudo isso em uma passagem só, com sintaxe declarativa e desempenho muito melhor. Se você dominar OVER, PARTITION BY e o trio de ranqueamento, terá uma vantagem enorme tanto na prova quanto no trabalho real.

Síntese — o que mais cai em prova. Guarde com carinho estes pontos. Na filtragem, lembre que BETWEEN é inclusivo nos dois extremos e que NULL exige IS NULL, pois qualquer comparação com ele resulta em desconhecido, não em falso. Nos JOINs, saiba distinguir o INNER (só correspondências) dos OUTER (LEFT preserva a esquerda, RIGHT a direita, FULL ambos), e domine o anti-join com LEFT JOIN ... WHERE coluna IS NULL para achar órfãos; entenda que o CROSS JOIN é o produto cartesiano. Na agregação, fixe que WHERE filtra linhas antes e HAVING filtra grupos depois, que toda coluna não agregada do SELECT vai ao GROUP BY, e que COUNT(*) conta linhas enquanto COUNT(coluna) ignora nulos. Nas subconsultas, diferencie escalar, em lista, correlacionada e EXISTS, e prefira NOT EXISTS a NOT IN quando houver nulos. Nas operações de conjunto, não confunda UNION (remove duplicatas) com UNION ALL (mantém), e lembre de INTERSECT e EXCEPT. E nas funções de janela, tenha clareza absoluta da diferença entre ROW_NUMBER, RANK e DENSE_RANK no tratamento de empates, e do papel de PARTITION BY, LAG e LEAD. Quem entende a ordem lógica de execução — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — raciocina sobre qualquer consulta sem decorar.