SUBQUERIES AND CTEs

When I first started learning SQL, writing a basic SELECT query wasn't too bad.
Then came questions like:

"What if I need to use the result of one query inside another query?"

That's where subqueries come in.
And when those queries start getting longer and harder to read, you might come across something called a CTE.
At first, both can seem confusing. But once you understand what they're doing, they actually make a lot of sense.

In this article, I'll break down subqueries and CTEs using simple examples and explain when you might want to use each one.

What Is a Subquery?

A subquery is simply a query inside another query. Instead of running two completely separate queries, we can put one query inside another and use its result.

Here's where they tend to show up in real code.

1. Inside a WHERE clause — "find rows above or below some number"

Say you run a small online store. You want to find your best customers: people who spent more than the average.

SELECT customer_id, name
FROM customers
WHERE lifetime_spend > (
  SELECT AVG(lifetime_spend) FROM customers
);

The inner query gives back one number: the average. The outer query uses that number as a cutoff. You'll see this pattern everywhere — flagging a payment that's way above someone's usual spend, or a product selling faster than others in its category.

2. Inside a FROM clause — using a query result like a table

Say your marketing team wants the average order value per city, but only for cities where that average is above $50.

SELECT city, avg_order_value
FROM (
  SELECT city, AVG(order_total) AS avg_order_value
  FROM orders
  GROUP BY city
) AS city_averages
WHERE avg_order_value > 50;

This inner query builds a small, temporary table, and the outer query filters it. You can't filter on avg_order_value in the same step where you calculate it, so this two-step approach is what lets you do it.

3. Inside a SELECT clause — pulling in one extra fact per row

Say someone asks: "next to each customer's name, show how many orders they've placed."

SELECT
  name,
  (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
FROM customers;

This only works if the subquery returns exactly one number per row. It's fine for small cases like this, but if you're doing it across a lot of rows, a JOIN is usually the faster choice.

4. Correlated subqueries — when the inner query needs the outer row

A correlated subquery looks at the current row from the outer query while it runs. It can't run on its own — it runs once for every row.

Say you want to find employees who earn more than the average salary in their own department (not the whole company).

SELECT e1.name, e1.salary, e1.department
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

See that line, e2.department = e1.department? That's the connection back to the outer row. For each employee, the subquery works out the average pay for just their department. This kind of check is hard to write any other way, which is exactly when a subquery is the right call.

What Is a CTE?

A CTE (short for Common Table Expression) is a named subquery placed at the top of your query, using WITH. Same result, easier to read.

Here's the "cities with above-average order value" example again, written as a CTE:

WITH city_averages AS (
  SELECT city, AVG(order_total) AS avg_order_value
  FROM orders
  GROUP BY city
)
SELECT city, avg_order_value
FROM city_averages
WHERE avg_order_value > 50;

It does the exact same thing as the earlier version. But read it out loud: "with city averages as this, select from city averages where..." It sounds close to plain English. That's the main reason people like CTEs.

Chaining CTEs together

This is where CTEs really help. Say you're building a dashboard and want to know which cities have the most big-spending customers.

WITH big_spenders AS (
  SELECT * FROM customers WHERE lifetime_spend > 1000
),
big_spenders_by_city AS (
  SELECT city, COUNT(*) AS spender_count
  FROM big_spenders
  GROUP BY city
)
SELECT * FROM big_spenders_by_city ORDER BY spender_count DESC;

Each step has a name, and each step builds on the one before it. Try writing this with nested subqueries instead, and you'll end up with closing brackets stacked on top of each other. Nobody enjoys reading that.

Recursive CTEs — the one thing subqueries can't do

This is the real strength of CTEs. Recursive CTEs handle anything shaped like a tree: org charts, product categories (like "Electronics > Laptops > Gaming Laptops"), comment threads with replies to replies.

Here's an org chart example — showing who reports to who, all the way down.

WITH RECURSIVE org_chart AS (
  -- Start at the top: anyone with no manager
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Then find everyone who reports to someone we already found
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

It starts at the top of the company, then keeps adding the next level down, one step at a time, until there's nobody left to add. Without a recursive CTE, you'd need to write a loop in your app code to do this.

A product category tree works almost the same way. Just swap manager_id for parent_category_id.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_category_id, 1 AS depth
  FROM categories
  WHERE parent_category_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_category_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_category_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

Same pattern, different data. Once you see it once, you'll notice it everywhere.

Subquery vs CTE: Quick Comparison

	Subquery	CTE
Can you reuse it in the same query?	No, you'd have to write it again	Yes, just reference its name
Easy to read when there are many steps?	Gets messy fast	Stays readable, step by step
Can it repeat itself (recursion)?	No	Yes, with WITH RECURSIVE
Can it use the outer row for context?	Yes	Not directly

"But Which One Is Faster?"

People ask this a lot, so let's settle it: there's no single right answer. Be careful trusting anyone who claims one is always faster.

• Older versions of Postgres (before version 12) always computed a CTE fully before using it, even if you only needed a few rows out of it. That gave CTEs a bad reputation. Postgres 12 and later fixed this.
• In SQL Server, MySQL 8+, and modern Postgres, a CTE and an equivalent subquery are usually treated the same way by the database, with the same speed.
• Recursive CTEs aren't really part of this comparison, since there's no subquery way to repeat a query like that.

Don't choose based on an old rumor about speed. Choose based on what's easier to read. If speed really matters for a specific query, check the real execution plan instead of guessing.

So, Which One Should You Use?

Most of the time it comes down to how many steps your query needs. Here's a simple way to decide:

Use a subquery when:

• It's a small, one-off check, like "only show rows above the average."
• Giving it a name would be more effort than it's worth.
• It needs to use the current row from the outer query (a correlated subquery).

Use a CTE when:

• Your query has more than one step, and naming each step makes it easier to follow.
• You need to use the same result more than once in your query.
• You're working with tree-shaped data, like org charts or categories.
• You're debugging and want to check one step at a time.

Using Both Together (This Is Normal)

Most real queries mix the two. A CTE handles the overall structure, and a small subquery handles a quick check inside it. Here's a monthly sales report that does both:

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  total_sales,
  total_sales - (SELECT AVG(total_sales) FROM monthly_sales) AS diff_from_avg
FROM monthly_sales
ORDER BY month;

The CTE groups sales by month. The small subquery at the end compares each month to the average. Simple, and easy to follow.

Final thoughts

Subqueries and CTEs both do similar work, and most SQL developers use both, depending on the situation. Any CTE without recursion could technically be written as a subquery instead — but that doesn't mean it should be. If your query has several steps, let a CTE lay them out clearly. If you just need one quick check, a subquery works fine. And if your data is shaped like a tree, a recursive CTE is really your only option.

The best test isn't "which one is technically correct." It's "can I read this again in six months without getting confused." Aim for that, and you'll be fine.