Subqueries & CTElessonadvanced
Common Table Expressions (CTEs)
WITH clause syntax, multiple CTEs, recursive CTEs, and CTE vs subquery trade-offs.
Overview
CTEs make complex queries readable by naming intermediate result sets. This lesson covers the WITH clause syntax, chaining multiple CTEs, using CTEs in INSERT/UPDATE/DELETE, and writing recursive CTEs for hierarchical data.
Lesson Plan
| Time | Activity |
|---|---|
| 0–10 min | Intro: readability problem CTEs solve |
| 10–25 min | Basic WITH syntax — single CTE, referencing in main query |
| 25–45 min | Multiple CTEs in one statement; chaining CTEs |
| 45–60 min | Recursive CTEs: syntax, base case, recursive step, UNION ALL |
| 60–75 min | Guided practice: rewrite a complex subquery as a CTE |
| 75–80 min | Wrap-up: CTE vs subquery vs view |
Key Concepts
WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name- Multiple CTEs: comma-separated, each can reference previous ones
- CTEs are not materialised by default in PostgreSQL — same as subquery
- Recursive CTE:
WITH RECURSIVE, base case + recursive case joined byUNION ALL - Termination condition prevents infinite recursion
- CTEs can be used before INSERT, UPDATE, DELETE for data manipulation
✏️Practice
Short in-class queries:
- Rewrite a nested subquery that calculates average payment per customer using a CTE.
- Use two CTEs to find films rented more than average and return their category.
- Write a recursive CTE to generate a number series from 1 to 10.