Learn SQL with PostgreSQL - Common Table Expressions (CTE) & Recursive Queries

#Introduction

Welcome to episode 10 of the Learn SQL with PostgreSQL series! In episode 7, we met subqueries nested inside a query. But there's one problem: deep, layered subqueries very quickly become unreadable. The elegant solution is Common Table Expressions (CTE).

In this episode, we'll cover non-recursive CTEs and how to compose multiple CTEs in a single query, then move into recursive CTEs with the anchor member + UNION ALL + recursive member syntax, and apply them to hierarchical data such as organizational structures and category taxonomies.

#Non-Recursive CTE: The WITH Clause

CTE is a named temporary result table that lives only within a single query. The syntax is simple:

WITH nama_cte AS (
    query_tabel_sementara
)
SELECT ... FROM nama_cte;

#Example: Building Modular Queries

Take the case from episode 7: finding users whose total spending is above average. Without a CTE, the query is layered and hard to read. With a CTE, we break it into named steps:

WITH ringkasan_order AS (
    SELECT customer_id, SUM(total) AS total_belanja
    FROM orders
    GROUP BY customer_id
),
rata_rata AS (
    SELECT AVG(total_belanja) AS nilai_rata FROM ringkasan_order
)
SELECT u.email, r.total_belanja
FROM ringkasan_order r
JOIN users u ON u.id = r.customer_id
CROSS JOIN rata_rata ra
WHERE r.total_belanja > ra.nilai_rata
ORDER BY r.total_belanja DESC;

#Multiple CTEs: Breaking Down Giant Queries

One of CTE's strengths is the ability to break a giant query into pieces that can be tested one by one:

WITH
jumlah_pelanggan AS (
    SELECT country, COUNT(*) AS total FROM users GROUP BY country
),
revenue AS (
    SELECT c.country, SUM(o.total) AS total_revenue
    FROM orders o
    JOIN users u ON u.id = o.customer_id
    JOIN customers c ON c.id = u.id
    GROUP BY c.country
)
SELECT j.country, j.total AS pelanggan, r.total_revenue
FROM jumlah_pelanggan j
JOIN revenue r USING (country)
ORDER BY r.total_revenue DESC;

#CTE vs Subquery: When to Use Which?

#Recursive CTE: WITH RECURSIVE

Recursive CTE allows a query to call itself, making it possible to process hierarchical data: organizational structures, category taxonomies, comment trees, bill of materials, and networks.

#Recursive CTE Syntax

A recursive CTE consists of two parts joined by UNION ALL:

WITH RECURSIVE nama AS (
    query_anchor      -- titik awal (seed)
    UNION ALL
    query_recursive   -- memanggil nama sendiri
)
SELECT ... FROM nama;

• Anchor member: the initial query that produces the first rows (usually the root node / level 0).
• Recursive member: the query that references the CTE itself, producing the rows for the next level.
• The process repeats until the recursive member produces no more rows.

#Case Study: Organizational Structure

Suppose we have the employees table from episode 6 (with manager_id). To display the entire hierarchy with its depth level:

WITH RECURSIVE hierarki AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarki h ON e.manager_id = h.id
)
SELECT id, name, level
FROM hierarki
ORDER BY level, name;

The anchor member selects the CEO (employees without a manager). The recursive member then takes the employees whose manager is a row from the previous result, incrementing the level. The result: every employee with their depth level in the organization.

#Case Study: Category Taxonomy

The same pattern applies to nested product categories:

WITH RECURSIVE kategori_tree AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id,
           kt.path || ' > ' || c.name
    FROM categories c
    JOIN kategori_tree kt ON c.parent_id = kt.id
)
SELECT id, name, path
FROM kategori_tree
ORDER BY path;

Beyond depth, we also build a path that shows the full trail: Electronics > Laptop > Gaming. This is an example of recursive CTE power that a plain join cannot replicate.

#Limiting Depth: Adding a Guard

For potentially problematic data, add a depth limit in the recursive member:

WITH RECURSIVE hierarki AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarki h ON e.manager_id = h.id
    WHERE h.level < 10
)
SELECT id, name, level FROM hierarki;

#Closing

Key takeaways:

• CTE makes complex queries read like a sequential story — good names are documentation.
• Multiple CTEs can reference each other within a single query.
• WITH RECURSIVE allows a query to call itself for tree data.
• Recursive syntax: anchor member + UNION ALL + recursive member.
• Always install a depth guard to prevent endless loops.

In the next episode, episode 11, we get into the foundation of database reliability: Transaction Management, ACID & Concurrency Control — from the principles of Atomicity, Consistency, Isolation, Durability, the BEGIN, COMMIT, ROLLBACK, and SAVEPOINT commands, isolation levels, to explicit locking with SELECT ... FOR UPDATE to prevent race conditions.