Historical Context & Motivation
Relational databases revolutionized how businesses store and retrieve data, but early SQL implementations exposed a significant limitation: analysts often needed to perform multi-step calculations that could not be expressed in a single flat query. Imagine a marketing manager who wants to identify customers whose total spending exceeds the company-wide average — this seemingly simple question requires the database to first compute the average, then compare each customer against it. Before subqueries and common table expressions (CTEs), analysts had to resort to temporary tables, application-side processing, or multiple round-trips to the database — all of which were error-prone and inefficient.
The evolution of SQL from a simple retrieval language to a powerful analytical tool mirrors the growing complexity of business questions themselves. As organizations accumulated more data across sales, operations, and finance, the demand for composable, layered queries grew dramatically. The introduction of subqueries and, later, CTEs represented two of the most important leaps in making SQL expressive enough for real-world business analytics.
The central question these features address is deceptively simple: how can we break a complex, multi-step business question into composable pieces within a single SQL statement? Subqueries and CTEs each answer this question in distinct but complementary ways, and understanding both is essential for any business analyst working with data.
Core Principles & Definitions
At their core, both subqueries and CTEs allow you to embed one query's result inside another, creating layered logic that mirrors how business questions are actually structured. A business question like "which stores exceeded the regional average in Q4" implicitly requires two computations — finding the regional average, then filtering stores against it. SQL subqueries and CTEs formalize this kind of layered reasoning directly in the query language.
Subquery (Nested Query)
Common Table Expression (CTE)
WITH keyword. CTEs exist only for the duration of the query and can be referenced multiple times, functioning like a temporary view.Correlated vs. Non-Correlated
Derived Table (Inline View)
FROM clause, it produces a temporary result set called a derived table. The outer query treats it exactly like a regular table and must assign it an alias.Query Composability
Visual Explanation — Query Nesting & CTE Flow
The diagram below illustrates the fundamental structural difference between a subquery and a CTE. On the left, a subquery is literally nested inside the outer query — the database engine encounters the inner query during execution and resolves it in place. On the right, a CTE defines the intermediate result set first (using the WITH clause) and then the main query references it by name. Both approaches achieve the same logical outcome, but the CTE reads top-to-bottom, which aligns more naturally with how analysts think about multi-step problems.
WITH, then the main query references it by the alias avg_spend. Both yield the same result set — customers whose spending exceeds the average.Notice that the CTE version reads from top to bottom: first define the building block, then use it. This mirrors the way you would explain the analysis to a colleague — "first compute the average, then find all customers above it." In contrast, the subquery version requires the reader to parse inside-out, understanding the inner query before the outer query makes sense. For simple one-off nesting, both approaches work equally well, but as the number of intermediate steps grows, CTEs offer a significant readability advantage that pays dividends when queries are shared across a team.
How Subqueries & CTEs Work Under the Hood
Understanding the execution mechanics helps you write more efficient queries. When the database engine encounters a non-correlated subquery, it evaluates the inner query once, caches the result, and substitutes it into the outer query. This is efficient because the inner query is independent of the outer query's rows. A correlated subquery, however, must re-execute for every row the outer query processes, which can lead to O(n × m) performance — where n is the number of outer rows and m is the cost of the inner query. For large datasets typical in business analytics, this distinction can mean the difference between a query that runs in seconds and one that takes minutes.
Subquery Placement & Return Types
| Placement | Return Type | Example Use Case |
|---|---|---|
WHERE clause | Scalar (single value) or list (single column) | Filter orders where amount exceeds the average order value |
FROM clause (derived table) | Full result set (rows × columns) | Pre-aggregate sales by region, then join to the main table |
SELECT clause (scalar) | Scalar (exactly one value) | Add a column showing each employee's salary as a percent of the department total |
HAVING clause | Scalar or list | Filter grouped results where group total exceeds a benchmark |
CTE Execution Model
A CTE defined with WITH cte_name AS ( ... ) behaves, in most modern databases, as an inline expansion — the optimizer replaces each reference to the CTE with the CTE's definition and optimizes the whole query holistically. This means a CTE generally performs identically to the equivalent subquery. Some databases (notably PostgreSQL prior to version 12) treated CTEs as optimization fences, materializing the CTE result before the outer query could push predicates into it. Modern versions of all major platforms — including BigQuery, Snowflake, SQL Server, and PostgreSQL 12+ — inline CTEs by default, so performance parity with subqueries is the norm.
Types of Subqueries & CTE Syntax Patterns
Subqueries come in several flavors, each suited to different analytical patterns. Recognizing which type fits a given business question is a key skill. The diagram below classifies the most common patterns you will encounter when wrangling business data, from simple scalar subqueries through multi-column derived tables and chained CTEs.
CTE Syntax Template
The general CTE syntax pattern is worth memorizing because it is remarkably uniform across database platforms. You can chain multiple CTEs by separating them with commas, and each subsequent CTE can reference any CTE defined before it — enabling a clean, step-by-step analytical pipeline within a single query.
name AS (SELECT …) pair. CTEs are comma-separated — no comma after the last one. The final SELECT is the main query that consumes the CTEs.Worked Example — Regional Sales Analysis
Consider a scenario common in retail analytics: you have a sales table with columns order_id, region, product_category, and revenue. Your VP of Sales asks: "Show me each region's total revenue, but only for regions whose total exceeds the company-wide average regional revenue." We will solve this using both a subquery approach and a CTE approach.
SELECT AVG(region_total)
FROM (
SELECT region, SUM(revenue) AS region_total
FROM sales
GROUP BY region
) sub
SELECT region, SUM(revenue) AS region_total
FROM sales
GROUP BY region
HAVING SUM(revenue) > (
SELECT AVG(region_total)
FROM (
SELECT region, SUM(revenue) AS region_total
FROM sales
GROUP BY region
) sub
);
WITH regional_totals AS (
SELECT region, SUM(revenue) AS region_total
FROM sales
GROUP BY region
)
, avg_regional AS (
SELECT AVG(region_total) AS avg_total
FROM regional_totals
)
SELECT rt.region, rt.region_total
FROM regional_totals rt
CROSS JOIN avg_regional ar
WHERE rt.region_total > ar.avg_total;Subqueries vs. CTEs — Strengths & Trade-offs
Choosing between a subquery and a CTE is not purely a matter of style — each has structural strengths and limitations that matter in production analytics environments. The table below summarizes the key dimensions a business analyst should consider when deciding which approach to use.
| Dimension | Subquery | CTE |
|---|---|---|
| Readability | Harder to read when deeply nested (inside-out parsing required) | Reads top-to-bottom; each step is named and self-documenting |
| Reusability within a query | Must be duplicated if needed in multiple places, increasing error risk | Defined once, referenced multiple times by name |
| Performance | Generally identical; optimizer inlines both. Correlated subqueries can be slow. | Generally identical. Some engines auto-materialize CTEs referenced multiple times. |
| Recursion support | Not supported — cannot write recursive subqueries | Supported via RECURSIVE keyword (e.g., org chart traversal) |
| Scope | Scoped to the exact clause where it appears | Available to the entire main query and subsequent CTEs |
| Best for | Quick, one-off filters; simple scalar comparisons; EXISTS checks | Multi-step pipelines; queries shared across teams; complex joins |
Connection to Advanced Techniques
Subqueries and CTEs form the foundation for several advanced SQL techniques that you will encounter as your analytics skills deepen. Understanding how today's concepts connect to these more powerful tools will help you anticipate when to reach beyond introductory patterns. The table below maps each introductory concept to its advanced counterpart, giving you a roadmap for future learning.
| Introductory Concept | Advanced Extension | Business Use Case |
|---|---|---|
| Non-correlated subquery in WHERE | Window functions (OVER, PARTITION BY) eliminate the need for many WHERE subqueries | Rank each salesperson within their region without a self-join |
| Derived table in FROM | Materialized views — pre-computed derived tables stored on disk for repeated use | Daily dashboard aggregations that run too slowly as ad-hoc subqueries |
| Chained CTEs | Recursive CTEs — CTEs that reference themselves to traverse hierarchical data | Org chart reporting: find all employees who report (directly or indirectly) to a given VP |
| Correlated subquery | LATERAL joins (or CROSS APPLY in SQL Server) — a more explicit and often faster correlated pattern | For each customer, retrieve their three most recent orders |
One particularly powerful extension is the recursive CTE, which adds a RECURSIVE keyword and allows a CTE to reference itself iteratively. This enables traversal of tree-structured data — such as corporate hierarchies, bill-of-materials explosions, or category taxonomies — entirely within SQL. While recursive CTEs are beyond the scope of this introductory lesson, recognizing that CTEs can self-reference gives you a glimpse of their full power and explains why many analytics teams prefer CTEs as the default pattern for all nested logic.
Practice Problems
Work through these five problems to solidify your understanding of subqueries and CTEs. Each problem increases in complexity, moving from conceptual recall through applied business scenarios.
orders(order_id, customer_id, order_total), write a SQL query using a subquery to find all orders where the order_total is greater than the overall average order total.SELECT product_name, total_units
FROM (
SELECT product_name, SUM(units) AS total_units
FROM sales
GROUP BY product_name
) p
WHERE total_units > (
SELECT AVG(total_units)
FROM (
SELECT SUM(units) AS total_units
FROM sales
GROUP BY product_name
) a
);stores(store_id, region) and transactions(txn_id, store_id, amount, txn_date). Management wants a report showing each store's total 2024 revenue alongside its region's average store revenue, but only for stores that exceed their region's average. Write the query using chained CTEs.Lesson Summary
This lesson introduced two essential SQL techniques for multi-step business analysis. Subqueries are complete SELECT statements nested inside another query — they can appear in the WHERE, FROM, SELECT, or HAVING clauses and return scalar values, lists, or full result sets. They may be non-correlated (independent, run once) or correlated (reference the outer query, re-run per row). Common table expressions (CTEs) use the WITH keyword to define named, temporary result sets at the top of a query, enabling top-to-bottom readability and reuse within the same statement.
In terms of performance, subqueries and CTEs are generally equivalent because modern optimizers inline both. The key differentiator is readability and maintainability — CTEs excel when queries involve multiple intermediate steps, team collaboration, or future extension. Subqueries remain the right choice for quick, self-contained filters and EXISTS checks. Looking ahead, these foundational patterns connect directly to window functions, recursive CTEs, materialized views, and LATERAL joins — the advanced tools that power enterprise-grade analytics pipelines.