SQL Common Table Expression: Syntax, Usage, and Examples

A SQL common table expression (CTE) is a temporary result set within a query that improves readability and modularity. Common table expressions in SQL can be used in complex queries, recursion, and aggregations.

A CTE is often described as a named result set because you define it once and then reference it by name in the rest of the statement.

How to Use a Common Table Expression in SQL

The basic syntax for a common table expression in SQL follows this structure:

WITH cte_name AS (
    SELECT column1, column2
    FROM some_table
    WHERE some_condition
)
SELECT * FROM cte_name;

• WITH clause introduces the common table expression.
• cte_name is the alias for the temporary result set.
• The enclosed SELECT statement retrieves data.
• The final query references the CTE instead of using subqueries.

Example: Using a CTE in SQL

WITH HighValueCustomers AS (
    SELECT customer_id, COUNT(order_id) AS order_count
    FROM Orders
    GROUP BY customer_id
    HAVING COUNT(order_id) > 5
)
SELECT c.first_name, c.last_name, h.order_count
FROM Customers c
JOIN HighValueCustomers h ON c.customer_id = h.customer_id;

This query first retrieves customers who placed more than five orders using the CTE and then joins that data with the Customers table.

That first block is the CTE definition, while the statement below it is the main query that uses the temporary result.

When to Use Common Table Expressions in SQL

Improving Query Readability

Long and complex queries benefit from CTEs by breaking them into smaller, named sections, making them easier to understand and debug.

This is one of the most common use cases for a CTE: turning a tangled query into smaller steps you can read without squinting at the screen.

Recursive Queries

A recursive common table expression in SQL allows hierarchical data retrieval, such as an employee-manager hierarchy.

WITH EmployeeHierarchy AS (
    SELECT employee_id, manager_id, first_name, last_name
    FROM Employees
    WHERE manager_id IS NULL  -- Start with the top-level manager

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.first_name, e.last_name
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

This query recursively fetches all employees under a specific manager.

In recursive queries, each iteration adds another level of matching rows until no more records are found.

Reusable Queries in SQL

A common table expression SQL query can be referenced multiple times within a statement without duplicating code.

WITH OrderCounts AS (
    SELECT customer_id, COUNT(order_id) AS order_count
    FROM Orders
    GROUP BY customer_id
)
SELECT c.first_name, c.last_name, oc.order_count
FROM Customers c
JOIN OrderCounts oc ON c.customer_id = oc.customer_id
WHERE oc.order_count > 3;

CTEs are especially useful in back-end development for organizing complex queries and improving database efficiency. If you want to learn more about using SQL in real-world applications, check out our Back-End Development course.

Examples of Common Table Expressions SQL

Filtering Aggregated Data

Using a CTE simplifies filtering aggregated values.

WITH ProductSales AS (
    SELECT product_id, SUM(quantity) AS total_sold
    FROM OrderDetails
    GROUP BY product_id
)
SELECT p.product_name, ps.total_sold
FROM Products p
JOIN ProductSales ps ON p.product_id = ps.product_id
WHERE ps.total_sold > 500;

Recursive CTE for Parent-Child Relationships

This example retrieves hierarchical data, such as company departments.

WITH Recursive DepartmentHierarchy AS (
    SELECT department_id, department_name, parent_department_id
    FROM Departments
    WHERE parent_department_id IS NULL  -- Top-level department

    UNION ALL

    SELECT d.department_id, d.department_name, d.parent_department_id
    FROM Departments d
    JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
SELECT * FROM DepartmentHierarchy;

Without a proper stop condition, recursive logic can keep going like an infinite loop, which is why you need a clear way for the recursion to end.

Using Multiple CTEs in One Query

Multiple CTEs can be defined in a single query by separating them with commas.

WITH
    CustomersWithOrders AS (
        SELECT customer_id, COUNT(order_id) AS order_count
        FROM Orders
        GROUP BY customer_id
    ),
    HighValueCustomers AS (
        SELECT customer_id
        FROM CustomersWithOrders
        WHERE order_count > 10
    )
SELECT c.first_name, c.last_name
FROM Customers c
JOIN HighValueCustomers hvc ON c.customer_id = hvc.customer_id;

In this example, CustomersWithOrders acts as the first CTE, while HighValueCustomers acts as the second CTE.

That pattern is handy when one CTE builds on another, like stacked building blocks instead of one giant SQL query.

Learn More About Common Table Expressions in SQL

CTEs vs. Subqueries

CTEs and subqueries both allow temporary result sets, but CTEs offer:

• Better readability by naming result sets.
• The ability to reference the same dataset multiple times.
• Easier debugging and step-by-step execution.

For example, a nested subquery:

SELECT first_name, last_name, total_sold
FROM (
    SELECT p.product_id, p.product_name, SUM(od.quantity) AS total_sold
    FROM Products p
    JOIN OrderDetails od ON p.product_id = od.product_id
    GROUP BY p.product_id, p.product_name
) AS ProductSales
WHERE total_sold > 500;

Using a CTE:

WITH ProductSales AS (
    SELECT p.product_id, p.product_name, SUM(od.quantity) AS total_sold
    FROM Products p
    JOIN OrderDetails od ON p.product_id = od.product_id
    GROUP BY p.product_id, p.product_name
)
SELECT first_name, last_name, total_sold
FROM ProductSales
WHERE total_sold > 500;

When to Use CTEs vs. Temporary Tables

CTEs:

• Exist only within the query.
• Do not persist beyond execution.
• Best for improving readability and recursion.

Temporary tables:

• Persist for the duration of a session.
• Can be indexed for better performance on large datasets.
• Useful when needing intermediate results across multiple queries.

Example of a temporary table:

CREATE TEMP TABLE HighValueOrders AS
SELECT order_id, customer_id, total_amount
FROM Orders
WHERE total_amount > 1000;

How to Use Common Table Expressions in SQL for Updates and Deletes

Updating Data Using a CTE

CTEs can be used in UPDATE statements to modify data.

WITH DiscountedProducts AS (
    SELECT product_id, price
    FROM Products
    WHERE price > 50
)
UPDATE Products
SET price = price * 0.9
WHERE product_id IN (SELECT product_id FROM DiscountedProducts);

Deleting Data Using a CTE

CTEs also work with DELETE statements.

WITH OldOrders AS (
    SELECT order_id
    FROM Orders
    WHERE order_date < '2020-01-01'
)
DELETE FROM Orders
WHERE order_id IN (SELECT order_id FROM OldOrders);

Common CTE Details Across Databases

Many database systems support CTEs, including PostgreSQL, MySQL, and SQL Server. Even so, the exact behavior can vary a bit from one platform to another.

A recursive CTE in SQL Server can use MAXRECURSION to cap how many levels the query is allowed to process.

When you define a CTE, you can optionally provide a column list after the CTE name to label the output columns clearly.

That matters in a relational database, where readable naming makes joined datasets much easier to follow.

A CTE can also work with joins such as INNER JOIN or LEFT JOIN, depending on whether you want only matching rows or all rows from one side.

You’ll also see CTEs used with aggregates like AVG when people need summary logic before joining or filtering the results.

Some teams wrap repeated logic inside stored procedures, but a CTE still helps keep the inner query readable.

Common table expressions in SQL provide a structured way to simplify queries, handle recursion, and improve readability. They are particularly useful for hierarchical data, aggregated filters, and temporary data sets within a query.