CTE vs Subquery: When to Use Each in SQL

CTEs and Subqueries Both Solve the Same Problem

Both Common Table Expressions (CTEs) and subqueries let you break complex queries into smaller steps. In interviews, you'll use them constantly — the question is which one to reach for and when.

What Is a CTE?

A CTE (Common Table Expression) is a named temporary result set defined with the WITH keyword:

WITH active_users AS (
    SELECT user_id, COUNT(*) as login_count
    FROM logins
    WHERE login_date >= '2025-01-01'
    GROUP BY user_id
    HAVING COUNT(*) >= 5
)
SELECT u.name, u.email, a.login_count
FROM users u
JOIN active_users a ON u.id = a.user_id
ORDER BY a.login_count DESC;

What Is a Subquery?

A subquery is a query nested inside another query:

SELECT u.name, u.email, a.login_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as login_count
    FROM logins
    WHERE login_date >= '2025-01-01'
    GROUP BY user_id
    HAVING COUNT(*) >= 5
) a ON u.id = a.user_id
ORDER BY a.login_count DESC;

Both queries produce identical results. The difference is style and sometimes performance.

When to Use CTEs

1. Multi-Step Queries

CTEs shine when you need to build up results step by step:

WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date) as month,
           SUM(amount) as revenue
    FROM orders
    GROUP BY 1
),
with_growth AS (
    SELECT month,
           revenue,
           LAG(revenue) OVER (ORDER BY month) as prev_month,
           revenue - LAG(revenue) OVER (ORDER BY month) as growth
    FROM monthly_revenue
)
SELECT month, revenue, growth,
       ROUND(100.0 * growth / NULLIF(prev_month, 0), 1) as growth_pct
FROM with_growth
WHERE growth IS NOT NULL
ORDER BY month;

Trying to write this as nested subqueries would be painful to read.

2. Reusing the Same Logic Multiple Times

If you reference the same derived table more than once, a CTE avoids duplication:

WITH user_metrics AS (
    SELECT user_id,
           COUNT(*) as total_orders,
           SUM(amount) as total_spent
    FROM orders
    GROUP BY user_id
)
SELECT
    (SELECT AVG(total_spent) FROM user_metrics) as avg_spending,
    (SELECT AVG(total_spent) FROM user_metrics WHERE total_orders > 5) as avg_spending_power_users,
    (SELECT COUNT(*) FROM user_metrics WHERE total_spent > 1000) as high_value_count;

3. Recursive Queries

Only CTEs support recursion — useful for hierarchical data:

WITH RECURSIVE org_chart AS (
    -- Base case: CEO (no manager)
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with managers
    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, name;

When to Use Subqueries

1. Simple Filtering

For simple EXISTS or IN checks, subqueries are more concise:

-- Find customers who placed an order in the last 30 days
SELECT name, email
FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

2. Scalar Subqueries

When you need a single value in a SELECT or WHERE clause:

-- Find employees earning above average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

3. Correlated Subqueries

When the inner query references the outer query:

-- Find each department's highest earner
SELECT name, department, salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

Performance Comparison

In most modern databases (PostgreSQL, BigQuery, Snowflake), CTEs and subqueries perform identically. The optimizer inlines CTEs just like subqueries.

However, there are exceptions:

• PostgreSQL < 12: CTEs were optimization fences (always materialized). After v12, they're inlined by default.
• MySQL 8.0+: CTEs are supported and generally well-optimized
• Materialized CTEs: Some databases let you force materialization, which is useful when a CTE is referenced multiple times and is expensive to compute

Interview tip: If asked about CTE vs subquery performance, say "In modern databases, they're typically optimized the same way. I choose based on readability. But in older PostgreSQL versions, CTEs could be slower because they acted as optimization barriers."

Best Practices for Interviews

Do:

• Use CTEs for multi-step analysis — it shows structured thinking
• Name CTEs descriptively (monthly_revenue, not t1)
• Use subqueries for simple filters (IN, EXISTS)
• Start your solution by sketching the CTE structure before writing SQL

Don't:

• Nest subqueries more than 2 levels deep — use CTEs instead
• Create unnecessary CTEs for simple operations
• Mix CTEs and deeply nested subqueries in the same query

Interview Example: Putting It Together

Find the month with the highest number of first-time purchasers.

WITH first_purchases AS (
    SELECT customer_id,
           MIN(order_date) as first_order_date
    FROM orders
    GROUP BY customer_id
),
monthly_new_customers AS (
    SELECT DATE_TRUNC('month', first_order_date) as month,
           COUNT(*) as new_customers
    FROM first_purchases
    GROUP BY 1
)
SELECT month, new_customers
FROM monthly_new_customers
ORDER BY new_customers DESC
LIMIT 1;

This would be much harder to read as nested subqueries.

Practice CTE Problems

Master CTEs with our CTE interview problems — from basic single CTEs to recursive queries and multi-step analysis.