SQL CTEs vs Subqueries: When to Use Each (With Examples)

One of the most common SQL interview follow-up questions is:

"Could you rewrite that as a CTE? Why would you choose one over the other?"

Most candidates fumble this because they learned CTEs and subqueries as interchangeable — and while they often are, the reasons for choosing one reveal how you think about code quality and performance.

This guide breaks down both techniques with real examples so you can answer confidently.

1. What is a subquery?

A subquery is a SELECT nested inside another SQL statement.

sql
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE amount > 1000
);

The inner SELECT runs first and produces a result that the outer query uses.

Subqueries can appear in:

• WHERE clauses (filtering)
• FROM clauses (inline views)
• SELECT clauses (scalar subqueries)

2. What is a CTE?

A CTE (Common Table Expression) defines a named temporary result set using WITH, before the main query.

sql
WITH high_value_orders AS (
  SELECT customer_id
  FROM orders
  WHERE amount > 1000
)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM high_value_orders);

The CTE block runs first and acts like a temporary view scoped to the query.

3. Side-by-side comparison

Same business problem — two styles:

Subquery style:

sql
SELECT
  customer_id,
  country,
  total_spend
FROM (
  SELECT
    c.customer_id,
    c.country,
    SUM(o.amount) AS total_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.country
) AS customer_totals
WHERE total_spend > 5000
ORDER BY total_spend DESC;

CTE style:

sql
WITH customer_totals AS (
  SELECT
    c.customer_id,
    c.country,
    SUM(o.amount) AS total_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.country
)
SELECT customer_id, country, total_spend
FROM customer_totals
WHERE total_spend > 5000
ORDER BY total_spend DESC;

The logic is identical. The difference is readability.

4. When to use a subquery

Subqueries are a good fit when:

The logic is short and self-contained:

sql
SELECT name
FROM employees
WHERE department_id = (
  SELECT id FROM departments WHERE name = 'Engineering'
);

One line. Easy to read inline. A CTE here would be over-engineering.

You need a scalar value in the SELECT clause:

sql
SELECT
  product_id,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

Scalar subqueries in SELECT are cleaner than joining to a one-row CTE.

The subquery is used only once:

If you only reference the intermediate result in one place, a subquery keeps things compact.

5. When to use a CTE

CTEs shine when:

The intermediate logic is complex enough to deserve a name:

sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
revenue_with_lag AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  ROUND(100.0 * (revenue - prev_month_revenue) / prev_month_revenue, 2) AS pct_change
FROM revenue_with_lag
ORDER BY month;

Try writing that as nested subqueries. It quickly becomes unreadable.

You need to reference the same intermediate result more than once:

sql
WITH active_users AS (
  SELECT user_id
  FROM users
  WHERE last_active >= NOW() - INTERVAL '30 days'
)
SELECT COUNT(*) AS total_active FROM active_users
UNION ALL
SELECT COUNT(*) FROM active_users WHERE country = 'IN';

Without a CTE, you would have to write the subquery twice — duplicating logic and risking inconsistency if you update it.

You are building logic in sequential steps:

CTEs encourage a step-by-step thinking style that maps naturally to how data transformations work:

sql
WITH step1 AS (...),
     step2 AS (...),
     step3 AS (...)
SELECT * FROM step3;

This is easier to debug: you can run each step independently by changing the final SELECT.

6. Recursive CTEs — a special case

CTEs have one capability subqueries do not: recursion.

"Given an employee hierarchy, find all direct and indirect reports of a given manager."

sql
WITH RECURSIVE org_chart AS (
  -- Anchor: start from the manager
  SELECT employee_id, manager_id, name, 1 AS depth
  FROM employees
  WHERE employee_id = 42

  UNION ALL

  -- Recursive: find direct reports of each row
  SELECT e.employee_id, e.manager_id, e.name, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY depth;

Recursive CTEs are the standard way to traverse trees and hierarchies in SQL. Subqueries cannot do this.

Interview tip: If a question involves hierarchies, parent-child relationships, or "find all levels," mention recursive CTEs. Even if you do not implement one live, naming it shows senior-level awareness.

7. Performance: does it actually matter?

In most modern databases (Postgres, BigQuery, Snowflake, SQL Server), the optimizer treats CTEs and subqueries very similarly for simple cases.

A few nuances worth knowing:

• Postgres (pre-12): CTEs were optimization fences — the planner could not push predicates through them. From Postgres 12+, this changed and the planner can inline CTEs.
• BigQuery and Snowflake: generally inline CTEs like subqueries unless they are recursive.
• SQL Server: CTEs are always inlined unless recursive.

For most interview and analytics work, readability matters more than micro-performance differences between CTEs and subqueries. Optimize only when you have profiling data.

What to say in an interview: "I default to CTEs for multi-step logic because they are easier to read and debug. I would profile both if performance became a concern."

8. Quick decision guide

9. How to practice this in real problems

The best way to internalize this choice is to:

1. Solve a problem using subqueries first.
2. Rewrite it using CTEs.
3. Ask yourself: which one would a teammate understand faster at 9am?

On TheQueryLab, most multi-step problems can be approached either way. Try the SQL Interview Preparation Kit — the window function and aggregation problems are ideal for practicing CTEs with multiple sequential steps.

Once CTEs feel natural, your SQL becomes significantly easier to review, debug, and extend.