What is CTE in SQL?

A Common Table Expression (CTE) is a temporary, named result set that you define within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or MERGE statement. Think of it as a "temporary view" that exists only for the duration of one query.

They are primarily used to simplify complex joins and subqueries, making your SQL code more readable and maintainable.

Key characteristics of CTEs: 

• Temporary: CTEs exist only for the duration of the query in which they are defined. They are not stored as objects in the database. 
• Named: They are given a name using the WITH clause, allowing for clear identification and referencing within the main query or subsequent CTEs. 
• Reusable: A CTE can be referenced multiple times within the same query, avoiding repetitive code and improving maintainability. 
• Recursive capabilities: CTEs can be self-referential, enabling the processing of hierarchical data structures (e.g., organizational charts and bills of materials).

When to use a CTE?

Use CTE when:

• SQL Query becomes long and complicated.
• You need to reuse a subquery multiple times.
• You want to perform recursive operations.
• You want to break a complex query into steps.
• You want to reference the result set multiple times.

Let's understand how to write CTE with a few examples.

Example 1: Simple CTE to Filter Employees.

This SQL CTE will filter the list of employees getting high salaries.

WITH HighSalaryEmployees AS (
    SELECT EmployeeId, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighSalaryEmployees;

This logic is cleaner and reusable.

Example 2: CTE used multiple times.

In this example, we are creating a CTE to fetch sales records of each employee and then filtering them out based on conditions.

WITH SalesCTE AS (
    SELECT EmployeeId, TotalSales
    FROM Sales
)
SELECT * FROM SalesCTE WHERE TotalSales > 100000;

-- reuse same CTE again
SELECT EmployeeId, TotalSales * 0.10 AS Bonus
FROM SalesCTE;

Here, CTE is used in two different select queries, which shows that it is reusable code and can use multiple times.

Example 3: Recursive CTE.

This SQL uses a recursive CTE to build an employee hierarchy (CEO → Managers → Employees) and shows each employee with their hierarchy level.

WITH EmpHierarchy AS (
    -- Anchor Query (Top-Level)
    SELECT EmployeeId, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive Query
    SELECT e.EmployeeId, e.Name, e.ManagerId, h.Level + 1
    FROM Employees e
    INNER JOIN EmpHierarchy h
        ON e.ManagerId = h.EmployeeId
)
SELECT * FROM EmpHierarchy;

After executing the above query, your result set will look like this:

In one of my interviews, the interviewer asked me how we can use CTE to perform INSERT, UPDATE, or DELETE queries. At that time, I was not sure whether we could perform these operations inside CTE or not. So the correct answer is no, you cannot directly insert these queries inside CTE, but you can use CTE while performing these operations.

Note: CTE must contain only a SELECT.

Let's check a few examples:

Example 4: INSERT Query Using CTE.

WITH EmpCTE AS (
    SELECT EmployeeId, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
INSERT INTO BonusTable(EmployeeId, BonusAmount)
SELECT EmployeeId, Salary * 0.1
FROM EmpCTE;

Example 5: UPDATE Query Using CTE.

WITH EmpCTE AS (
    SELECT EmployeeId, Salary
    FROM Employees
    WHERE Salary < 30000
)
UPDATE EmpCTE
SET Salary = Salary + 2000;

Example 6: DELETE Query Using CTE.

WITH EmpCTE AS (
    SELECT EmployeeId
    FROM Employees
    WHERE Salary < 10000
)
DELETE FROM EmpCTE;

Why can a CTE not contain INSERT, UPDATE, or DELETE?

A CTE (Common Table Expression) is not a table and is not stored anywhere.

It is only a temporary result set that exists only for the duration of the next SELECT, INSERT, UPDATE, or DELETE statement.

Because of this:

❌ CTE can only generate data using a SELECT.

❌ It cannot perform data-modifying operations inside itself.

Logical Answer: A CTE is like a named subquery.

Subqueries cannot contain:

• INSERT
• UPDATE
• DELETE

They only return a dataset.

Since CTE is just a subquery with a name, it also cannot perform DML inside the definition.