PostgreSQL: Subconsultas e CTEs no PostgreSQL

Q: P: Há diferença de desempenho entre uma CTE e uma subconsulta?

R: PostgreSQL 12+ decide automaticamente se faz inline de uma CTE. CTEs simples geralmente são inline; as complexas podem ser materializadas. Use MATERIALIZED / NOT MATERIALIZED para controlar manualmente.

Q: P: Uma CTE recursiva pode entrar em laço infinito?

R: Possivelmente. Se os dados contiverem um ciclo (ex.: A→B→A), a recursão não vai parar. Proteja-se adicionando um limite de nível ou rastreando o caminho para evitar revisitar nós.

Q: P: Por que NOT IN retorna vazio quando encontra NULL?

R: x NOT IN (a, NULL) é equivalente a x<>a AND x<>NULL, e x<>NULL é UNKNOWN; na cadeia AND, UNKNOWN torna toda a linha FALSE. NOT EXISTS é mais seguro em vez disso.

Q: P: Uma CTE pode referenciar uma CTE definida anteriormente?

R: Sim. Em WITH a AS (...), b AS (SELECT ... FROM a), b pode referenciar a, referenciada para baixo na ordem de definição.

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

Subconsultas escalares / de coluna / de linha / de tabela
EXISTS / NOT EXISTS
Operadores ANY / ALL
Onde as subconsultas aparecem: FROM / WHERE / SELECT
CTE (cláusula WITH, recurso do PostgreSQL)
CTE recursiva (WITH RECURSIVE para consulta em árvore)
CTE vs subconsulta vs tabela temporária

2. A História

Alice é uma desenvolvedora de sistema de RH em uma empresa SaaS. A empresa tem mais de 500 pessoas, organizadas em uma árvore: o CEO no topo, abaixo dele VPs, abaixo dos VPs Diretores, abaixo dos Diretores Gerentes, abaixo dos Gerentes funcionários.

O gerente de produto pergunta: a partir de qualquer nó, liste esse nó e todos os seus descendentes (em vários níveis), exibidos com indentação para revelar a hierarquia.

Uma consulta simples só pode ir um nível de profundidade. Alice aprende CTEs recursivas WITH RECURSIVE e resolve toda a subárvore em uma única instrução SQL.

3. Conceito

(1) Classificação de Subconsultas

Tipo	Retorna	Pode aparecer em	Exemplo
Subconsulta escalar	Uma linha, um valor	SELECT, WHERE, HAVING	`(SELECT MAX(salary) ...)`
Subconsulta de coluna	Uma coluna, muitas linhas	WHERE + IN/ANY/ALL	`WHERE id IN (SELECT ...)`
Subconsulta de linha	Uma linha, muitas colunas	WHERE	`WHERE (a,b) = (SELECT x,y ...)`
Subconsulta de tabela	Muitas linhas, muitas colunas	FROM	`FROM (SELECT ...) AS t`

▶ Exemplo: Subconsulta Escalar no SELECT

SQL

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg,
  salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees
WHERE department_id = 5;

TEXT 📖 Somente leitura

 name    | salary | company_avg | diff
---------+--------+-------------+-------
 Alice   |  95000 |    72000.00 | 23000
 Bob     |  88000 |    72000.00 | 16000

▶ Exemplo: Subconsulta Escalar no WHERE

SQL

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

▶ Exemplo: Subconsulta de Coluna + IN

SQL

SELECT order_id, amount
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE region = 'NA'
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Subconsulta de Linha

SQL

SELECT name, department_id, salary
FROM employees
WHERE (department_id, salary) = (
  SELECT department_id, MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING department_id = employees.department_id
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) EXISTS / NOT EXISTS

EXISTS verifica se a subconsulta retorna alguma linha; não se importa com os valores reais, apenas com a "existência".

▶ Exemplo: EXISTS Encontra Usuários com Pedidos

SQL

SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.user_id
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Abordagem	Sintaxe	Condição de parada	Compatível com NULL
IN	`WHERE id IN (SELECT ...)`	Varre tudo	Cuidado com NULLs
EXISTS	`WHERE EXISTS (SELECT 1 ...)`	Para na primeira linha	NULL não tem efeito
JOIN	`JOIN ...`	Todas as correspondências	Depende do tipo de JOIN

▶ Exemplo: NOT EXISTS Encontra Usuários sem Pedidos

SQL

SELECT u.user_id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.user_id
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT EXISTS é mais seguro que NOT IN: quando o resultado da subconsulta contém NULL, NOT IN retorna um resultado vazio para toda a consulta.

▶ Exemplo: Armadilha do NULL no NOT IN

SQL

SELECT name FROM customers
WHERE region NOT IN ('NA', 'EU', NULL);

TEXT 📖 Somente leitura

(0 rows)

Porque x NOT IN (a, b, NULL) é equivalente a x <> a AND x <> b AND x <> NULL, e x <> NULL é UNKNOWN, toda a expressão é FALSE.

(3) ANY / ALL

▶ Exemplo: ANY Encontra Pessoas com Salário Maior que Qualquer Funcionário de um Departamento

SQL

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department_id = 3
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Equivalente a > MIN(resultado da subconsulta).

▶ Exemplo: ALL Encontra Pessoas com Salário Maior que Todos em um Departamento

SQL

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 3
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Equivalente a > MAX(resultado da subconsulta).

Operador	Significado	Equivalente
`> ANY (...)`	Maior que qualquer um	`> MIN(...)`
`> ALL (...)`	Maior que todos	`> MAX(...)`
`= ANY (...)`	Igual a qualquer um	`IN (...)`

▶ Exemplo: Subconsulta de Tabela no FROM

SQL

SELECT
  department_id,
  avg_salary,
  count
FROM (
  SELECT
    department_id,
    AVG(salary) AS avg_salary,
    COUNT(*)    AS count
  FROM employees
  GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 70000;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

4. Pontos-Chave

(1) CTE (Cláusula WITH)

Uma CTE (Common Table Expression) usa WITH para definir um conjunto de resultados temporário nomeado que pode ser referenciado várias vezes.

▶ Exemplo: CTE Simplifica Subconsultas Aninhadas

SQL

WITH regional_sales AS (
  SELECT
    region,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
),
top_regions AS (
  SELECT region
  FROM regional_sales
  WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales)
)
SELECT
  o.order_id,
  o.amount,
  o.region
FROM orders o
WHERE o.region IN (SELECT region FROM top_regions)
ORDER BY o.amount DESC;

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

Abordagem	Legibilidade	Reutilizável	Otimizador inline	Materializada
Subconsulta aninhada	Ruim	Não	Sim	—
CTE	Boa	Sim	PG 12+ decide automaticamente	Pode forçar MATERIALIZED
Tabela temporária	Razoável	Sim	Não	Grava em disco

▶ Exemplo: CTE MATERIALIZED Força Materialização

SQL

WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM expensive_calc
UNION ALL
SELECT * FROM expensive_calc;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

MATERIALIZED força o cálculo uma vez e o armazena em cache, ideal para CTEs que são referenciadas muitas vezes com cálculo pesado.

▶ Exemplo: CTE NOT MATERIALIZED Força Inline

SQL

WITH simple_filter AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE region = 'NA'
)
SELECT * FROM simple_filter WHERE amount > 50000;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT MATERIALIZED permite que o otimizador faça inline da expansão, adequado para cenários simples de pushdown de predicado.

(2) CTE Recursiva (WITH RECURSIVE)

Uma CTE recursiva é a abordagem SQL padrão para dados em árvore e grafo.

Estrutura da sintaxe:

SQL

WITH RECURSIVE nome_cte AS (
  consulta_base        -- âncora: semente não recursiva
  UNION ALL
  consulta_recursiva   -- referencia a própria nome_cte
)
SELECT * FROM nome_cte;

▶ Exemplo: Todos os Subordinados a partir do CEO

SQL

WITH RECURSIVE subordinates AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1 AS level,
    name::text AS path
  FROM employees
  WHERE manager_id IS NULL
    AND name = 'David'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    s.level + 1,
    s.path || ' > ' || e.name
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT
  level,
  REPEAT('  ', level - 1) || name AS org_chart,
  path
FROM subordinates
ORDER BY path;

TEXT 📖 Somente leitura

 level |       org_chart        |              path
-------+------------------------+--------------------------------
     1 | David                  | David
     2 |   Alice                | David > Alice
     3 |     Bob                | David > Alice > Bob
     3 |     Charlie            | David > Alice > Charlie
     2 |   Eve                  | David > Eve
     3 |     Frank              | David > Eve > Frank

▶ Exemplo: Limitar Profundidade da Recursão (Prevenir Laço Infinito)

SQL

WITH RECURSIVE subordinates AS (
  SELECT
    employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
  WHERE s.level < 5
)
SELECT * FROM subordinates;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

WHERE s.level < 5 limita a recursão a no máximo 5 níveis.

▶ Exemplo: Gerar uma Série de Datas

SQL

WITH RECURSIVE date_series AS (
  SELECT '2025-01-01'::date AS dt
  UNION ALL
  SELECT dt + INTERVAL '1 day'
  FROM date_series
  WHERE dt < '2025-12-31'
)
SELECT dt FROM date_series;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Prática

▶ Exemplo: Encontrar o Funcionário com Maior Salário de Cada Departamento

SQL

SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: CTE Calcula Pontuações RFM de Clientes

SQL

WITH customer_orders AS (
  SELECT
    customer_id,
    MAX(created_at)                           AS last_order_date,
    COUNT(*)                                  AS frequency,
    SUM(amount)                               AS monetary
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  frequency,
  monetary,
  NTILE(4) OVER (ORDER BY monetary DESC) AS m_quartile
FROM customer_orders;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: CTE Recursiva para Árvore de Categorias de Produtos

SQL

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    c.category_id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT
  depth,
  REPEAT('──', depth) || name AS tree_view
FROM category_tree
ORDER BY depth, name;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: EXISTS Encontra um Produto Pedido em Todas as Regiões

SQL

SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM regions r
  WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.region = r.region_code
  )
);

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: CTE + Função de Janela para Top-N

SQL

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn <= 3;

TEXT 📖 Somente leitura

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. Exemplo Completo

A consulta de organograma de Alice—a partir de qualquer gerente, liste todos os subordinados com indentação de nível, caminho e número de pessoas:

SQL

WITH RECURSIVE org_tree AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1          AS level,
    name::text AS path,
    ARRAY[employee_id] AS subtree_ids
  FROM employees
  WHERE name = 'Alice'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name,
    o.subtree_ids || e.employee_id
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.employee_id
)
SELECT
  o.level,
  REPEAT('    ', o.level - 1) || o.name AS org_chart,
  o.path,
  ARRAY_LENGTH(o.subtree_ids, 1)       AS team_size
FROM org_tree o
ORDER BY o.path;

TEXT 📖 Somente leitura

 level |         org_chart          |              path               | team_size
-------+----------------------------+---------------------------------+-----------
     1 | Alice                      | Alice                           |         1
     2 |     Bob                    | Alice > Bob                     |         2
     3 |         Diana              | Alice > Bob > Diana             |         3
     3 |         Eve                | Alice > Bob > Eve               |         4
     2 |     Charlie                | Alice > Charlie                 |         5
     3 |         Frank              | Alice > Charlie > Frank         |         6

7. Fluxo de Execução da CTE Recursiva

flowchart TD
    A["Consulta Âncora<br/>(semente não recursiva)"] --> B["Tabela de Trabalho T₀"]
    B --> C["Consulta Recursiva<br/>(JOIN com T₀)"]
    C --> D{"Novas linhas<br/>produzidas?"}
    D -->|Sim| E["Tabela de Trabalho T₁"]
    E --> F["Anexar ao resultado"]
    F --> C
    D -->|Não| G["Resultado Final<br/>(todas as iterações UNION ALL)"]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style G fill:#c8e6c9

Passo	Operação	Descrição
1	Executar consulta âncora	Semente não recursiva, gera linhas iniciais
2	Colocar na tabela de trabalho	T₀ = resultado da âncora
3	Consulta recursiva	JOIN da tabela de trabalho com a tabela original
4	Verificar novas linhas	Continuar se existirem novas linhas; terminar se não houver
5	Mesclar resultados	UNION ALL de todos os resultados das iterações

❓ Perguntas Frequentes

📖 Resumo

Subconsultas vêm em quatro tipos—escalar / coluna / linha / tabela—cada uma com seu lugar adequado
EXISTS/NOT EXISTS são mais seguros que IN/NOT IN e não são afetados por NULL
ANY é equivalente a "maior que o mínimo"; ALL a "maior que o máximo"
Uma CTE usa WITH para definir um resultado temporário nomeado, muito mais legível que subconsultas aninhadas
PostgreSQL 12+ decide inline ou materialização de CTE automaticamente; pode ser controlado manualmente
WITH RECURSIVE lida com dados em árvore/grafo e é a abordagem SQL padrão
Uma CTE recursiva precisa de uma âncora mais uma parte recursiva, unidas com UNION ALL
Previna recursão infinita com limite de nível ou caminho rastreado

📝 Exercícios

⭐ Use uma subconsulta escalar para encontrar funcionários cujo salário está acima da média da empresa.
⭐ Use NOT EXISTS para encontrar clientes que nunca fizeram um pedido.
⭐⭐ Reescreva a seguinte subconsulta aninhada usando uma CTE: encontre pedidos cujo valor está acima do valor médio de pedido daquele cliente.
⭐⭐ Use WITH RECURSIVE para consultar os subordinados de 3 níveis de um gerente especificado da tabela employees, com indentação de nível.
⭐⭐⭐ Usando uma CTE recursiva mais agregação: a partir do CEO, conte os subordinados diretos de cada gerente e o número de pessoas de toda a sua subárvore, tudo em uma única instrução SQL.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós