Common Table Expression (CTE) in SQL

A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a single query. CTEs simplify complex queries, make them easier to read and can be reused multiple times within the same query. It is used for:

• Performing recursive operations, such as traversing hierarchical data.
• Breaking down multi-step calculations into manageable parts.
• Replacing nested subqueries in complex data retrieval tasks.

Consider an employees table containing employee details such as employee_id, name, department, salary and manager_id. We will use this table to demonstrate Common Table Expression (CTE) examples.

This table represents the hierarchical structure of employees within an organization, based on a recursive CTE query.

Example: Calculate Average salary by department

This example uses a CTE to find the average salary of each department, making the query simpler and easier to read.

Query:

WITH avg_salary_by_dept AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM avg_salary_by_dept;

Output:

• The WITH clause defines a CTE named avg_salary_by_dept.
• The main query references this CTE to retrieve the average salary for each department.

Syntax

WITH cte_name AS (
    SELECT query
)
SELECT *
FROM cte_name;

• cte_name: A unique name for the CTE expression.
• query: A valid SQL query that returns a result set, which will be treated as a virtual table within the main query.
• SELECT: The main query that can reference the CTE by its name.

Recursive Common Table Expression 

A recursive CTE references itself to retrieve hierarchical data, such as employee-manager relationships. Use MAXRECURSION to prevent infinite loops. It consist of two parts:

• Anchor member: The initial query that selects the base case (e.g., top-level managers).
• Recursive member: The query that references the CTE itself, pulling the next level of data.

Example: Hierarchical Employee Data

The recursive CTE can be used to represent employees according to their reporting hierarchy. The following steps show how to identify top-level employees, find their reporting employees and display the hierarchy.

Step 1: Select Top-Level Employees

SELECT employee_id,
       first_name,
       last_name,
       manager_id,
       1 AS emp_level
FROM employees
WHERE manager_id IS NULL;

• This selects employees who do not have a manager and assigns them Level 1.

Step 2: Find Employees Reporting to Them

UNION ALL

SELECT e.employee_id,
       e.first_name,
       e.last_name,
       e.manager_id,
       r.emp_level + 1
FROM employees e
INNER JOIN cte_reports r
    ON e.manager_id = r.employee_id;

• This finds employees who report to the selected managers and increases their hierarchy level by 1.

Step 3: Display the Hierarchy

SELECT first_name || ' ' || last_name AS full_name,
       emp_level,
       (
           SELECT first_name || ' ' || last_name
           FROM employees
           WHERE employee_id = cte_reports.manager_id
       ) AS manager
FROM cte_reports
ORDER BY emp_level, manager_id;

• This displays the employee's full name, hierarchy level and manager name.

Complete Query

WITH RECURSIVE cte_reports(
    employee_id,
    first_name,
    last_name,
    manager_id,
    emp_level
) AS (
    SELECT employee_id,
           first_name,
           last_name,
           manager_id,
           1
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id,
           e.first_name,
           e.last_name,
           e.manager_id,
           r.emp_level + 1
    FROM employees e
    INNER JOIN cte_reports r
        ON e.manager_id = r.employee_id
)
SELECT first_name || ' ' || last_name AS full_name,
       emp_level,
       (
           SELECT first_name || ' ' || last_name
           FROM employees
           WHERE employee_id = cte_reports.manager_id
       ) AS manager
FROM cte_reports
ORDER BY emp_level, manager_id;

Output:

• Michael Anderson is at Level 1 with no manager.
• Emily Johnson and Daniel Williams are at Level 2 reporting to him.
• Sophia Miller and Ethan Brown are at Level 3 reporting to Emily Johnson.

Uses of CTEs

CTEs (Common Table Expressions) help simplify and organize complex SQL queries.

• Breaks complex queries into small, reusable parts.
• Makes queries easier to read and understand.
• Helps work with hierarchical data using recursion.

Limitations

While useful, CTEs come with a few practical constraints.

• Temporary: A CTE works only while the query runs, then it disappears.
• Performance: On very large data, CTEs can be slower if reused many times.
• Restrictions: Some databases limit using CTEs with INSERT, UPDATE or DELETE.

CTE vs. Subqueries