Table of Contents
- Introduction
- What is a Common Table Expression (CTE)?
- Why Use CTEs?
- Syntax of a CTE
- CTE vs Subquery vs View
- Multiple CTEs in a Query
- Using CTEs with Aggregation
- Recursive CTEs – Introduction
- Syntax of Recursive CTEs
- Recursive CTE Real-World Example: Hierarchies
- CTEs with
JOINandGROUP BY - Filtering Data within CTEs
- Nested CTEs
- CTEs with INSERT, UPDATE, DELETE
- Performance Considerations
- Readability and Debugging with CTEs
- Common Errors and Troubleshooting
- Best Practices for Writing CTEs
- Real-World Use Cases for CTEs
- Summary and What’s Next
1. Introduction
Common Table Expressions (CTEs) offer a way to define temporary result sets that exist only during the execution of a query. They allow for cleaner, more readable, and modular SQL code — especially in complex logic involving recursion, filtering, and aggregation.
2. What is a Common Table Expression (CTE)?
A CTE is a temporary named result set that you define using the WITH clause. It can be referred to just like a table or subquery within the main query.
3. Why Use CTEs?
- Improves readability of complex queries
- Encourages query modularity and reuse
- Enables recursive queries (e.g., hierarchies, graph traversal)
- Simplifies debugging and testing
- Supports multi-step transformations
4. Syntax of a CTE
sqlCopyEditWITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
CTEs are defined first and then used like a virtual table.
5. CTE vs Subquery vs View
| Feature | CTE | Subquery | View |
|---|---|---|---|
| Temporary | Yes (per query) | Yes | No (persistent) |
| Reusable | Yes (in same query) | No | Yes |
| Syntax | WITH clause | Inside FROM, WHERE, etc. | CREATE VIEW |
| Use case | Modular, recursive, readable | Simple conditions or filters | Reuse logic, access control |
6. Multiple CTEs in a Query
You can define multiple CTEs in a single query:
sqlCopyEditWITH sales AS (
SELECT * FROM orders WHERE status = 'completed'
),
top_customers AS (
SELECT customer_id, SUM(total) AS total_spent
FROM sales
GROUP BY customer_id
)
SELECT * FROM top_customers WHERE total_spent > 5000;
7. Using CTEs with Aggregation
sqlCopyEditWITH department_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM department_salary WHERE avg_salary > 70000;
This breaks down logic and makes complex queries easier to manage.
8. Recursive CTEs – Introduction
Recursive CTEs allow you to reference the CTE inside itself. They are used for:
- Traversing hierarchies (e.g., org charts)
- Generating sequences
- Working with tree-structured data
9. Syntax of Recursive CTEs
sqlCopyEditWITH RECURSIVE cte_name (columns) AS (
-- Anchor member
SELECT ...
FROM ...
WHERE ...
UNION ALL
-- Recursive member
SELECT ...
FROM table
JOIN cte_name ON ...
)
SELECT * FROM cte_name;
10. Recursive CTE Real-World Example: Hierarchies
employees table:
| id | name | manager_id |
|---|---|---|
| 1 | Ravi | NULL |
| 2 | Sneha | 1 |
| 3 | Ankit | 2 |
sqlCopyEditWITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, h.level + 1
FROM employees e
JOIN employee_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM employee_hierarchy;
11. CTEs with JOIN and GROUP BY
sqlCopyEditWITH active_orders AS (
SELECT * FROM orders WHERE status = 'active'
)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN active_orders o ON c.id = o.customer_id
GROUP BY c.name;
12. Filtering Data within CTEs
Apply WHERE clauses inside or outside the CTE, depending on logic needs.
sqlCopyEditWITH sales_2024 AS (
SELECT * FROM orders WHERE order_date >= '2024-01-01'
)
SELECT * FROM sales_2024 WHERE total > 500;
13. Nested CTEs
CTEs can refer to other CTEs defined earlier:
sqlCopyEditWITH first_cte AS (
SELECT id, salary FROM employees
),
second_cte AS (
SELECT id FROM first_cte WHERE salary > 80000
)
SELECT * FROM second_cte;
14. CTEs with INSERT, UPDATE, DELETE
PostgreSQL and SQL Server allow CTEs in DML:
sqlCopyEditWITH recent_orders AS (
SELECT id FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
DELETE FROM orders WHERE id IN (SELECT id FROM recent_orders);
15. Performance Considerations
- Not all CTEs are materialized; depends on the DBMS
- Some engines inline CTEs (like subqueries)
- For large queries, test CTE vs subquery performance
- Recursive CTEs can be expensive — consider setting depth limits
16. Readability and Debugging with CTEs
CTEs help:
- Break logic into logical blocks
- Test individual pieces
- Reuse temporary results in joins and filters
- Keep the final query focused and readable
17. Common Errors and Troubleshooting
| Issue | Reason |
|---|---|
| Invalid reference to CTE | CTE must be declared before it’s used |
| Infinite recursion in recursive CTE | Missing anchor or stopping condition |
| Ambiguous columns | Always alias CTE columns explicitly |
18. Best Practices for Writing CTEs
- Use meaningful CTE names (
monthly_sales,latest_orders, etc.) - Don’t over-nest — two to three CTEs are ideal for clarity
- Separate business logic into layers
- Consider performance tradeoffs on large datasets
- Use recursive CTEs with depth limits or base case checks
19. Real-World Use Cases for CTEs
- Organization charts
- Breadcrumb navigation (categories, menus)
- Paginated result construction
- Building step-by-step reporting metrics
- Reusing filtered datasets across complex joins
20. Summary and What’s Next
Common Table Expressions (CTEs) make your SQL more modular, readable, and maintainable. They’re perfect for breaking down logic and handling hierarchical or recursive data structures — a must-have tool for any serious SQL developer.