Common Table Expressions (CTEs)
7 questions found
What is a common table expression, and why would you use one instead of a subquery?
Beginner
A common table expression, defined using the WITH keyword, creates a temporary named result set that you can reference within a single query, making complex queries easier to read and organize compared to nesting several subqueries inside each other, especially when the same intermediate result needs to be referenced more than once.
WITH HighValueOrders AS (
SELECT CustomerId, Amount
FROM Orders
WHERE Amount > 500
)
SELECT CustomerId, COUNT(*) AS OrderCount
FROM HighValueOrders
GROUP BY CustomerId;
Real-world example
A sales report uses a CTE to first isolate high value orders, then groups and counts them by customer, making the overall query much easier to read than nesting the same filter logic inside a subquery.
Common follow-ups: Does a CTE actually improve query performance compared to a subquery?;Can a query have more than one CTE defined at the same time?
Subqueries;Query Optimization & Plans
How do you define multiple common table expressions within a single query?
Beginner
You separate multiple CTE definitions with a comma after the WITH keyword, and each subsequent CTE can even reference the ones defined before it, letting you build up a series of clearly named, logical steps that eventually feed into your final SELECT statement.
WITH RecentOrders AS (
SELECT * FROM Orders WHERE OrderDate >= '2026-01-01'
),
HighValue AS (
SELECT * FROM RecentOrders WHERE Amount > 500
)
SELECT * FROM HighValue;
Real-world example
A reporting query breaks a complex filtering process into two clearly named steps, first isolating recent orders and then further filtering for high value ones, making the overall logic much easier to follow.
Common follow-ups: Can later CTEs reference earlier ones in the same WITH clause?;Is there a limit to how many CTEs can be defined in a single query?
Subqueries;Joins
What is a recursive common table expression, and what kind of problems is it commonly used to solve?
Intermediate
A recursive CTE references itself within its own definition, repeatedly building on its previous results, which makes it especially useful for working with hierarchical data, such as an organizational chart showing employees and their managers, or generating a sequence of numbers or dates.
WITH EmployeeHierarchy AS (
SELECT EmployeeId, ManagerId, Name, 0 AS Level
FROM Employees WHERE ManagerId IS NULL
UNION ALL
SELECT e.EmployeeId, e.ManagerId, e.Name, eh.Level + 1
FROM Employees e
JOIN EmployeeHierarchy eh ON e.ManagerId = eh.EmployeeId
)
SELECT * FROM EmployeeHierarchy;
Real-world example
A human resources system uses a recursive CTE to display a complete organizational chart, showing every employee's reporting level from the top executive down to individual team members, all in a single query.
Common follow-ups: What happens if a recursive CTE accidentally creates an infinite loop?;How do you limit the maximum recursion depth in SQL Server?
Data Types & Schema Design;Subqueries
How do you prevent a recursive common table expression from running forever if the underlying data accidentally contains a circular reference?
Intermediate
SQL Server has a default maximum recursion limit of one hundred, which will stop the query and raise an error if that limit is exceeded, but you can also explicitly set a lower limit using the MAXRECURSION query hint, or set it to zero to allow unlimited recursion if you are confident the data does not contain any circular references.
WITH EmployeeHierarchy AS (
SELECT EmployeeId, ManagerId, 0 AS Level FROM Employees WHERE ManagerId IS NULL
UNION ALL
SELECT e.EmployeeId, e.ManagerId, eh.Level + 1
FROM Employees e JOIN EmployeeHierarchy eh ON e.ManagerId = eh.EmployeeId
)
SELECT * FROM EmployeeHierarchy
OPTION (MAXRECURSION 50);
Real-world example
A data quality team sets a lower MAXRECURSION limit while testing a new organizational chart query, quickly catching a data entry error that had accidentally created a circular manager relationship between two employees.
Common follow-ups: What error does SQL Server return when the recursion limit is exceeded?;Is it ever appropriate to disable the recursion limit entirely?
Data Types & Schema Design;Error Handling with TRY CATCH
How would you use a CTE combined with a window function to identify and remove duplicate rows from a table?
Advanced
You define a CTE that uses ROW_NUMBER combined with a PARTITION BY clause to assign a sequential number to each duplicate group based on the columns that define a duplicate, and then you can delete or select only the rows where that row number is greater than one, effectively removing every duplicate except the first occurrence.
WITH DuplicateRows AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerId) AS RowNum
FROM Customers
)
DELETE FROM DuplicateRows WHERE RowNum > 1;
Real-world example
A marketing database cleans up duplicate customer records that share the same email address, using a CTE with ROW_NUMBER to keep only the earliest record for each customer and remove the rest.
Common follow-ups: How do you choose which duplicate row to keep when there are several to choose from?;Can this same pattern be used to identify duplicates without actually deleting them?
Window Functions;Data Types & Schema Design
Can a common table expression be used inside an UPDATE, DELETE, or MERGE statement, and how does that work?
Advanced
Yes, you can define a CTE and then reference it directly within an UPDATE, DELETE, or MERGE statement, which is particularly useful when you need to first identify a specific, often complex, set of rows using logic like window functions or joins, and then perform a data modification against exactly those identified rows.
WITH RecentHighValue AS (
SELECT OrderId FROM Orders
WHERE Amount > 1000 AND OrderDate >= '2026-01-01'
)
UPDATE Orders
SET Status = 'Priority'
WHERE OrderId IN (SELECT OrderId FROM RecentHighValue);
Real-world example
An order management system marks all high value recent orders as priority using a CTE to first clearly identify exactly which orders qualify, then applies the update to just those specific rows.
Common follow-ups: Does using a CTE in an UPDATE statement affect performance compared to a plain subquery?;Can the same CTE be referenced by both a SELECT and an UPDATE in the same statement?
Merge Statement (Upsert);Subqueries
What are the main limitations of common table expressions that developers should be aware of?
Intermediate
A CTE only exists for the duration of the single statement that immediately follows its definition and cannot be reused across multiple separate statements, it cannot include an ORDER BY clause except when combined with TOP, and unlike a temporary table, it does not have its own indexes, meaning very complex or frequently reused CTEs might sometimes perform better as an actual temporary table instead.
-- A CTE cannot be reused in a second, separate statement
WITH RecentOrders AS (SELECT * FROM Orders WHERE OrderDate >= '2026-01-01')
SELECT * FROM RecentOrders; -- valid
-- SELECT * FROM RecentOrders; -- this second reference would fail
Real-world example
A developer initially tries referencing the same CTE across two separate SELECT statements, discovers this is not allowed, and instead stores the intermediate result in a temporary table when it needs to be reused multiple times.
Common follow-ups: When would a temporary table be a better choice than a CTE for performance reasons?;Why can't a CTE have its own indexes like a temporary table can?
Temporary Tables & Table Variables;Query Optimization & Plans