Subqueries vs CTEs: Writing Cleaner SQL

A subquery nests one query inside another. A Common Table Expression (CTE) names that inner query up front with the WITH keyword. Both return the same rows in most cases. The real differences are readability, reuse, recursion, and how your specific database engine plans the query. This guide shows when each one earns its place, with runnable code and a decision framework.

Quick Takeaways

• CTEs improve readability. They name each logical step and read top to bottom.
• Subqueries are compact. They suit one-off filters, scalar lookups, and EXISTS checks.
• Performance is engine-dependent. PostgreSQL 12+ inlines most CTEs, while older versions and some engines may materialize them.
• Only CTEs support recursion. Hierarchies and graph traversals need WITH RECURSIVE.

The Sample Dataset

Every example below uses two tables. Create them in any SQL engine to follow along.

-- customers: one row per customer
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100),
    region        VARCHAR(20)
);

-- orders: one row per order
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_date  DATE,
    amount      DECIMAL(10,2)
);

The business question: which customers spent more than the average customer in their region during 2026?

What Is a Subquery?

A subquery is a SELECT statement enclosed in parentheses inside another statement. It can appear in WHERE, FROM, or SELECT.

Types of Subqueries

Subquery Solution

SELECT c.customer_name,
       c.region,
       t.total_spend
FROM customers AS c
JOIN (
    -- derived table: total spend per customer in 2026
    SELECT customer_id,
           SUM(amount) AS total_spend
    FROM orders
    WHERE order_date >= '2026-01-01'
      AND order_date <  '2027-01-01'
    GROUP BY customer_id
) AS t
  ON t.customer_id = c.customer_id
WHERE t.total_spend > (
    -- scalar subquery: average of the per-customer totals in this region
    SELECT AVG(x.total_spend)
    FROM (
        SELECT o.customer_id,
               SUM(o.amount) AS total_spend
        FROM orders AS o
        JOIN customers AS c2
          ON c2.customer_id = o.customer_id
        WHERE c2.region = c.region
          AND o.order_date >= '2026-01-01'
          AND o.order_date <  '2027-01-01'
        GROUP BY o.customer_id
    ) AS x
);

This works. It also nests three levels deep, repeats the 2026 filter twice, and correlates on c.region. Reviewing it takes effort.

What Is a CTE?

A CTE defines a temporary named result set that exists for the duration of a single statement. You declare it with WITH name AS (...) and reference it like a table.

CTE Solution

WITH customer_spend AS (
    -- Step 1: total spend per customer in 2026
    SELECT o.customer_id,
           SUM(o.amount) AS total_spend
    FROM orders AS o
    WHERE o.order_date >= '2026-01-01'
      AND o.order_date <  '2027-01-01'
    GROUP BY o.customer_id
),
regional_spend AS (
    -- Step 2: attach region to each customer total
    SELECT c.customer_id,
           c.customer_name,
           c.region,
           cs.total_spend
    FROM customers AS c
    JOIN customer_spend AS cs
      ON cs.customer_id = c.customer_id
),
regional_avg AS (
    -- Step 3: average customer spend per region
    SELECT region,
           AVG(total_spend) AS avg_spend
    FROM regional_spend
    GROUP BY region
)
-- Step 4: keep customers above their regional average
SELECT rs.customer_name,
       rs.region,
       rs.total_spend,
       ra.avg_spend
FROM regional_spend AS rs
JOIN regional_avg  AS ra
  ON ra.region = rs.region
WHERE rs.total_spend > ra.avg_spend
ORDER BY rs.region, rs.total_spend DESC;

The 2026 filter appears once. Each step has a name. You can test any CTE in isolation by selecting from it. The query also returns the regional average, which the subquery version did not.

Performance: Do CTEs Run Slower?

The answer depends on the engine. The SQL standard defines what a query returns, not how the engine executes it.

Two rules follow from this table:

1. Do not assume a CTE caches results. In SQL Server, a CTE referenced three times can execute three times.
2. Do not assume a CTE is slower. In modern PostgreSQL, a single-reference CTE compiles to the same plan as the equivalent subquery.

Verify With EXPLAIN

Never guess. Compare plans directly.

-- PostgreSQL: force materialization to test its effect
EXPLAIN (ANALYZE, BUFFERS)
WITH customer_spend AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total_spend
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
)
SELECT * FROM customer_spend WHERE total_spend > 10000;

-- Same query, allow the optimizer to inline
EXPLAIN (ANALYZE, BUFFERS)
WITH customer_spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total_spend
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
)
SELECT * FROM customer_spend WHERE total_spend > 10000;

Compare actual time, rows, and buffers in both outputs. Materialization helps when a CTE is expensive and referenced many times. It hurts when the outer query could push a selective filter down into the CTE.

Benchmarking Both Styles in Python

Run this script to time both approaches on your own database. It uses sqlite3 and pandas, so it needs no server.

import sqlite3
import time
import numpy as np
import pandas as pd

rng = np.random.default_rng(42)  # reproducible data

# Build synthetic data: 5,000 customers, 500,000 orders
customers = pd.DataFrame({
    "customer_id": np.arange(5_000),
    "customer_name": [f"cust_{i}" for i in range(5_000)],
    "region": rng.choice(["NA", "EU", "APAC", "LATAM"], 5_000),
})
orders = pd.DataFrame({
    "order_id": np.arange(500_000),
    "customer_id": rng.integers(0, 5_000, 500_000),
    "order_date": pd.to_datetime("2026-01-01")
                  + pd.to_timedelta(rng.integers(0, 365, 500_000), unit="D"),
    "amount": rng.gamma(2.0, 50.0, 500_000).round(2),
})

con = sqlite3.connect(":memory:")
customers.to_sql("customers", con, index=False)
orders.assign(order_date=orders["order_date"].dt.strftime("%Y-%m-%d")) \
      .to_sql("orders", con, index=False)
con.execute("CREATE INDEX idx_orders_cust ON orders(customer_id)")

subquery_sql = """
SELECT COUNT(*) FROM (
    SELECT customer_id, SUM(amount) AS s
    FROM orders GROUP BY customer_id
) t WHERE t.s > (SELECT AVG(s) FROM (
    SELECT SUM(amount) AS s FROM orders GROUP BY customer_id))
"""

cte_sql = """
WITH spend AS (
    SELECT customer_id, SUM(amount) AS s FROM orders GROUP BY customer_id
)
SELECT COUNT(*) FROM spend WHERE s > (SELECT AVG(s) FROM spend)
"""

def timeit(sql, runs=5):
    times = []
    for _ in range(runs):
        t0 = time.perf_counter()
        con.execute(sql).fetchone()
        times.append(time.perf_counter() - t0)
    return np.median(times)  # median resists outliers

print(f"Subquery median: {timeit(subquery_sql):.4f}s")
print(f"CTE median:      {timeit(cte_sql):.4f}s")

Expect near-identical timings on small data. Differences appear when the CTE is expensive and referenced repeatedly. Always benchmark on your production engine, not SQLite.

When Subqueries Win

Use a subquery when the logic is short and local.

EXISTS for Semi-Joins

-- Customers with at least one order over $500
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1                      -- value is irrelevant; existence matters
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 500
);

EXISTS short-circuits on the first match. A CTE adds ceremony without benefit here.

Scalar Lookups

-- Flag orders above the global average
SELECT order_id,
       amount,
       amount > (SELECT AVG(amount) FROM orders) AS above_avg
FROM orders;

One value, one use, one line. A CTE would be longer and no clearer.

When CTEs Win

Multi-Step Transformations

Chain CTEs when each stage feeds the next. The earlier regional example shows this pattern. Each CTE acts as a labeled stage, similar to a Pandas method chain.

Recursive Queries

Only CTEs can walk hierarchies such as org charts, bills of materials, or category trees.

WITH RECURSIVE org AS (
    -- anchor member: top-level managers
    SELECT employee_id, manager_id, employee_name, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- recursive member: direct reports of the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, o.depth + 1
    FROM employees AS e
    JOIN org AS o
      ON e.manager_id = o.employee_id
)
SELECT * FROM org ORDER BY depth, employee_name;

Add a depth limit (WHERE o.depth < 20) to guard against cycles in dirty data.

Reusing One Result Several Times

If you need the same aggregate in two places, a CTE avoids duplicated SQL text. Remember that duplicated text and duplicated execution are different problems. In engines that inline CTEs, the logic may still run twice.

Decision Framework

Real-World Use Cases

Customer Churn Cohorts

A retention analyst needs customers whose last order was more than 90 days ago, split by first-purchase quarter. A CTE chain reads cleanly:

WITH last_order AS (
    SELECT customer_id,
           MIN(order_date) AS first_order,
           MAX(order_date) AS last_order_date
    FROM orders
    GROUP BY customer_id
),
churned AS (
    SELECT customer_id, first_order
    FROM last_order
    WHERE last_order_date < CURRENT_DATE - INTERVAL '90 days'
)
SELECT DATE_TRUNC('quarter', first_order) AS cohort_quarter,
       COUNT(*) AS churned_customers
FROM churned
GROUP BY 1
ORDER BY 1;

Deduplicating Event Streams

Sensor and clickstream tables often contain repeated events. Pair a CTE with ROW_NUMBER() to keep only the latest record per device:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY device_id, event_type
               ORDER BY ingested_at DESC
           ) AS rn
    FROM sensor_events
)
SELECT * FROM ranked WHERE rn = 1;

Data Quality Audits

Use a subquery with NOT EXISTS to find orphaned records:

SELECT o.order_id
FROM orders AS o
WHERE NOT EXISTS (
    SELECT 1 FROM customers AS c
    WHERE c.customer_id = o.customer_id
);

Common Mistakes to Avoid

Style Rules for Cleaner SQL

1. Name CTEs by what they contain, such as customer_spend, not cte1.
2. Keep one transformation per CTE. Filter, aggregate, or join, but avoid all three at once.
3. Comment the business logic, not the syntax.
4. Select only needed columns. SELECT * inside a CTE can defeat column pruning in some engines.
5. Format consistently. Align keywords and indent nested blocks.

FAQ

Is a CTE faster than a subquery?

Not by default. Most modern optimizers produce the same plan for a single-reference CTE and its equivalent subquery. Speed differs only when the engine materializes the CTE or when the CTE is referenced multiple times. Check EXPLAIN output on your own engine.

Can a CTE be used more than once in a query?

Yes. You can reference a CTE multiple times in the same statement. The query text is defined once, but some engines (such as SQL Server) re-execute it per reference. PostgreSQL materializes it when referenced more than once.

What is the difference between a CTE and a temporary table?

A CTE exists only for one statement and cannot be indexed. A temporary table persists for the session, stores results physically, and supports indexes and statistics. Use a temp table when a heavy intermediate result feeds several separate queries.

When should I avoid CTEs?

Avoid them for trivial filters and EXISTS checks, where a subquery is shorter. Also avoid long chains in engines that expand CTEs inline, because the final plan can grow complex and hard to tune.