CTE vs Subquery vs View vs Temporary Table in MySQL

MySQL gives you four ways to name an intermediate result. They look similar, and each has a different lifetime and cost.

	Subquery / derived table	CTE (`WITH`)	View	Temporary table
Lives	One statement	One statement	Permanently in the schema	Your session
Stored data	No	No	No (just a stored query)	Yes (rows are stored)
Reusable in the same statement	No (repeat the text)	Yes	Yes	Yes
Reusable across statements	No	No	Yes	Yes, in your session
Can have an index	No (MySQL may add one)	No (MySQL may add one)	On the underlying tables	Yes
Recursive	No	Yes	Only via a recursive CTE inside it	No
Privileges / sharing	n/a	n/a	Granted like a table	Visible only to you
Best for	A single small step	Multi-step queries, recursion	A shared definition for many users	Big intermediate results used many times

The same query, four ways

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

Goal: customers whose spending is above the average customer spend.

Derived table:

query.sql

SQL

SELECT t.customer_id, t.spent
FROM (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) AS t
WHERE t.spent > (SELECT AVG(spent) FROM (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) AS u)
ORDER BY t.customer_id;

Output

OUTPUT

+-------------+---------+
| customer_id | spent   |
+-------------+---------+
|           1 | 1979.00 |
|           3 | 1529.00 |
+-------------+---------+
2 rows in set

The inner query is written twice.

CTE:

query.sql

SQL

1
2

WITH totals AS (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id)
SELECT customer_id, spent FROM totals WHERE spent > (SELECT AVG(spent) FROM totals) ORDER BY customer_id;

Output

OUTPUT

+-------------+---------+
| customer_id | spent   |
+-------------+---------+
|           1 | 1979.00 |
|           3 | 1529.00 |
+-------------+---------+
2 rows in set

The query is written once and read twice.

View:

query.sql

SQL

1
2

CREATE VIEW customer_totals AS SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id;
SELECT customer_id, spent FROM customer_totals WHERE spent > (SELECT AVG(spent) FROM customer_totals) ORDER BY customer_id;

Output

OUTPUT

+-------------+---------+
| customer_id | spent   |
+-------------+---------+
|           1 | 1979.00 |
|           3 | 1529.00 |
+-------------+---------+
2 rows in set

The definition now exists in the database, and every query and every user can use customer_totals.

Temporary table:

query.sql

SQL

CREATE TEMPORARY TABLE tmp_totals AS SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id;
ALTER TABLE tmp_totals ADD PRIMARY KEY (customer_id);
SET @average_spend = (SELECT AVG(spent) FROM tmp_totals);
SELECT customer_id, spent FROM tmp_totals WHERE spent > @average_spend ORDER BY customer_id;

Output

OUTPUT

+-------------+---------+
| customer_id | spent   |
+-------------+---------+
|           1 | 1979.00 |
|           3 | 1529.00 |
+-------------+---------+
2 rows in set

The summary is computed once and stored, and can be indexed. It stays for the whole session, and several later statements can use it. One restriction: MySQL cannot open the same temporary table twice in one statement. A query such as SELECT ... FROM tmp_totals WHERE spent > (SELECT AVG(spent) FROM tmp_totals) fails with error 1137, “Can’t reopen table”, which is why the average is read into a variable first.

How MySQL runs CTEs and derived tables

If the CTE (or derived table) is simple (no GROUP BY, DISTINCT, LIMIT, UNION), MySQL can merge it into the main query, as it does for a view.
Otherwise MySQL materialises it in an internal temporary table, once. A CTE referenced twice is materialised once and shared.
MySQL may add an automatic index on a materialised derived table to speed up a join.

You can influence it with the optimiser hints MERGE() and NO_MERGE():

query.sql

SQL

1
2
3

EXPLAIN FORMAT=TRADITIONAL
WITH totals AS (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id)
SELECT /*+ NO_MERGE(totals) */ customer_id FROM totals WHERE spent > 1000;

Output

OUTPUT

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL            |
|  2 | DERIVED     | orders     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set

A common wrong belief

“A CTE is evaluated once and stored, so it is faster than a subquery.” Not necessarily. A merged CTE is just part of the query. A materialised one is a temporary table with no indexes of your own. A CTE is a readability tool, not a performance tool.

Decision guide

Situation	Choose
A small, one-off step	Derived table
A multi-step query you want to read top to bottom	CTE
A tree or a series	Recursive CTE
The same logic is needed by many queries, reports and people	View
An expensive intermediate result used by several statements in a session or a script	Temporary table (with an index)
An intermediate result needed permanently, refreshed periodically	A real summary table (Summary Tables)

CTE inside a view

A view can contain a CTE in its definition (MySQL 8.0), so you can combine them:

query.sql

SQL

CREATE VIEW top_customers AS
WITH totals AS (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id)
SELECT customer_id, spent FROM totals WHERE spent > 1000;
SELECT * FROM top_customers ORDER BY customer_id;

Output

OUTPUT

+-------------+---------+
| customer_id | spent   |
+-------------+---------+
|           1 | 1979.00 |
|           3 | 1529.00 |
+-------------+---------+
2 rows in set

lightbulb

Start simple

Write the query as a CTE for clarity. If it is slow, look at EXPLAIN. Only then decide whether a temporary table, an index or a summary table is the right fix.