Common Table Expressions (CTEs) Explained

Learn how to simplify complex queries and improve readability with SQL's Common Table Expressions.

What is a Common Table Expression?

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SQL query. It allows you to break down complex queries into smaller, more manageable parts, improving readability and maintainability.

CTEs are defined using the WITH keyword, followed by a name for the CTE and a subquery enclosed in parentheses. The result of this subquery can then be used as if it were a table in the main query.

How CTEs Work

When you define a CTE, it is only available for the duration of the query execution. Unlike temporary tables, CTEs do not persist beyond the query itself. This makes them a great tool for structuring queries without cluttering the database.

The syntax of a CTE follows this structure:

-- Basic CTE
WITH recent_orders AS (
    SELECT order_id, customer_id, order_date
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT * FROM recent_orders;

Here’s what happens step-by-step:

• The WITH clause defines the CTE.
• A name is assigned to the CTE (in this case, recent_orders).
• The subquery inside the parentheses is executed, generating the temporary dataset.
• The main query then references the CTE as if it were a regular table.

Recursive CTEs

Recursive CTEs are used to work with hierarchical or tree-structured data, such as organizational charts or file systems. They reference themselves within the query to repeatedly execute until a stopping condition is met.

-- Recursive CTE for Hierarchical Data
WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, e.employee_name, h.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM employee_hierarchy;

Using Multiple CTEs

SQL allows defining multiple CTEs within a single WITH clause. This is useful for breaking down complex logic into structured parts.

-- Multiple CTEs
WITH customers_with_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
),

high_value_customers AS (
    SELECT customer_id
    FROM customers_with_orders
    WHERE order_count > 10
)
SELECT * FROM high_value_customers;