(opens in a new tab)

CTE vs Subquery vs View in SQL: Which One to Use

September 11, 2026 by Chat2DB Team

SQL gives you at least three ways to name an intermediate result and build on it: a common table expression (CTE), a subquery, and a view. They often produce identical rows and, on modern databases, identical execution plans. So the CTE vs subquery vs view question is rarely about speed. It is about scope, reuse, permissions and how readable the result is six months later.

This article writes the same query three ways on a small sample schema, then works through the situations where each form is the right choice. It also covers the related question of subquery vs join, including how to rewrite a correlated subquery, and closes with a decision table and the two errors that trip people up most often.

Sample schema

All examples run on PostgreSQL. Create the tables and data first; every query below is written to run as is against them.

CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    name         VARCHAR(50) NOT NULL,
    country      VARCHAR(2)  NOT NULL
);
 
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),
    order_date   DATE NOT NULL,
    amount       NUMERIC(10, 2) NOT NULL,
    status       VARCHAR(10) NOT NULL
);
 
INSERT INTO customers VALUES
    (1, 'Alice', 'US'),
    (2, 'Bruno', 'BR'),
    (3, 'Chen',  'CN'),
    (4, 'Dana',  'US'),
    (5, 'Emeka', 'NG');
 
INSERT INTO orders VALUES
    (101, 1, '2026-01-05', 120.00, 'paid'),
    (102, 1, '2026-02-14',  80.50, 'paid'),
    (103, 2, '2026-01-20', 300.00, 'paid'),
    (104, 3, '2026-03-02',  45.00, 'refunded'),
    (105, 3, '2026-03-09', 210.00, 'paid'),
    (106, 4, '2026-02-28',  60.00, 'pending'),
    (107, 1, '2026-03-15', 500.00, 'paid');

The task for the first section: list every customer whose total of paid orders is at least 250, with their name and total, highest total first. The expected answer is Alice (700.50) and Bruno (300.00).

The same query written three ways

Version 1: CTE

WITH paid_totals AS (
    SELECT customer_id, SUM(amount) AS total_paid
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT c.name, p.total_paid
FROM customers AS c
JOIN paid_totals AS p ON p.customer_id = c.customer_id
WHERE p.total_paid >= 250
ORDER BY p.total_paid DESC;

Version 2: derived-table subquery

A derived table is a subquery in the FROM clause. It must have an alias.

SELECT c.name, p.total_paid
FROM customers AS c
JOIN (
    SELECT customer_id, SUM(amount) AS total_paid
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
) AS p ON p.customer_id = c.customer_id
WHERE p.total_paid >= 250
ORDER BY p.total_paid DESC;

Version 3: view

A view stores the query definition in the database catalog. Create it once, then query it like a table.

CREATE VIEW paid_totals_v AS
SELECT customer_id, SUM(amount) AS total_paid
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;
 
SELECT c.name, p.total_paid
FROM customers AS c
JOIN paid_totals_v AS p ON p.customer_id = c.customer_id
WHERE p.total_paid >= 250
ORDER BY p.total_paid DESC;

All three return:

 name  | total_paid
-------+------------
 Alice |     700.50
 Bruno |     300.00

The logic is identical. What differs is where the definition lives and how long it lasts:

• The CTE lives for one statement.
• The derived table lives for one statement and only at the spot where it is written.
• The view lives in the database until someone drops it, and is visible to every session and user with permission.

When to use a CTE

Choose a CTE when the intermediate result belongs to exactly one query and you want that query to read top to bottom. CTEs are also the only option when you need to reference the same intermediate result twice in one statement without repeating it, and the only option for recursion.

Here is a case where a CTE is referenced twice. The goal is to show each qualifying customer's share of the total across all qualifying customers.

WITH paid_totals AS (
    SELECT customer_id, SUM(amount) AS total_paid
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT c.name,
       p.total_paid,
       ROUND(100.0 * p.total_paid / (SELECT SUM(total_paid) FROM paid_totals), 1) AS pct_of_total
FROM customers AS c
JOIN paid_totals AS p ON p.customer_id = c.customer_id
ORDER BY p.total_paid DESC;

 name  | total_paid | pct_of_total
-------+------------+--------------
 Alice |     700.50 |         57.9
 Bruno |     300.00 |         24.8
 Chen  |     210.00 |         17.3

With a derived table you would have to paste the aggregate twice. With a view you could do it, but you would be creating a permanent object for a one-off report.

When to use a subquery

A derived-table subquery is a fine choice when the inner query is short, used once, and does not deserve a name. Many developers prefer the CTE anyway for consistency, and there is nothing wrong with that.

Subqueries have two other forms that CTEs do not replace: scalar subqueries and predicate subqueries (IN, EXISTS, comparisons). These are covered in the subquery vs join section below, because that is where the real decision lies.

When to use a view

Choose a view when the definition must be shared across statements, sessions, applications or people. Three concrete reasons:

Reuse across statements. If five reports all start from "paid totals per customer", a view means the logic is written once and fixed once.

Permission boundaries. A view can expose a subset of columns or rows from a table the user is not allowed to read directly. Grant SELECT on the view, not the table.

CREATE ROLE reporting NOLOGIN;
 
CREATE VIEW customer_orders_public AS
SELECT c.name, c.country, o.order_date, o.status
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id;
 
GRANT SELECT ON customer_orders_public TO reporting;

A member of reporting can now see who ordered when, but not the amount column, and cannot query orders directly.

Stable interface. If the underlying tables get refactored, the view can be redefined so downstream queries keep working.

The cost is that views are schema objects. They need migrations, they can break when a column they depend on is dropped (PostgreSQL will refuse the drop unless you use CASCADE), and their definitions live away from the code that uses them.

If a view is expensive and queried far more often than its data changes, a materialized view stores the result physically and refreshes on demand; that is a separate topic and out of scope here.

Optimizer treatment: are CTEs slower?

This is the most common worry, so it is worth being precise.

In PostgreSQL 12 and later, a non-recursive CTE that is referenced exactly once and contains no data-modifying statement is inlined into the outer query. The planner treats it exactly as it would treat the equivalent derived-table subquery, including pushing WHERE conditions from the outer query down into it. Performance is therefore the same.

Before PostgreSQL 12, a CTE was always an optimization fence: it was planned and executed as a separate unit and the outer filter could not be pushed inside. On large tables this could make the CTE version noticeably slower than the subquery version, and this history is why "CTEs are slow" is still repeated.

You can check this yourself. Run EXPLAIN (COSTS OFF) on version 1 and version 2 from the earlier section:

EXPLAIN (COSTS OFF)
WITH paid_totals AS (
    SELECT customer_id, SUM(amount) AS total_paid
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT c.name, p.total_paid
FROM customers AS c
JOIN paid_totals AS p ON p.customer_id = c.customer_id
WHERE p.total_paid >= 250
ORDER BY p.total_paid DESC;

On PostgreSQL 12+ the output for both versions has the same shape. The important thing to look for is that there is no CTE Scan node; instead you see the aggregate over orders joined directly to customers, something like this:

Sort
  Sort Key: (sum(orders.amount)) DESC
  ->  Hash Join
        Hash Cond: (orders.customer_id = c.customer_id)
        ->  HashAggregate
              Group Key: orders.customer_id
              Filter: (sum(orders.amount) >= 250)
              ->  Seq Scan on orders
                    Filter: ((status)::text = 'paid'::text)
        ->  Hash
              ->  Seq Scan on customers c

The exact join order and node types depend on your version, statistics and data size; on this tiny sample everything is a sequential scan. The point is the absence of a CTE Scan node, which is what you would have seen before version 12. If you add MATERIALIZED after AS in the CTE, the CTE Scan node comes back, and the total_paid >= 250 filter is applied on top of it instead of inside the aggregate.

A view behaves like a subquery in this respect too: the planner expands the view definition into the query and optimizes the whole thing together. So for the three versions above, the optimizer sees essentially the same query.

Other databases follow the same principle. SQL Server, Oracle and MySQL 8 all treat CTEs, derived tables and views as query text to be merged and optimized as a whole, with the optimizer free to materialize when it judges that to be cheaper.

A quick way to compare plans side by side is to open two query tabs in Chat2DB (download at https://chat2db.ai/download (opens in a new tab) or use the web version at https://app.chat2db.ai (opens in a new tab)) and run EXPLAIN on each form against the same connection.

Subquery vs join

Beyond derived tables, subqueries appear in the SELECT list and in WHERE predicates. In both places a join is frequently an alternative, and the choice affects both clarity and, on larger data, the plan.

Scalar subquery in the SELECT list vs JOIN

Task: show every customer with the number of orders they have placed, including customers with none. First with a scalar subquery:

SELECT c.name,
       (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers AS c
ORDER BY c.customer_id;

Now with a join and aggregate:

SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Both return:

 name  | order_count
-------+-------------
 Alice |           3
 Bruno |           1
 Chen  |           2
 Dana  |           1
 Emeka |           0

The scalar subquery is easy to read for a single computed column, and the optimizer can often turn it into a join internally. The join form scales better when you need several aggregates from the same child table (count, sum, max date), because one pass over orders produces them all, whereas three scalar subqueries describe three separate lookups. Use LEFT JOIN rather than JOIN so customers with no orders, like Emeka, are kept.

Correlated subquery vs join rewrite

A correlated subquery references a column from the outer query, so conceptually it runs once per outer row. Task: for each customer, show the date of their most recent order.

SELECT c.name,
       (SELECT MAX(o.order_date)
        FROM orders AS o
        WHERE o.customer_id = c.customer_id) AS last_order
FROM customers AS c
ORDER BY c.customer_id;

The join rewrite aggregates first, then joins:

SELECT c.name, MAX(o.order_date) AS last_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

 name  | last_order
-------+------------
 Alice | 2026-03-15
 Bruno | 2026-01-20
 Chen  | 2026-03-09
 Dana  | 2026-02-28
 Emeka |

Both give the same result. The rewrite matters most when the correlated subquery cannot be flattened by the optimizer, or when you need to return additional columns from the matched child row (for example the amount of the latest order), which a scalar subquery cannot do without a second subquery. For that "latest row per group" case, a CTE with a window function is often the cleanest:

WITH ranked AS (
    SELECT customer_id, order_date, amount,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT c.name, r.order_date AS last_order, r.amount AS last_amount
FROM customers AS c
LEFT JOIN ranked AS r ON r.customer_id = c.customer_id AND r.rn = 1
ORDER BY c.customer_id;

 name  | last_order | last_amount
-------+------------+-------------
 Alice | 2026-03-15 |      500.00
 Bruno | 2026-01-20 |      300.00
 Chen  | 2026-03-09 |      210.00
 Dana  | 2026-02-28 |       60.00
 Emeka |            |

EXISTS vs IN vs JOIN for filtering

To find customers who have at least one refunded order, all three forms work:

-- EXISTS (correlated)
SELECT c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o
              WHERE o.customer_id = c.customer_id AND o.status = 'refunded');
 
-- IN (uncorrelated)
SELECT c.name
FROM customers AS c
WHERE c.customer_id IN (SELECT customer_id FROM orders WHERE status = 'refunded');
 
-- JOIN with DISTINCT
SELECT DISTINCT c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'refunded';

Each returns one row, Chen. Prefer EXISTS or IN when you only want to filter the outer rows: they express a semi-join, cannot duplicate rows, and PostgreSQL plans them as semi-joins. Use a real JOIN when you need columns from the child table in the output. Avoid NOT IN with a subquery that can return NULL; it will return no rows at all, and NOT EXISTS is the safe alternative.

A note on CTE vs temp table

A temporary table stores actual rows for the duration of the session, survives across statements, can be indexed and analyzed, and is the right tool when a multi-step procedure needs the same intermediate result in several statements. A CTE cannot do any of that. If you find yourself wanting to "keep" a CTE for the next query, that is the signal to switch to a temporary table.

Pitfalls

Subquery returns more than one row

A scalar subquery or an = comparison expects exactly one value. This query fails because several orders are paid:

SELECT name
FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'paid');

ERROR:  more than one row returned by a subquery used as an expression

Fix it with IN, or with EXISTS, or by adding an aggregate or LIMIT 1 when a single value really is intended:

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'paid');

Ambiguous column names

When two joined sources share a column name and you reference it without a prefix, the database refuses to guess:

SELECT customer_id, name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id;

ERROR:  column reference "customer_id" is ambiguous

Always qualify columns in multi-table queries (c.customer_id). This bites especially often when a CTE or view reuses column names from the base table.

Derived table without an alias

PostgreSQL and SQL Server require an alias on every subquery in FROM. Forgetting it produces subquery in FROM must have an alias. MySQL reports Every derived table must have its own alias. Add AS something after the closing parenthesis.

Decision table

Summary

CTEs, derived-table subqueries and views are different ways to package the same SQL. On PostgreSQL 12+ and other modern optimizers they produce the same plans when referenced once, so choose by scope and reuse rather than speed: a CTE for a single statement, a view for logic that must be shared or protected, and a temporary table when the result has to outlive one statement. For subquery vs join, use EXISTS or IN to filter, a join to pull in child columns or several aggregates at once, and a CTE with a window function for latest-row-per-group problems. Whichever form you choose, qualify your column names and make sure scalar subqueries can return only one row.

FAQ

Is a CTE the same as a view?

No. A CTE is defined inside one statement and disappears when it finishes. A view is a saved query definition stored in the database that any statement can reference. You can, however, use a CTE inside a view definition.

Should I rewrite all my subqueries as CTEs?

Not necessarily. For a single short derived table, a subquery is fine and some teams prefer it. Switch to a CTE when the query has more than one intermediate step, when the same intermediate result is needed twice, or when you need recursion. Performance on modern databases is usually the same either way.

Why did my query get slower after I moved a subquery into a CTE?

Check your PostgreSQL version. Before version 12, CTEs were optimization fences and outer filters could not be pushed into them. On version 12 or later, check whether the CTE is referenced more than once (which prevents inlining by default) or whether it contains a data-modifying statement. EXPLAIN will show a CTE Scan node when the CTE is materialized. Adding NOT MATERIALIZED forces inlining if that is what you want.