PostgreSQL Subqueries and CTEs

Q: Is there a performance difference between a CTE and a subquery?

PostgreSQL 12+ automatically decides whether to inline a CTE. Simple CTEs are usually inlined; complex ones may be materialized. Use MATERIALIZED / NOT MATERIALIZED to control it manually.

Q: Can a recursive CTE loop infinitely?

Possibly. If the data contains a cycle (e.g., A→B→A), recursion won't stop. Guard against it by adding a level limit, or tracking the path to avoid revisiting nodes.

Q: Why does NOT IN return empty when it hits NULL?

x NOT IN (a, NULL) is equivalent to x<>a AND x<>NULL , and x<>NULL is UNKNOWN; in the AND chain, UNKNOWN makes the whole row FALSE. NOT EXISTS is safer instead.

Q: Can a CTE reference a previously defined CTE?

Yes. In WITH a AS (...), b AS (SELECT ... FROM a) , b can reference a, referenced downward in definition order.

1. What You'll Learn

Scalar / column / row / table subqueries
EXISTS / NOT EXISTS
ANY / ALL operators
Where subqueries appear: FROM / WHERE / SELECT
CTE (WITH clause, PostgreSQL feature)
Recursive CTE (WITH RECURSIVE tree query)
CTE vs subquery vs temp table

2. The Story

Alice is an HR-system developer at a SaaS company. The company has over 500 people, organized in a tree: the CEO at the top, below them VPs, below VPs Directors, below Directors Managers, below Managers employees.

The product manager asks: starting from any node, list that node and all of its descendants (across multiple levels), shown with indentation to reveal the hierarchy.

A plain query can only go one level deep. Alice learns WITH RECURSIVE recursive CTEs and solves the whole subtree in a single SQL statement.

3. Concept

(1) Subquery Classification

Type	Returns	Can appear in	Example
Scalar subquery	One row, one value	SELECT, WHERE, HAVING	`(SELECT MAX(salary) ...)`
Column subquery	One column, many rows	WHERE + IN/ANY/ALL	`WHERE id IN (SELECT ...)`
Row subquery	One row, many columns	WHERE	`WHERE (a,b) = (SELECT x,y ...)`
Table subquery	Many rows, many columns	FROM	`FROM (SELECT ...) AS t`

▶ Example: Scalar Subquery in 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

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

▶ Example: Scalar Subquery in WHERE

SQL

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

TEXT

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

▶ Example: Column Subquery + IN

SQL

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

TEXT

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

▶ Example: Row Subquery

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

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

(2) EXISTS / NOT EXISTS

EXISTS checks whether the subquery returns any rows; it does not care about the actual values, only about "existence".

▶ Example: EXISTS Finds Users with Orders

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

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

Approach	Syntax	Stop condition	NULL-friendly
IN	`WHERE id IN (SELECT ...)`	Scans all	Mind NULLs
EXISTS	`WHERE EXISTS (SELECT 1 ...)`	Stops at first row	NULL has no effect
JOIN	`JOIN ...`	All matches	Depends on JOIN type

▶ Example: NOT EXISTS Finds Users without Orders

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

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

NOT EXISTS is safer than NOT IN: when the subquery result contains NULL, NOT IN returns an empty result for the whole query.

▶ Example: NOT IN's NULL Trap

SQL

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

TEXT

(0 rows)

Because x NOT IN (a, b, NULL) is equivalent to x <> a AND x <> b AND x <> NULL, and x <> NULL is UNKNOWN, the whole expression is FALSE.

(3) ANY / ALL

▶ Example: ANY Finds People Paid More Than Any Employee in a Department

SQL

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

TEXT

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

Equivalent to > MIN(subquery result).

▶ Example: ALL Finds People Paid More Than Everyone in a Department

SQL

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

TEXT

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

Equivalent to > MAX(subquery result).

Operator	Meaning	Equivalent
`> ANY (...)`	Greater than any one	`> MIN(...)`
`> ALL (...)`	Greater than all	`> MAX(...)`
`= ANY (...)`	Equal to any one	`IN (...)`

▶ Example: Table Subquery in 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

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

4. Key Points

(1) CTE (WITH Clause)

A CTE (Common Table Expression) uses WITH to define a named temporary result set that can be referenced multiple times.

▶ Example: CTE Simplifies Nested Subqueries

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

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

Approach	Readability	Reusable	Optimizer inlines	Materialized
Nested subquery	Poor	No	Yes	—
CTE	Good	Yes	PG 12+ decides automatically	Can force MATERIALIZED
Temp table	Fair	Yes	No	Writes to disk

▶ Example: CTE MATERIALIZED Forces Materialization

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

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

MATERIALIZED forces computation once and caches it, ideal for CTEs that are referenced many times with heavy computation.

▶ Example: CTE NOT MATERIALIZED Forces Inlining

SQL

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

TEXT

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

NOT MATERIALIZED lets the optimizer inline the expansion, suited to simple predicate-pushdown scenarios.

(2) Recursive CTE (WITH RECURSIVE)

A recursive CTE is the standard SQL approach for tree and graph data.

Syntax structure:

SQL

WITH RECURSIVE cte_name AS (
  base_query        -- anchor: non-recursive seed
  UNION ALL
  recursive_query   -- references cte_name itself
)
SELECT * FROM cte_name;

▶ Example: All Subordinates from the 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

 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

▶ Example: Limit Recursion Depth (Prevent Infinite Loop)

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

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

WHERE s.level < 5 limits recursion to at most 5 levels.

▶ Example: Generate a Date Series

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

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

5. Practice

▶ Example: Find Each Department's Highest-Paid Employee

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

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

▶ Example: CTE Computes Customer RFM Scores

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

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

▶ Example: Recursive CTE for a Product Category Tree

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

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

▶ Example: EXISTS Finds a Product Ordered in Every Region

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

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

▶ Example: CTE + Window Function for 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

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

6. Comprehensive Example

Alice's org-chart query—from any manager, list all subordinates with level indentation, path, and headcount:

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

 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. Recursive CTE Execution Flow

flowchart TD
    A["Anchor Query<br/>(non-recursive seed)"] --> B["Working Table T₀"]
    B --> C["Recursive Query<br/>(JOIN with T₀)"]
    C --> D{"New rows<br/>produced?"}
    D -->|Yes| E["Working Table T₁"]
    E --> F["Append to result"]
    F --> C
    D -->|No| G["Final Result<br/>(all iterations UNION ALL)"]

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

Step	Operation	Description
1	Run anchor query	Non-recursive seed, generates initial rows
2	Put into working table	T₀ = anchor result
3	Recursive query	JOIN working table with the original table
4	Check for new rows	Continue if new rows exist; terminate if none
5	Merge results	UNION ALL all iteration results

❓ FAQ

Q Is there a performance difference between a CTE and a subquery?

A PostgreSQL 12+ automatically decides whether to inline a CTE. Simple CTEs are usually inlined; complex ones may be materialized. Use MATERIALIZED / NOT MATERIALIZED to control it manually.

Q Can a recursive CTE loop infinitely?

A Possibly. If the data contains a cycle (e.g., A→B→A), recursion won't stop. Guard against it by adding a level limit, or tracking the path to avoid revisiting nodes.

Q Why does NOT IN return empty when it hits NULL?

A x NOT IN (a, NULL) is equivalent to x<>a AND x<>NULL, and x<>NULL is UNKNOWN; in the AND chain, UNKNOWN makes the whole row FALSE. NOT EXISTS is safer instead.

Q Which is faster, EXISTS or IN?

A It depends on the data and indexes. Typically IN is faster when the subquery result set is small, and EXISTS is faster when the outer table is small. The PostgreSQL optimizer rewrites automatically, so performance is close in most cases.

Q Can a recursive CTE handle graph structures?

A Yes, but it needs extra cycle-prevention logic. Track visited nodes (with an ARRAY or path string) in the recursive part to avoid revisiting.

Q Can a CTE reference a previously defined CTE?

A Yes. In WITH a AS (...), b AS (SELECT ... FROM a), b can reference a, referenced downward in definition order.

📖 Summary

Subqueries come in four kinds—scalar / column / row / table—each with its proper place
EXISTS/NOT EXISTS are safer than IN/NOT IN and unaffected by NULL
ANY is equivalent to "greater than the minimum"; ALL to "greater than the maximum"
A CTE uses WITH to define a named temporary result, far more readable than nested subqueries
PostgreSQL 12+ decides CTE inlining or materialization automatically; can be controlled manually
WITH RECURSIVE handles tree/graph data and is the standard SQL approach
A recursive CTE needs an anchor plus a recursive part, joined with UNION ALL
Prevent infinite recursion with a level limit or a tracked path

📝 Exercises

⭐ Use a scalar subquery to find employees whose salary is above the company average.
⭐ Use NOT EXISTS to find customers who have never placed an order.
⭐⭐ Rewrite the following nested subquery using a CTE: find orders whose amount is above that customer's average order amount.
⭐⭐ Use WITH RECURSIVE to query a specified manager's 3-level subordinates from the employees table, with level indentation.
⭐⭐⭐ Using a recursive CTE plus aggregation: starting from the CEO, count each manager's direct reports and the headcount of their entire subtree, all in a single SQL statement.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Subqueries and CTEs

1. What You'll Learn

2. The Story

3. Concept

(1) Subquery Classification

▶ Example: Scalar Subquery in SELECT

▶ Example: Scalar Subquery in WHERE

▶ Example: Column Subquery + IN

▶ Example: Row Subquery

(2) EXISTS / NOT EXISTS

▶ Example: EXISTS Finds Users with Orders

▶ Example: NOT EXISTS Finds Users without Orders

▶ Example: NOT IN's NULL Trap

(3) ANY / ALL

▶ Example: ANY Finds People Paid More Than Any Employee in a Department

▶ Example: ALL Finds People Paid More Than Everyone in a Department

▶ Example: Table Subquery in FROM

4. Key Points

(1) CTE (WITH Clause)

▶ Example: CTE Simplifies Nested Subqueries

▶ Example: CTE MATERIALIZED Forces Materialization

▶ Example: CTE NOT MATERIALIZED Forces Inlining

(2) Recursive CTE (WITH RECURSIVE)

▶ Example: All Subordinates from the CEO

▶ Example: Limit Recursion Depth (Prevent Infinite Loop)

▶ Example: Generate a Date Series

5. Practice

▶ Example: Find Each Department's Highest-Paid Employee

▶ Example: CTE Computes Customer RFM Scores

▶ Example: Recursive CTE for a Product Category Tree

▶ Example: EXISTS Finds a Product Ordered in Every Region

▶ Example: CTE + Window Function for Top-N

6. Comprehensive Example

7. Recursive CTE Execution Flow

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好