.png)
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;
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;
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;
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.
Latest Google News, Updates, and Features. Everything You Need to Know About Google