SQL - Common Table Expression

SQL - Common Table Expressions (CTEs)

Common Table Expressions, or CTEs, are a powerful feature for writing cleaner, more readable, and more maintainable SQL. This chapter introduces CTEs, their syntax using the WITH clause, and how to use them for both simple and complex recursive queries.

What is a Common Table Expression?

A CTE is a named, temporary result set that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. Think of it as creating a temporary, single-use view. CTEs help break down long and complex queries into logical, reusable building blocks, drastically improving readability.

CTEs are defined using the WITH clause. This feature is part of the SQL standard and is supported by all modern RDBMS, including PostgreSQL, SQL Server, Oracle, SQLite, and MySQL (version 8.0 and newer).

Syntax

The basic syntax for a single CTE is as follows:

WITH CteName (column1, column2, ...) AS (
    -- The query that defines the CTE
    SELECT ...
    FROM ...
)
-- The main query that uses the CTE
SELECT *
FROM CteName;

Where:

• CteName: A meaningful name you assign to your temporary result set.
• (column1, …): An optional list of column names for the CTE. If omitted, the columns from the CTE’s SELECT statement are used.
• AS (...): The parentheses contain the query that generates the temporary result set.
• Main Query: The final query that can reference CteName as if it were a regular table.

Simple and Chained CTEs

Let’s use a sample employees table to see CTEs in action.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 120000, 3),
(2, 'Bob', 'Engineering', 100000, 1),
(3, 'Charlie', 'Management', 150000, NULL),
(4, 'David', 'Sales', 90000, 5),
(5, 'Eve', 'Sales', 110000, 3);

Example: A Single CTE

Find all employees in the ‘Engineering’ department.

WITH EngineeringDept AS (
    SELECT id, name, salary
    FROM employees
    WHERE department = 'Engineering'
)
SELECT name, salary
FROM EngineeringDept
ORDER BY salary DESC;

Example: Chaining Multiple CTEs

You can define multiple CTEs in a single WITH clause, separated by commas. A later CTE can even reference an earlier one. Let’s find the total salary for the Sales department and then find all employees who earn more than the Sales average.

WITH
  SalesDept AS (
    -- First CTE: Isolate sales employees
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
  ),
  SalesStats AS (
    -- Second CTE: Calculate average salary, referencing the first CTE
    SELECT AVG(salary) as avg_sales_salary
    FROM SalesDept
  )
-- Main query: Use the calculated average to find high earners
SELECT e.name, e.department, e.salary
FROM employees e, SalesStats ss
WHERE e.salary > ss.avg_sales_salary;

Recursive CTEs

A recursive CTE is one that references itself. This is incredibly useful for querying hierarchical data, such as organizational charts, file systems, or bills of materials.

A recursive CTE has two parts:

• Anchor Member: The initial query that returns the base result set (e.g., the CEO of a company).
• Recursive Member: A query that references the CTE itself and is joined with the anchor member. It executes repeatedly until it returns no more rows.
• The two members are combined with UNION ALL.

Example: Employee Hierarchy

Let’s find the entire reporting chain for ‘Alice’ (employee ID 1).

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: Start with the top-level manager, Charlie
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 3 -- Charlie is the starting point (CEO/top manager)

    UNION ALL

    -- Recursive Member: Find employees who report to the previous level
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT *
FROM EmployeeHierarchy
ORDER BY level;

Output

id	name	manager_id	level
3	Charlie	null	0
1	Alice	3	1
5	Eve	3	1
2	Bob	1	2

Advantages and Disadvantages of CTEs

Advantages

• Readability: Breaks down complex logic into understandable steps.
• Maintainability: Easier to debug and modify individual logical units.
• Recursion: Provides an elegant way to query hierarchical data, which is difficult with traditional joins.
• Reusability: A CTE can be referenced multiple times in the main query, avoiding redundant code.

Disadvantages

• Scope: A CTE is only visible to the statement immediately following it.
• Performance: While usually clear, a deeply nested or complex CTE can sometimes make it harder for the query optimizer to find the best execution plan compared to subqueries or temporary tables. Always analyze performance for critical queries.