Anyone who writes SQL eventually hits the same fork in the road: do you tuck a subquery inside your SELECT, FROM, or WHERE clause, or pull it out to the top as a Common Table Expression (CTE)?
Under the hood, modern databases usually compile both into the exact same execution plan. Speed isn't the real battleground here; maintainability is. Choosing the right structure determines whether a teammate can understand your query in ten seconds or has to untangle it line by line.
Inside-Out vs. Top-Down
Subqueries: Inside-Out Storytelling
A subquery is simply a SELECT statement nested inside another query. You usually drop them into one of three spots:
- The
SELECTclause: Pulling a single value per row (known as a scalar subquery). - The
FROMclause: Acting as an inline, temporary table. - The
WHEREorHAVINGclause: Filtering rows usingINor comparison operators.
-- 1. In the SELECT clause: Show each order alongside the company average
SELECT
customer_id,
order_total,
(SELECT AVG(order_total) FROM orders) AS company_avg
FROM orders;
-- 2. In the WHERE clause: Filter for orders larger than that average
SELECT
customer_id,
order_total
FROM orders
WHERE order_total > (
SELECT AVG(order_total)
FROM orders
);
(Note: A subquery inside a SELECT clause must always return a single column and a single row. Otherwise, the database will throw an error.)
CTEs: Top-Down Storytelling
A Common Table Expression (CTE) lets you define a temporary, named result set at the very top of your query using a WITH clause. It lives only for that single execution and disappears the moment the query finishes.
-- CTE: The exact same filtering logic, written top-to-bottom
WITH average_order AS (
SELECT AVG(order_total) AS avg_total
FROM orders
)
SELECT
o.customer_id,
o.order_total
FROM orders o
CROSS JOIN average_order a
WHERE o.order_total > a.avg_total;
How They Stack Up
| CTEs (WITH) | Subqueries | |
| Readability | High. Reads linearly from top to bottom. | Lower. Forces you to read from the inside out. |
| Reusability | Define once, reference anywhere in the query. | Must be copy-pasted if used more than once. |
| Footprint | Requires a setup block at the top of the file. | Compact; lives right inside the clause that uses it. |
| Performance | Identical to subqueries in modern engines. | Identical to CTEs in modern engines. |
When to Reach for a CTE
1. You Are Chaining Multiple Transformations
When a query requires three or four distinct steps (cleaning raw events, rolling them up by user, and filtering the winners), nested subqueries quickly collapse into an unreadable wall of parentheses. CTEs turn that mess into a clean, step-by-step pipeline where each block builds on the last.
WITH completed_sales AS (
SELECT
user_id,
amount,
sale_date
FROM raw_sales
WHERE status = 'COMPLETED'
),
monthly_spend AS (
SELECT
user_id,
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS total
FROM completed_sales
GROUP BY 1, 2
)
SELECT
user_id,
month,
total
FROM monthly_spend
WHERE total > 1000;
2. You Need the Same Dataset Twice
Suppose you want to compare each department's spending against the company-wide average, or calculate month-over-month growth. A CTE lets you define that baseline table once and join against it multiple times. Doing that with subqueries means duplicating code and praying nobody updates one copy without updating the other.
3. You Are Actively Debugging
CTEs make troubleshooting painless. If a 100-line query returns strange numbers, you don't have to dissect nested brackets. You just highlight everything down to step two, swap the final query to SELECT * FROM step_two, and inspect the data mid-flight.
When a Subquery Makes More Sense
1. Quick, Single-Purpose Filters or Scalar Lookups
If you only need a fast WHERE id IN (...) check, a single threshold, or one quick scalar metric in your SELECT list, spinning up a WITH block adds ceremony without adding clarity. Keep it inline where the intent is obvious at a glance.
-- Clean, self-contained inline filter
SELECT
customer_id,
name
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2026-01-01'
);
2. Tiny, One-Off Derived Tables
If a derived table is three lines long, used once, and doesn't clutter the outer SELECT, leaving it inline in the FROM clause keeps the logic right next to where it's consumed.
Clearing Up the Performance Myth
You will still hear veteran developers warn that CTEs hurt performance because databases always dump them into temporary tables on disk. That advice is outdated.
- What modern engines actually do: PostgreSQL (12+), SQL Server, MySQL (8.0+), Oracle, Snowflake, and BigQuery all treat standard CTEs as inline views by default. Their query optimizers merge the CTE into the main query, push filters down to the underlying indexes, and generate the exact same execution plan as a subquery.
- When it differs: If you reference a CTE multiple times, or explicitly force caching with
AS MATERIALIZEDin Postgres, the database may compute it once and hold the result in memory. Depending on table size and indexes, that can either save duplicate work or block index usage. When speed matters, letEXPLAIN ANALYZEbe the judge, not guesswork.
The Golden Rule
If you can read the query in one glance without tracking parentheses, keep it as a subquery. The moment you have to trace your finger from the inside out to understand what is happening, pull it out into a CTE.
Modern databases handle the optimization either way. Your job is to make the logic unmistakable.
