Common Table Expressions (CTEs) in SQL: Modular and Reusable Query Blocks

Table of Contents

1. Introduction
2. What is a Common Table Expression (CTE)?
3. Why Use CTEs?
4. Syntax of a CTE
5. CTE vs Subquery vs View
6. Multiple CTEs in a Query
7. Using CTEs with Aggregation
8. Recursive CTEs – Introduction
9. Syntax of Recursive CTEs
10. Recursive CTE Real-World Example: Hierarchies
11. CTEs with JOIN and GROUP BY
12. Filtering Data within CTEs
13. Nested CTEs
14. CTEs with INSERT, UPDATE, DELETE
15. Performance Considerations
16. Readability and Debugging with CTEs
17. Common Errors and Troubleshooting
18. Best Practices for Writing CTEs
19. Real-World Use Cases for CTEs
20. 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

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:

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

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.