SQL Basics Part 6: Subqueries and CTEs (Common Table Expressions)

Up to now in my SQL series, almost every query I’ve written has stood on its own as a single step: creating tables in Part 1, filtering and grouping in Part 2, joining tables in Part 3, transforming columns in Part 4, and window functions in Part 5.

Part 6 is about queries inside queries: Subqueries and their much cleaner, more readable cousin, CTEs (Common Table Expressions). This post wraps up my core SQL foundation notes before I move on to tackling real-world, multi-step SQL scenarios.

What Is a Subquery?

A subquery is simply a SELECT query nested inside parentheses within a larger query. The inner query runs first, and its output is handed over to the outer query, just like answering a smaller helper question before answering the main question.

1. A Scalar Subquery Inside WHERE

Suppose you want to find every product in dim_product whose unit_price is strictly above the average price across the whole table. Remember from Part 2 that SQL does not allow aggregate functions directly inside a WHERE clause (WHERE unit_price > AVG(unit_price) throws an error).

A subquery inside WHERE solves this cleanly:

SELECT * FROM dim_product 
WHERE unit_price > (SELECT AVG(unit_price) FROM dim_product);

Here is how the database executes this:

1. The inner query (SELECT AVG(unit_price) FROM dim_product) runs first and returns a single number (the table-wide average price).
2. The outer query substitutes that number into WHERE unit_price > <avg_price> and filters every row against it.

The best part is that you never have to run a separate query, copy the average by hand, and hardcode a number into your WHERE clause. Whenever new products are added to dim_product, the subquery recalculates the average automatically.

2. A Subquery Inside FROM (Derived Table)

Subqueries aren’t limited to WHERE. You can also place a subquery directly inside the FROM clause so the outer query treats its result set like a temporary table:

SELECT * FROM (
    SELECT * FROM dim_product 
    WHERE unit_price > (SELECT AVG(unit_price) FROM dim_product)
) AS subquery_table
WHERE product_name = 'Also Audience';

Because nested subqueries execute from the inside out, you read them starting from the deepest parentheses:

1. Innermost query: Calculates AVG(unit_price) across dim_product.
2. Middle query: Finds every product priced above that average.
3. Derived table (AS subquery_table): Wraps that intermediate result set as a temporary table in FROM.
4. Outer query: Filters subquery_table down to the row where product_name = 'Also Audience'.

One rule in MySQL that tripped me up the first time I tried this: every subquery inside a FROM clause must have an alias (like AS subquery_table). If you leave off AS subquery_table, MySQL immediately throws Error Code: 1248. Every derived table must have its own alias.

CTEs (Common Table Expressions): A Cleaner Way to Write Multi-Step Queries

Nested subqueries work fine when you only have one inner query, but as soon as you nest two or three levels deep, reading from the inside out through layers of parentheses gets messy fast.

A CTE (Common Table Expression) solves that readability problem. You define a named temporary result set at the very top of your query using the WITH keyword, and then query it like a regular table:

WITH cte_table AS (
    SELECT * 
    FROM dim_product 
    WHERE unit_price > (SELECT AVG(unit_price) FROM dim_product) 
    ORDER BY category
)

SELECT * FROM cte_table;

This produces the exact same result as the derived-table subquery, except now you can read the logic from top to bottom in the order it actually happens: first define cte_table, then SELECT * FROM cte_table.

One important rule to keep in mind: a CTE only exists for the single SQL statement immediately following WITH. Unlike a physical table or a saved VIEW, a CTE is not stored in the database schema. As soon as that one SELECT statement finishes executing, cte_table disappears completely.

Chaining Multiple CTEs Together

Where CTEs really shine is when you need to chain multiple transformation or filtering steps together in a pipeline:

WITH cte_table AS (
    SELECT * 
    FROM dim_product 
    WHERE unit_price > (SELECT AVG(unit_price) FROM dim_product) 
    ORDER BY category
),

cte_table_2 AS (
    SELECT * 
    FROM cte_table 
    WHERE product_name IN ('Less Other', 'Property Above', 'Only Sense')
)

SELECT * FROM cte_table_2 WHERE product_name = 'Only Sense';

Look at how clean that step-by-step progression is:

1. cte_table pulls all products from dim_product that cost more than the average product price.
2. cte_table_2 queries cte_table (not dim_product!) and narrows those above-average products down to three specific product names.
3. The final SELECT queries cte_table_2 and picks out 'Only Sense'.

Notice syntax-wise that you only write the WITH keyword once at the very top, and separate each CTE definition with a comma (,). Any CTE in the chain can reference any CTE defined above it.

Using a CTE to Filter Window Functions (From Part 5)

CTEs also complete the puzzle from Part 5. Because window functions like DENSE_RANK() are evaluated during the SELECT step, you cannot write WHERE dense_rank <= 3 in the same query. Wrapping the window function inside a CTE makes getting the top 3 products per category super clean:

WITH ranked_products AS (
    SELECT 
        product_name,
        category,
        unit_price,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY unit_price DESC) AS price_rank
    FROM dim_product
)

SELECT * 
FROM ranked_products
WHERE price_rank <= 3;

Subquery vs. CTE: When to Use Which

Functionally, a subquery in FROM and a CTE often compile down to the exact same execution plan in modern databases. The real difference is how easy the query is for a human to read and debug:

Feature	Subquery	CTE (WITH Clause)
Reading order	Inside-out (start at deepest parentheses)	Top-to-bottom (step 1, step 2, final query)
Best use case	Quick one-liner inside WHERE or IN (...)	Multi-step queries, chaining logic, or filtering window functions
Reusability within the query	Must copy-paste the subquery if needed twice	Can be referenced multiple times in FROM or JOIN within the same query

Whenever I just need a quick scalar value inside a WHERE filter (like WHERE unit_price > (SELECT AVG(unit_price) ...)), an inline subquery is short and sweet. Once a query needs two or three steps stacked on top of each other, a CTE is much easier to read back a week later.

What’s Next After the SQL Basics Series

With tables and keys (Part 1), filtering and aggregation (Part 2), joins and data modification (Part 3), data transformations and CASE (Part 4), window functions (Part 5), and now subqueries and CTEs under my belt, that wraps up my 6-part SQL Basics series.

Next up in SQL Real-World Scenarios: Nth Highest Value, Removing Duplicates, and LAG/LEAD, I move from learning individual SQL building blocks to combining window functions, PARTITION BY, and CTEs on practical dataset problems.