CTE vs Subquery in SQL: Readability, Reuse, and Performance

A CTE names a query result with WITH; a subquery embeds a query inside another statement. Choose the form that makes the data transformations easiest to understand. A CTE is not automatically faster, and it is not a permanent table.

This guide compares an ordinary, nonrecursive CTE with a derived-table subquery in FROM. Scalar subqueries, correlated subqueries, and recursive CTEs solve other problems and are not interchangeable with every example here.

Situation	Useful starting point
One short intermediate calculation	Subquery
Several named transformation steps	CTE
Reuse a result across separate statements	Consider a view or temporary table instead
Need a performance decision	Measure on the actual database

The question is: which customers have at least 150 in paid payments? Refunded payments must not contribute.

Try it yourself in SQLite

Open SQL Lab, paste this setup, and run it once. Then run each query separately. Table and column names stay in English so the same SQL works in every language. The lab interface and linked lessons are currently in English.

CREATE TABLE blog_payments (
  id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, amount INTEGER
);
INSERT INTO blog_payments VALUES
  (1, 1, 'paid', 120), (2, 1, 'paid', 80), (3, 1, 'refunded', 90),
  (4, 2, 'paid', 60), (5, 3, 'paid', 200);

1. Use a derived-table subquery

The inner query filters paid payments and totals them per customer. The outer query filters those totals. The alias totals names the derived table. Customers 1 and 3 qualify with 200 each; customer 2 has only 60.

SELECT customer_id, paid_total
FROM (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
) AS totals
WHERE paid_total >= 150
ORDER BY customer_id;

Expected result

customer_id	paid_total
1	200
3	200

2. Give the same intermediate result a CTE name

The result is identical. WITH moves the intermediate calculation above the main SELECT and gives it the name paid_totals. That name exists only within this statement. If you run another independent SELECT afterward, paid_totals is not a stored table you can query.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT customer_id, paid_total
FROM paid_totals
WHERE paid_total >= 150
ORDER BY customer_id;

Expected result

customer_id	paid_total
1	200
3	200

3. Chain transformations without deep nesting

The first CTE computes paid totals. The second labels customer segments; the main query summarizes those segments. This separates row filtering, customer aggregation, classification, and final reporting. The high segment has two customers and revenue of 400; standard has one customer and revenue of 60.

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
), customer_segments AS (
  SELECT customer_id, paid_total,
    CASE WHEN paid_total >= 150 THEN 'high' ELSE 'standard' END AS segment
  FROM paid_totals
)
SELECT segment, COUNT(*) AS customers, SUM(paid_total) AS revenue
FROM customer_segments
GROUP BY segment
ORDER BY segment;

Expected result

segment	customers	revenue
high	2	400
standard	1	60

Practice and check your answer

Change the qualification threshold in the first two queries from 150 to 210. Both should return no rows. Then include all payment statuses and restore the threshold to 150: customer 1 reaches 290 and customer 3 remains at 200. This checks that the row filter, rather than the choice between CTE and subquery, defines the business result.

For performance, compare execution plans and timings on the engine and dataset you actually use. PostgreSQL can fold certain CTEs into the parent query or materialize them depending on the query; its documentation explains the conditions and hints. SQLite also has its own planning behavior. Naming a result does not guarantee one physical execution or a cached result.

Do not add materialization hints as a general tuning habit. A useful investigation records the database version, indexes, row counts, query plan, and timings over repeated runs. The tiny example here teaches correctness and structure; it cannot establish a production performance winner.

Use a CTE when a meaningful name removes ambiguity. Use a short subquery when it keeps related logic together. In either form, list required columns instead of relying on SELECT *, and check row counts before and after joins to avoid accidental duplication.

Documentation and next steps

Try the CTE lesson and subqueries in FROM lesson. The examples run in SQLite and use ordinary WITH syntax also available in PostgreSQL and MySQL 8. Engine-specific materialization syntax is intentionally not used in the runnable examples.

• SQLite reference
• Database reference