Subqueries and CTEs in SQL
Source: Dev.to
When working with SQL, you eventually run into situations where a single query just isn’t enough. You need to break a problem into parts, compute an intermediate result, and then use that result elsewhere. That’s where subqueries and Common Table Expressions (CTEs) come in.
They solve similar problems, but they do it in slightly different ways, and choosing between them can affect performance, readability, and maintainability.
Subqueries
A subquery is a query written inside another query. It produces a result that the outer query depends on—essentially a question within a question.
How Subqueries Work
- Placement: Typically embedded in
WHERE,SELECT, orFROMclauses, depending on their role. - Use Cases: Quick calculations or filters where the result is needed only once.
Types of Subqueries
| Type | Description | Typical Use |
|---|---|---|
| Scalar subquery | Returns a single value | Comparisons (e.g., WHERE salary > (SELECT AVG(salary) FROM employees)) |
| Multi‑row subquery | Returns multiple values | Operators like IN (e.g., WHERE department_id IN (SELECT id FROM departments)) |
| Correlated subquery | References columns from the outer query; executed once per row | Complex filters; can be slower if not indexed properly |
| Nested subquery | Subquery inside another subquery | Valid but can become hard to read; often a sign to refactor |
When to Use Subqueries
- The logic is relatively straightforward.
- The intermediate result is needed only once.
- You want a compact query without extra naming.
Common Table Expressions (CTEs)
A CTE is a named temporary result set defined at the beginning of a query. You define it once and reference it by name throughout the query.
Benefits of CTEs
- Improves readability by separating logic into named sections.
- Allows building queries step‑by‑step; one CTE can depend on another.
- Enables reuse of the same intermediate result multiple times.
- Supports recursion for hierarchical data (e.g., organizational charts, category trees).
Types of CTEs
- Simple CTE – Improves readability for a single logical block.
- Multiple CTEs – Layered approach for complex transformations; each CTE can build on the previous one.
- Recursive CTE – Handles hierarchical data by repeatedly referencing itself.
Comparison: Subqueries vs. CTEs
| Aspect | Subqueries | CTEs |
|---|---|---|
| Readability | Can become hard to follow as nesting grows | Structured top‑down flow; easier to debug |
| Performance | Simple subqueries are often well‑optimized; correlated subqueries may be expensive | May be materialized depending on the DBMS; performance varies |
| Reusability | Typically written inline; duplication if needed in multiple places | Defined once, referenced multiple times |
| Complexity | Suitable for simple, one‑off calculations | Better for complex queries with multiple steps or recursion |
| Hierarchy | Not ideal for hierarchical data | Recursive CTEs excel at hierarchical queries |
In practice, performance depends more on query design and indexing than on the choice between subqueries and CTEs.
When Should You Use Each?
- Subqueries: Choose when the task is simple, the result is needed only once, and you prefer a compact query.
- CTEs: Prefer when the logic is complex, readability matters, you need to reuse intermediate results, or you’re working with hierarchical data that requires recursion.
Subqueries and CTEs are complementary tools rather than direct competitors. It’s common to start with a subquery and switch to a CTE as the query grows in complexity, leading to clearer, more maintainable SQL.