KAHIBARO
Discord Login Register ☰
☰

10.16. Common Table Expressions

Table of Contents

Understanding Common Table Expressions

Common Table Expressions, usually called CTEs, let you write temporary, named result sets inside a query. You can think of a CTE as a query inside a query, with a name that you can reuse.

CTEs make complex SQL easier to read, debug, and maintain, especially compared to very deeply nested subqueries.

In this chapter we focus on how to write and use CTEs, not on basic SQL or joins, which are covered in other chapters.

Basic CTE Syntax

A CTE has a simple pattern:

sql 
WITH cte_name AS (
    -- some SELECT query
    SELECT ...
)
SELECT ...
FROM cte_name;

You define the CTE first, then use it in a main query that follows.

A very small example:

sql 
WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 70000
)
SELECT name, salary
FROM high_salary_employees
ORDER BY salary DESC;

Here:

CTE rule:
Every CTE must start with WITH, have a name, and contain one SELECT query in parentheses, followed by a main query that uses it.

When to Use a CTE Instead of a Subquery

You could write the same logic with a subquery:

sql 
SELECT name, salary
FROM (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 70000
) AS high_salary_employees
ORDER BY salary DESC;

Both versions work. You usually prefer a CTE when:

Naming Columns in a CTE

You can optionally list column names right after the CTE name:

sql 
WITH high_salary_employees (emp_id, emp_name, emp_salary) AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 70000
)
SELECT emp_name, emp_salary
FROM high_salary_employees;

If you do not list columns there, the names from the inner SELECT are used.

The number of column names must match the number of columns returned by the SELECT.

Important:
If you list column names after the CTE name, they replace the inner query column names for the outer query.

Multiple CTEs in One Query

You can define more than one CTE in a single WITH clause, separated by commas:

sql 
WITH recent_orders AS (
    SELECT id, customer_id, total_amount, created_at
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
big_spenders AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM recent_orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 1000
)
SELECT c.id, c.name, b.total_spent
FROM customers c
JOIN big_spenders b ON c.id = b.customer_id
ORDER BY b.total_spent DESC;

Here:

Order matters. Each CTE can use CTEs defined before it in the list, but not after.

Example: Step by Step Data Preparation

Assume you have:

You want: total quantity and total revenue per product for the last month, but only for products with more than 50 units sold.

sql 
WITH last_month_orders AS (
    SELECT id
    FROM orders
    WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
      AND created_at <  DATE_TRUNC('month', CURRENT_DATE)
),
last_month_items AS (
    SELECT oi.product_id,
           oi.quantity,
           oi.quantity * oi.unit_price AS line_revenue
    FROM order_items oi
    JOIN last_month_orders o ON oi.order_id = o.id
),
product_totals AS (
    SELECT product_id,
           SUM(quantity) AS total_qty,
           SUM(line_revenue) AS total_revenue
    FROM last_month_items
    GROUP BY product_id
)
SELECT product_id, total_qty, total_revenue
FROM product_totals
WHERE total_qty > 50
ORDER BY total_revenue DESC;

Each CTE is a simple piece. Together they form a clear pipeline.

Using CTEs for Readable Stepwise Queries

CTEs shine when you want to:

Example: Active Customers With Their Latest Order

Tables:

Goal: list only active customers and their latest order total, if any.

Without CTEs, a typical solution uses a subquery inside a join:

sql 
SELECT c.id, c.name, o.total_amount, o.created_at
FROM customers c
LEFT JOIN orders o
    ON o.id = (
        SELECT id
        FROM orders
        WHERE customer_id = c.id
        ORDER BY created_at DESC
        LIMIT 1
    )
WHERE c.status = 'active';

With CTEs you can separate the steps:

sql 
WITH active_customers AS (
    SELECT id, name
    FROM customers
    WHERE status = 'active'
),
latest_orders AS (
    SELECT DISTINCT ON (customer_id)
           customer_id,
           total_amount,
           created_at
    FROM orders
    ORDER BY customer_id, created_at DESC
)
SELECT ac.id, ac.name,
       lo.total_amount,
       lo.created_at
FROM active_customers ac
LEFT JOIN latest_orders lo
       ON ac.id = lo.customer_id
ORDER BY ac.name;

Here each CTE has a clear job:

Example: Filtering Before Aggregation

Imagine page_views(user_id, page, viewed_at).

Goal: For each user, count views in the last 7 days, then only keep users with more than 100 views.

sql 
WITH last_week AS (
    SELECT user_id, page, viewed_at
    FROM page_views
    WHERE viewed_at >= NOW() - INTERVAL '7 days'
),
user_view_counts AS (
    SELECT user_id, COUNT(*) AS view_count
    FROM last_week
    GROUP BY user_id
)
SELECT user_id, view_count
FROM user_view_counts
WHERE view_count > 100
ORDER BY view_count DESC;

This makes both the filter and aggregation steps obvious.

Recursive CTEs

A recursive CTE is a powerful feature that lets you repeat a query until a condition is met.

Use cases:

Recursive CTE Syntax

General pattern:

sql 
WITH RECURSIVE cte_name AS (
    -- 1. Anchor member: base query, runs once
    SELECT ...
    FROM ...
    WHERE ...       -- base condition
    UNION ALL
    -- 2. Recursive member: refers to cte_name
    SELECT ...
    FROM ...
    JOIN cte_name ON ...   -- use previous results
    WHERE ...              -- stop condition
)
SELECT * FROM cte_name;

Recursive CTE rules:

  1. Use WITH RECURSIVE keyword.
  2. The CTE body has two parts joined by UNION or UNION ALL:
    • Anchor member (no recursion).
    • Recursive member (refers to the CTE itself).
  3. The recursive part must have a condition that eventually stops, or the query can loop forever.

Example: Employee Hierarchy

Table employees:

idnamemanager_id
1CEONULL
2VP Sales1
3VP Tech1
4Sales Rep A2
5Sales Rep B2
6Engineer A3
7Engineer B3

Goal: Find all employees under manager id = 1 with their levels in the hierarchy.

sql 
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: start from the top manager
    SELECT
        id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE id = 1
    UNION ALL
    -- Recursive: find direct reports of people already in the hierarchy
    SELECT
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1 AS level
    FROM employees e
    JOIN employee_hierarchy eh
      ON e.manager_id = eh.id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, id;

Output:

idnamemanager_idlevel
1CEONULL0
2VP Sales11
3VP Tech11
4Sales Rep A22
5Sales Rep B22
6Engineer A32
7Engineer B32

You can change the starting manager by changing the WHERE id = 1 condition.

Example: Path and Depth

You can also track the path:

sql 
WITH RECURSIVE employee_hierarchy AS (
    SELECT
        id,
        name,
        manager_id,
        0 AS level,
        name::text AS path
    FROM employees
    WHERE id = 1
    UNION ALL
    SELECT
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1 AS level,
        eh.path || ' > ' || e.name AS path
    FROM employees e
    JOIN employee_hierarchy eh
      ON e.manager_id = eh.id
)
SELECT id, name, level, path
FROM employee_hierarchy
ORDER BY level, id;

Now path shows the full chain, like CEO > VP Tech > Engineer A.

Example: Simple Number Series

You can generate sequences of numbers without a table.

Goal: numbers from 1 to 10.

sql 
WITH RECURSIVE numbers AS (
    SELECT 1 AS n          -- anchor
    UNION ALL
    SELECT n + 1           -- recursive
    FROM numbers
    WHERE n < 10           -- stop condition
)
SELECT n FROM numbers;

Output: 1,2,3,4,5,6,7,8,9,10.

This is useful for date ranges, reports per day, filling gaps, and more.

Recursive CTE for Category Trees

Table categories:

idnameparent_id
1ElectronicsNULL
2Phones1
3Laptops1
4Android2
5iOS2
6Gaming3

Goal: get a full tree under "Electronics" with depth and full path.

sql 
WITH RECURSIVE category_tree AS (
    -- start from the root category
    SELECT
        id,
        name,
        parent_id,
        0 AS depth,
        name::text AS path
    FROM categories
    WHERE name = 'Electronics'
    UNION ALL
    -- find children of categories already in the tree
    SELECT
        c.id,
        c.name,
        c.parent_id,
        ct.depth + 1 AS depth,
        ct.path || ' > ' || c.name AS path
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, depth, path
FROM category_tree
ORDER BY depth, name;

Avoiding Infinite Loops

If your data has a cycle (for example, an employee managed by their own subordinate), a recursive CTE can loop forever. Many databases have a recursion depth limit, but you still should protect yourself.

A common trick is to store visited IDs in a text or array column and check that you do not revisit them. Example with a path string:

sql 
WITH RECURSIVE safe_hierarchy AS (
    SELECT
        id,
        name,
        manager_id,
        0 AS level,
        id::text AS visited_ids
    FROM employees
    WHERE id = 1
    UNION ALL
    SELECT
        e.id,
        e.name,
        e.manager_id,
        sh.level + 1 AS level,
        sh.visited_ids || ',' || e.id::text AS visited_ids
    FROM employees e
    JOIN safe_hierarchy sh ON e.manager_id = sh.id
    WHERE sh.visited_ids NOT LIKE '%' || e.id::text || '%'
)
SELECT id, name, level
FROM safe_hierarchy;

The WHERE clause blocks revisiting any id that is already part of visited_ids.

Using CTEs With Aggregations and Window Functions

CTEs are very often combined with aggregations and window functions to structure complex analytical queries.

Example: Top N per Group

Goal: For each department, find the top 3 highest paid employees.

Tables:

sql 
WITH ranked_salaries AS (
    SELECT
        e.id,
        e.name,
        e.department_id,
        e.salary,
        RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS salary_rank
    FROM employees e
)
SELECT
    rs.id,
    rs.name,
    rs.department_id,
    rs.salary
FROM ranked_salaries rs
WHERE rs.salary_rank <= 3
ORDER BY rs.department_id, rs.salary DESC;

The window function RANK() adds the ranking, and the outer query just filters.

Example: Monthly Revenue and Percentage Change

Tables:

Goal: For each month, show total revenue and percentage change vs previous month.

sql 
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
),
monthly_with_prev AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    prev_revenue,
    CASE
        WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL
        ELSE ROUND(
            (revenue - prev_revenue) * 100.0 / prev_revenue,
            2
        )
    END AS pct_change
FROM monthly_with_prev
ORDER BY month;

Pipeline:

  1. monthly_revenue: aggregate by month.
  2. monthly_with_prev: attach previous month revenue using LAG.
  3. Final SELECT: calculate percentage change.

This approach is easier to understand than one giant query with nested subqueries.

CTEs vs Subqueries vs Views

CTEs are similar to subqueries and views, but they serve different purposes.

FeatureCTESubqueryView
ScopeSingle querySingle queryDatabase wide
Definition placeAt top with WITHInline in FROM / WHERECreated once, stored in DB
Reusable in SQLOnly in that main queryOnly where writtenReusable across many queries
Good forComplex one off reportsSimple in place logicShared common query patterns

Rules of thumb:

Some databases treat CTEs as optimization barriers, which can affect performance compared to inline views or subqueries. Modern versions of PostgreSQL and others often optimize CTEs better, but you should still measure performance for very heavy queries.

Practical Tips and Patterns

Pattern: Cleaning and Reusing Filters

Instead of copying the same filter in different places, put it in a CTE.

Bad practice:

sql 
SELECT COUNT(*) 
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '7 days';
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE status = 'completed'
  AND created_at >= NOW() - INTERVAL '7 days'
GROUP BY customer_id;

Better with a CTE:

sql 
WITH last_week_completed AS (
    SELECT *
    FROM orders
    WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '7 days'
)
SELECT COUNT(*) FROM last_week_completed;
SELECT customer_id, SUM(total_amount)
FROM last_week_completed
GROUP BY customer_id;

You can even combine both into one query with multiple final SELECTs in some environments, but many frameworks expect a single result set. For clarity in application code you usually keep one SELECT per statement.

Pattern: Limiting Recursion Depth

Always consider a maximum depth in recursive CTEs to avoid pathological cases.

sql 
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
    WHERE eh.level < 10   -- do not go deeper than 10 levels
)
SELECT * FROM employee_hierarchy;

This gives you a safety net even if the data is not perfect.

Summary

With these patterns, you can write clearer, more maintainable SQL for complex reporting and hierarchical data, which is very useful in backend development.

Views: 9

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3085 / 24h
Impressum / Imprint
Datenschutz / Data Policy