# Subqueries & CTEs: Two Ways to Query Inside a Query

Sometimes one query needs the result of another query to work. SQL gives you two main tools for that: subqueries and CTEs (Common Table Expressions). They can often solve the same problem, but they read differently, and knowing when to reach for each makes your SQL a lot easier to write and debug.

What Are Subqueries?

A subquery is a query nested inside another query, wrapped in parentheses. It runs first, and its result is used by the outer query, whether that's a single value, a list of values, or an entire table-like result. Subqueries can live almost anywhere: in the SELECT list, the FROM clause, or the WHERE clause.

Using the same customer and orders tables from earlier, a subquery could find every customer whose order total is above the overall average, without you having to calculate that average separately first.

What Are CTEs?

A CTE, or Common Table Expression, is a named temporary result set defined with a WITH clause, that you can reference later in the query just like a table. It exists only for the duration of that single query, and disappears once the query finishes.

Where a subquery is nested inside another statement, a CTE is defined up front, given a name, and then used below. That structure is what makes CTEs easier to read, especially once a query needs more than one step.

Differences Between Them

Subquery	CTE
Nested inside the query that uses it	Defined separately, above the query, with a name
Can get hard to read when nested deeply	Stays readable even when chaining several steps
Cannot reference itself	Can be recursive, referencing itself for hierarchical data
Typically used once, inline	Can be referenced multiple times in the same query
No separate name required	Requires a name via WITH

Performance between the two is usually similar in modern databases, since most query planners optimize CTEs and subqueries the same way. The real difference is readability and reusability, not speed.

Practical Examples & Use Cases

A subquery in the WHERE clause filters customers whose order total beats the overall average, calculated on the fly.

SELECT customer_id, order_total
FROM orders
WHERE order_total > (
    SELECT AVG(order_total) FROM orders
);

A subquery in the FROM clause treats a query's result as if it were its own table.

SELECT category, avg_total
FROM (
    SELECT category, AVG(order_total) AS avg_total
    FROM customer c JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY category
) AS category_avgs;

The same logic as a CTE reads top to bottom instead of inside out.

WITH category_avgs AS (
    SELECT category, AVG(order_total) AS avg_total
    FROM customer c JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY category
)
SELECT * FROM category_avgs WHERE avg_total > 50;

Chaining multiple CTEs keeps a multi-step query easy to follow, each step building on the last.

WITH customer_totals AS (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders GROUP BY customer_id
),
top_spenders AS (
    SELECT * FROM customer_totals WHERE total_spent > 100
)
SELECT c.item_purchased, t.total_spent
FROM top_spenders t JOIN customer c ON c.customer_id = t.customer_id;

A recursive CTE, something a subquery can't do, walks a hierarchy such as a category tree.

WITH RECURSIVE category_tree AS (
    SELECT category_id, parent_id, name FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Reach for a subquery when the logic is small and used once. Reach for a CTE when you need to name a step, reuse it, chain several steps together, or walk a hierarchy recursively.

The Simple Way to Remember It

A subquery is a query hidden inside another query. A CTE is a query given a name up front, then used like a table below it. They can solve the same problems, but CTEs scale better as logic gets more complex, and only CTEs can be recursive.

If a query is starting to nest three levels deep, that's usually the sign to pull it out into a CTE.