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:

• Split big queries into smaller, reusable pieces.
• Makes the query easier to read and understand.
• Can handle recursive queries, useful for things like employee–manager hierarchies.

Let’s consider an Employees table that contains employee details such as EmployeeID, Name, Department, Salary, and ManagerID. This table is used to demonstrate how to use a Common Table Expression (CTE) to simplify SQL queries, particularly when aggregating or filtering data.

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 and is useful for querying hierarchical data, such as employees and their managers stored in the same table. It repeatedly executes until the full hierarchy is returned. To avoid infinite loops from incorrect definitions, use the MAXRECURSION hint in the query’s OPTION clause.

Recursive CTEs consist of two parts:

1. Anchor member: The initial query that selects the base case (e.g., top-level managers).
2. 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

Here, are some key differences between CTE and Subqueries are give below: