A CTE (Common Table Expression) is a temporary named result set that you can reference within a single SQL statement.

Think of it as:

  • A named subquery
  • A way to make complex SQL cleaner and readable
  • A tool for hierarchical (recursive) queries

📌 A CTE exists only while the query runs.

1.Basic CTE Syntax

WITH cte_name (column1, column2, ...)
AS
(
SELECT column1, column2
FROM table_name
)
SELECT *
FROM cte_name;

Rules

✔ WITH keyword starts a CTE
✔ Must be followed immediately by a query
✔ Cannot be reused outside the statement

2.Simple CTE Example

Employees Table

EmployeeID Name Salary
1 John 50000
2 Jane 60000

Query

WITH EmployeeCTE AS
(
SELECT EmployeeID, Name, Salary
FROM Employees
)
SELECT *
FROM EmployeeCTE;

3.CTE vs Subquery

Subquery

SELECT *
FROM (
SELECT EmployeeID, Name
FROM Employees
) AS emp;

CTE (Better readability)

WITH emp AS
(
SELECT EmployeeID, Name
FROM Employees
)
SELECT *
FROM emp;

✔ CTE is easier to read and maintain

4.CTE with WHERE Clause

WITH HighSalary AS
(
SELECT Name, Salary
FROM Employees
WHERE Salary > 55000
)
SELECT *
FROM HighSalary;

5.CTE with JOIN

WITH EmployeeDept AS
(
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID
)
SELECT *
FROM EmployeeDept;

6.Multiple CTEs in One Query

WITH ITEmployees AS
(
SELECT Name FROM Employees WHERE Department = 'IT'
),
HREmployees AS
(
SELECT Name FROM Employees WHERE Department = 'HR'
)
SELECT * FROM ITEmployees
UNION
SELECT * FROM HREmployees;

7.Recursive CTE (Very Important)

Used for hierarchical data like:
✔ Employee → Manager
✔ Category → Subcategory
✔ Tree structures

Employees Table

EmployeeID Name ManagerID
1 CEO NULL
2 Manager 1
3 Developer 2

Recursive CTE Example

WITH EmployeeHierarchy AS
(
-- Anchor member
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL UNION ALL

-- Recursive member
SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh
ON e.ManagerID = eh.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

8.CTE with UPDATE

WITH SalaryCTE AS
(
SELECT EmployeeID, Salary
FROM Employees
)
UPDATE SalaryCTE
SET Salary = Salary + 3000;

Categorized in:

SQL Server,