What is CTE in SQL?

CTE (Common Table Expression) is a temporary named result set that you can use inside a single SQL statement to make complex queries more readable, reusable, and maintainable.

A CTE is defined using the WITH keyword.

We use CTE:

• To simplify complex queries
• To avoid repeating subqueries
• To handle hierarchical or recursive data
• To improve query readability.

Basic Syntax:

WITH cte_name AS (
    SELECT columns
    FROM table
    WHERE condition
)
SELECT *
FROM cte_name;

Simple Example: Find employees earning more than the average salary

WITH AvgSalaryCTE AS (
    SELECT AVG(Salary) AS AvgSalary
    FROM Employee
)
SELECT *
FROM Employee
WHERE Salary > (SELECT AvgSalary FROM AvgSalaryCTE);

Explanation:

• AvgSalaryCTE calculates the average salary.
• The main query uses that result to filter employees.
• CTE exists only during this query execution.

How is CTE better than a subquery?

A CTE (Common Table Expression) is often better than a subquery because it improves readability, reusability, and maintainability, especially in complex SQL queries.

Example: Using Subquery.

SELECT *
FROM Employee
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employee
);

Example: Using CTE.

WITH AvgSalaryCTE AS (
    SELECT AVG(Salary) AS AvgSalary
    FROM Employee
)
SELECT *
FROM Employee
WHERE Salary > (SELECT AvgSalary FROM AvgSalaryCTE);

Note: CTE separates logic into named blocks, making queries easier to understand.

Recursive CTE.

A Recursive CTE (Common Table Expression) is a CTE that refers to itself to process hierarchical or tree-structured data (parent → child relationships).

It repeatedly executes until no more rows are returned.

Example: Employee–Manager Hierarchy.

WITH EmpHierarchy AS (
    -- Anchor Query (Top-Level Manager)
    SELECT 
        EmpId,
        Name,
        ManagerId,
        0 AS Level
    FROM Employee
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive Query (Subordinates)
    SELECT 
        e.EmpId,
        e.Name,
        e.ManagerId,
        h.Level + 1
    FROM Employee e
    INNER JOIN EmpHierarchy h
        ON e.ManagerId = h.EmpId
)
SELECT * FROM EmpHierarchy;

Output:

EmpId | Name   | ManagerId | Level
1     | CEO    | NULL      | 0
2     | Manager| 1         | 1
3     | Dev    | 2         | 2
4     | Tester | 2         | 2

Use a recursive CTE when the data is hierarchical:

• Employee–Manager structure
• Category–Subcategory
• Organization chart
• Folder–Subfolder
• Tree or graph traversal
• Bill of materials

CTE is better than a subquery because it improves readability, avoids repeated logic, supports recursion, and makes complex SQL queries easier to maintain, while performance remains similar.

Note: A CTE (Common Table Expression) is temporary and exists only for the very next SQL statement that follows the WITH clause. Once that statement finishes execution, the CTE is gone.

Example: Invalid Use of CTE.

WITH EmpCTE AS (
    SELECT * FROM Employee
)
SELECT * FROM EmpCTE;

SELECT COUNT(*) FROM EmpCTE;  -- ❌ ERROR

A CTE can be referenced multiple times within a single SQL statement, but it cannot be used across multiple SELECT statements.