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 EmployeeID, Name, Department, Salary and ManagerID. 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 AvgSalaryByDept AS (
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
)
SELECT *
FROM AvgSalaryByDept;

Output:

• The WITH clause defines a CTE named AvgSalaryByDept.
• 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

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )

SELECT
  FirstName + ' ' + LastName AS FullName, 
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees 
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports 
ORDER BY EmpLevel, MgrID 

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 of CTEs in SQL

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