As your data requirements grow more complex, you’ll find that a single SELECT statement is not enough. Subqueries and Common Table Expressions (CTEs) allow you to break down logic into manageable parts.
What We Will Cover
- Subqueries: Queries within queries. Learn the difference between scalar, row, and table subqueries.
- Correlated Subqueries: Queries that depend on the outer query for their values.
- CTEs (WITH clause): Making queries more readable and maintainable by naming temporary result sets.
- Window Functions: Performing calculations across a set of table rows that are related to the current row.
Why This Matters
While Joins are about combining data, Subqueries and CTEs are about structuring logic. They are often used for:
- Finding outliers (e.g., employees earning more than the average).
- Deduplicating data while keeping the latest record.
- Recursive tasks (e.g., organisational hierarchies).
- Data cleaning (e.g., identifying orphan records).
Subquery vs CTE vs Join Decision Matrix
| Scenario | Best Tool |
|---|---|
| Single aggregated value (e.g., max salary) | Subquery |
| Same temp result referenced multiple times | CTE |
| Hierarchical/recursive data | Recursive CTE |
| Combining related columns from different tables | JOIN |
| Row-by-row ranking or running totals | Window Function |
Performance Note
Modern database optimisers are smart. A subquery, CTE, and JOIN can sometimes produce the same execution plan. Write for readability first, then optimise if needed.
Q: When should you use a CTE instead of a subquery?
A: Use a CTE when:
- You need to reference the same result set multiple times in a single query.
- You want to improve query readability (naming logic).
- You need to perform recursion.
Q: Are subqueries slower than Joins?
A: Not necessarily. Modern database optimizers often rewrite subqueries into joins. However, for readability and maintainability, joins or CTEs are usually preferred for complex logic.
Q: Do CTEs always materialise?
A: In older databases (PostgreSQL < 12), CTEs were optimisation fences that always materialised. Modern versions inline CTEs like subqueries when possible. Use WITH with MATERIALIZED or NOT MATERIALIZED hints in PostgreSQL for control.
Premium Content
Unlock Subqueries & CTEs Overview and all premium lessons with a subscription.
From ₹199.99/year — See plans