Common Table Expressions (CTE) in SQL
💡 Concept Name
CTE (Common Table Expression) is a temporary result set defined within a SQL statement using the WITH clause. It improves readability and supports recursion.
📘 Quick Intro
A CTE makes complex SQL queries more readable by isolating subqueries at the top of the query. CTEs can also be recursive, making them useful for hierarchical data queries like org charts or folder structures.
🧠 Analogy / Short Story
Think of a CTE like setting up a scratchpad for solving a math problem—you define part of the solution separately so the final equation is easier to write and understand. Instead of cramming everything into one step, you write reusable chunks for clarity and reuse.
🔧 Technical Explanation
- 🧾 Defined using
WITH CTE_Name AS (...)at the beginning of the query. - 🔁 Can be recursive—must include an anchor member and a recursive member.
- 🧹 Temporary scope—only valid for the duration of the query.
- 📊 Improves query readability compared to nested subqueries or derived tables.
- 🔒 Can be used with joins, aggregations, and ordering.
🎯 Purpose & Use Case
- ✅ Simplify and modularize complex queries.
- ✅ Perform hierarchical/recursive data operations.
- ✅ Eliminate repeated subqueries.
- ✅ Improve query maintainability and readability.
💻 Real Code Example
-- Example: Get total sales per region using a CTE
WITH RegionalSales AS (
SELECT Region, SUM(Amount) AS TotalSales
FROM Orders
GROUP BY Region
)
SELECT *
FROM RegionalSales
WHERE TotalSales > 10000;
-- Recursive CTE: Get all ancestors of a category
WITH CategoryHierarchy AS (
SELECT Id, ParentId, Name
FROM Categories
WHERE Id = 1 -- starting point
UNION ALL
SELECT c.Id, c.ParentId, c.Name
FROM Categories c
INNER JOIN CategoryHierarchy ch ON c.Id = ch.ParentId
)
SELECT * FROM CategoryHierarchy;
❓ Interview Q&A
Q1: What is a Common Table Expression (CTE)?
A: It's a temporary named result set defined with a WITH clause to simplify complex SQL queries.
Q2: Can CTEs be recursive?
A: Yes, they can refer to themselves to handle hierarchical or tree-structured data.
Q3: Do CTEs improve performance?
A: Not always; they improve readability but may not offer a performance boost over subqueries.
Q4: Are CTEs reusable within the same query?
A: Yes, you can reference a CTE multiple times within the query.
Q5: What’s required in a recursive CTE?
A: An anchor query, a recursive query, and a UNION ALL between them.
Q6: Can you use ORDER BY in a CTE?
A: Yes, but only in the final SELECT or with TOP/FETCH inside the CTE if required.
Q7: Can CTEs reference other CTEs?
A: Yes, multiple CTEs can be chained and referenced sequentially.
Q8: How long does a CTE exist?
A: Only for the duration of the SQL statement that uses it.
Q9: Can CTEs replace views?
A: For temporary, readable logic—yes; for reuse across queries—no, use views.
Q10: Is a CTE the same as a temp table?
A: No, temp tables are stored in tempdb and persist for the session; CTEs are query-local.
📝 MCQs
Q1. What SQL clause introduces a CTE?
- AS
- LET
- WITH
- DEFINE
Q2. What does a recursive CTE require?
- Trigger
- Loop
- Anchor and recursive member
- Temp table
Q3. How long does a CTE live?
- Until session ends
- Forever
- Duration of the query
- Until COMMIT
Q4. CTEs are mainly used for?
- Data backup
- Triggers
- Readability and modular queries
- Performance tuning
Q5. Can CTEs reference themselves?
- No
- Only in MySQL
- Yes, in recursive CTEs
- Only once
Q6. Can you have multiple CTEs?
- No
- Yes, one per file
- Yes, separated by commas
- Only in joins
Q7. Do CTEs store data physically?
- Yes
- No
- Sometimes
- Only in Oracle
Q8. Can you filter data inside a CTE?
- No
- Yes, with WHERE
- Only in joins
- Only with indexes
Q9. Is UNION ALL required in recursive CTE?
- No
- Yes
- Optional
- Only in Oracle
Q10. Can a CTE be joined with other tables?
- No
- Yes
- Only in MySQL
- Only in views
💡 Bonus Insight
Recursive CTEs can replace complex loops in procedural logic and are particularly useful in reporting hierarchical structures like employees, folders, or bills of materials. Use caution to avoid infinite recursion—SQL Server allows a default of 100 recursion levels.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!