What Is a CTE?
A CTE (Common Table Expression) is a temporary, named result set that exists only for the duration of a single query. Think of it as a throwaway mini-table that you define at the top of your query, use immediately below, and then it disappears.
CTEs are not stored anywhere on disk. They don't appear in your database as objects. They live in memory only while the query runs, and then they are gone — which is exactly why they are perfect for making complex queries readable without polluting your schema.
Basic Syntax
Every CTE starts with the keyword WITH, a name, and the list of columns. The actual query follows in parentheses. Right after the closing bracket, you use the CTE name just like a real table.
WITH CteName (Column1, Column2, ...) AS (
-- any SELECT query goes here
SELECT ...
FROM SomeTable
)
SELECT * FROM CteName;
That's the whole pattern — WITH → name → columns → AS → query in brackets → then use it.
A Simple Example
Let's take a small Sales table. It has many columns, but we only care about a light summary for a quick report.
CREATE TABLE Sales (
SaleId INT PRIMARY KEY,
ProductName VARCHAR(100),
Region VARCHAR(50),
Amount DECIMAL(10,2),
SaleDate DATE
);
INSERT INTO Sales VALUES
(1, 'Laptop', 'North', 55000, '2026-04-01'),
(2, 'Keyboard', 'South', 1800, '2026-04-02'),
(3, 'Laptop', 'North', 62000, '2026-04-03'),
(4, 'Monitor', 'East', 14000, '2026-04-04'),
(5, 'Keyboard', 'North', 1900, '2026-04-05');
Using a CTE to Build a Regional Summary
WITH RegionTotal AS (
SELECT Region,
SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region
)
SELECT Region, TotalSales
FROM RegionTotal
WHERE TotalSales > 20000;
Result:
| Region | TotalSales |
|---|---|
| North | 118900 |
The CTE RegionTotal builds a small summary, and the main query reads from it like any regular table. The same logic is possible with a subquery, but a CTE reads top-to-bottom like plain English.
Why Use a CTE?
Four clear reasons, and you will hit all four in real projects:
1. Readability. A big query with nested subqueries gets ugly fast. A CTE lets you name each step, making the query easy to scan.
2. Reusability inside the same query. You can reference the same CTE more than once in a query — unlike a subquery, which you would have to paste multiple times.
3. Recursion. CTEs can call themselves — the only clean way to handle hierarchical data (manager-employee trees, category-subcategory trees) in pure SQL.
4. No schema clutter. Unlike views or temp tables, CTEs don't create any permanent object. Perfect for one-off reports or steps inside a stored procedure.
CTE vs View vs Temporary Table
| Feature | CTE | View | Temp Table |
|---|---|---|---|
| Scope | Single query only | Entire database | Session / connection |
| Stored on disk? | No | Metadata stored | Yes (tempdb) |
| Can hold data? | No (re-computed) | No (query only) | Yes (real rows) |
| Supports recursion? | Yes | No | No |
| Best for | Clean one-off queries | Reusable logic across queries | Heavy intermediate data |
Recursive CTE: A Quick Taste
This is the CTE's super-power. A recursive CTE refers to itself — useful for walking trees like an employee-manager hierarchy.
WITH EmpTree AS (
-- Anchor: the CEO (no manager)
SELECT EmpId, EmpName, ManagerId, 1 AS Level
FROM Employees
WHERE ManagerId IS NULL
UNION ALL
-- Recursive step: employees reporting to known managers
SELECT e.EmpId, e.EmpName, e.ManagerId, t.Level + 1
FROM Employees e
INNER JOIN EmpTree t ON e.ManagerId = t.EmpId
)
SELECT * FROM EmpTree ORDER BY Level;
The anchor part runs once and grabs the top of the tree. The recursive part keeps running and adds the next level each time, until no more rows match. Very neat for any parent-child data.
CTE Can Also Power DELETE and UPDATE
A CTE is not just for reading data. You can also wrap a DELETE or UPDATE on top of it, which makes it the cleanest way to remove duplicates in SQL Server.
WITH Dupes AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS RowNum
FROM Customers
)
DELETE FROM Dupes WHERE RowNum > 1;
Common Mistakes
Mistake 1: Forgetting the semicolon before WITH. If there is a statement above your CTE, you must end it with a semicolon, or SQL Server throws a strange error.
Mistake 2: Trying to use a CTE outside its query. A CTE dies the moment its statement finishes. You cannot reference it in the next query.
Mistake 3: Infinite recursion. Forgetting the stopping condition in a recursive CTE hits the default 100-iteration limit. Always design a clear anchor.
The Interview Answer
"A CTE, or Common Table Expression, is a named temporary result set that exists only for the duration of a single query. It is defined using the
WITH keyword and makes complex queries more readable. Unlike views, it is not stored in the database, and unlike temp tables, it is not stored in tempdb. CTEs also support recursion, which makes them great for hierarchical data."
Summary
A CTE is a temporary, in-memory, named query result that only lives as long as the statement that uses it. Create it with WITH name AS (...), then query it just like a regular table right below. It does not appear in your schema, it is not stored on disk, and it vanishes as soon as the query ends.
Use CTEs to make complex queries readable, to reference the same sub-result multiple times, to write recursive queries for trees, and to power clean DELETE or UPDATE statements. Remember — CTEs are about clarity. For heavy reusable data, reach for a view or a temporary table instead.
| Concept | Key Takeaway |
|---|---|
| CTE | Named temporary result set for one query |
| Keyword | Starts with WITH |
| Stored? | No — in-memory only |
| Scope | Dies as soon as the statement ends |
| Main benefit | Makes complex queries readable |
| Recursion | CTEs can call themselves — great for trees |
| With DML | Can be used with SELECT, DELETE, UPDATE |
| vs View | View is stored, CTE is not |
| vs Temp Table | Temp table holds data; CTE is re-computed |
| Interview line | "Named temporary result set for one query" |