Skip to content
SQL

SQL CTE: WITH Clause and CTE vs Subquery

Ihsan Ullah · 13 min read
SQL CTE, Lesson 21 of the SQL course: WITH clause and CTE vs subquery

SQL course · Module 6: Subqueries and CTEs · Lesson 21 of 40

Quick answer: A SQL CTE (common table expression) is a named query that you write first with WITH name AS (...) and then use like a table in the main query. It exists only while that one statement runs. A CTE does the same job as a subquery in FROM, but it reads from top to bottom, it can be used more than once in the same query, and later CTEs can build on earlier ones. In PostgreSQL 12 and newer, you can add MATERIALIZED or NOT MATERIALIZED to tell PostgreSQL how to run it.

In this lesson you will learn:

  • the SQL CTE syntax: WITH name AS (...) SELECT ...
  • how to chain several CTEs in one query
  • how to rewrite a nested subquery as a CTE, and when each one is the better choice
  • what MATERIALIZED and NOT MATERIALIZED change in PostgreSQL
  • how a CTE works with UPDATE, DELETE and INSERT

Before you start: Lesson 20, SQL Subqueries: Scalar, IN, EXISTS and Correlated Subqueries and Lesson 10, SQL GROUP BY and HAVING.

We ran every example on PostgreSQL 16.15 with the shop database and checked the current PostgreSQL 18 manual. Everything here also works on newer versions. This lesson does not change the shop data.

SQL CTE syntax: the WITH clause

The PostgreSQL manual describes CTEs as “temporary tables that exist just for one query” (PostgreSQL docs: WITH Queries). You give a query a name with WITH name AS (...), and then the main query reads from that name.

Here is the total of each order, and then only the orders above 150:

WITH order_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT order_id, total
FROM order_totals
WHERE total > 150
ORDER BY total DESC;
 order_id | total  
----------+--------
       11 | 181.00
       22 | 174.00
       14 | 158.99
(3 rows)

The part in brackets makes one row per order with a GROUP BY (Lesson 10). The main SELECT then uses order_totals like a table. WITH ... SELECT ... is one statement, with no semicolon in the middle.

The name does not stay after the statement ends. Try to use it in the next query:

SELECT * FROM order_totals;
ERROR:  relation "order_totals" does not exist
LINE 1: SELECT * FROM order_totals;
                      ^

A saved query that you can reuse is a view (Lesson 23).

Several CTEs in one query

You can write many CTEs after one WITH. Separate them with commas. Each CTE can read the CTEs written above it.

Question: which customers spent more than the average customer? Leave cancelled orders out. We build it in three steps:

WITH order_totals AS (
    SELECT o.id AS order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY o.id, o.customer_id
),
customer_totals AS (
    SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
    FROM order_totals
    GROUP BY customer_id
)
SELECT c.name, ct.orders, ct.spent
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
WHERE ct.spent > (SELECT AVG(spent) FROM customer_totals)
ORDER BY ct.spent DESC;
     name     | orders | spent  
--------------+--------+--------
 Sofia Rossi  |      3 | 412.48
 Ayesha Malik |      3 | 319.99
 bilal ahmed  |      2 | 258.24
 Lucas Martin |      3 | 244.46
 Emma Clarke  |      2 | 231.00
(5 rows)
  • order_totals gives one row per order that was not cancelled, with its customer (INNER JOIN, Lesson 12).
  • customer_totals reads order_totals and adds up each customer’s orders.
  • The main query adds the names and keeps customers above the average (228.47 in a separate check), so five of the nine buyers remain.

customer_totals is used twice: in FROM and inside the subquery in WHERE.

The order matters. A CTE cannot read a CTE that comes later in the list:

WITH customer_totals AS (
    SELECT customer_id, SUM(total) AS spent
    FROM order_totals
    GROUP BY customer_id
),
order_totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT * FROM customer_totals;
ERROR:  relation "order_totals" does not exist
LINE 3:     FROM order_totals
                 ^
DETAIL:  There is a WITH item named "order_totals", but it cannot be referenced from this part of the query.
HINT:  Use WITH RECURSIVE, or re-order the WITH items to remove forward references.

Follow the hint: move order_totals to the top. (WITH RECURSIVE is for queries that read themselves, the topic of Lesson 22.)

CTE vs subquery: rewrite a nested subquery

In Lesson 20 you put subqueries in FROM. Here is the same “above average” question written only with subqueries:

SELECT c.name, ct.orders, ct.spent
FROM (
    SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
    FROM (
        SELECT o.id AS order_id, o.customer_id,
               SUM(oi.quantity * oi.unit_price) AS total
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.id
        WHERE o.status <> 'cancelled'
        GROUP BY o.id, o.customer_id
    ) AS order_totals
    GROUP BY customer_id
) AS ct
JOIN customers c ON c.id = ct.customer_id
WHERE ct.spent > (
    SELECT AVG(spent)
    FROM (
        SELECT customer_id, SUM(total) AS spent
        FROM (
            SELECT o.id AS order_id, o.customer_id,
                   SUM(oi.quantity * oi.unit_price) AS total
            FROM orders o
            JOIN order_items oi ON oi.order_id = o.id
            WHERE o.status <> 'cancelled'
            GROUP BY o.id, o.customer_id
        ) AS order_totals
        GROUP BY customer_id
    ) AS ct2
)
ORDER BY ct.spent DESC;
     name     | orders | spent  
--------------+--------+--------
 Sofia Rossi  |      3 | 412.48
 Ayesha Malik |      3 | 319.99
 bilal ahmed  |      2 | 258.24
 Lucas Martin |      3 | 244.46
 Emma Clarke  |      2 | 231.00
(5 rows)

Same result, but you read it from the inside out. A subquery in FROM has no name outside its own place, so the whole block is written twice. To change the rule later (say, also leave out pending orders), you must edit both copies.

To rewrite nested subqueries as CTEs:

  1. Find the innermost subquery. Move it to the top as the first CTE, and use its alias as the CTE name.
  2. Replace it in the query with that name.
  3. Repeat with the next level out.
SubqueryCTE
reading orderinside outtop to bottom
use the same result twicewrite it twicewrite it once, use the name twice
build step by stepnest deeperadd another CTE
can read itselfnoyes, with WITH RECURSIVE (Lesson 22)
good forshort, one-time filters like WHERE id IN (...)queries with several steps

MATERIALIZED and NOT MATERIALIZED in PostgreSQL

Is a CTE slower than a subquery? Before PostgreSQL 12, a CTE was always run on its own first, before the rest of the query. The release notes say CTEs “are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query” (PostgreSQL 12 release notes). “Inlined” means PostgreSQL folds the CTE into the main query, so it runs like the subquery version.

EXPLAIN shows the plan PostgreSQL makes for a query (PostgreSQL docs: EXPLAIN), and (COSTS OFF) hides the cost numbers. Lesson 32 explains plans; here we only look for the words CTE Scan.

EXPLAIN (COSTS OFF)
WITH pakistan AS (
    SELECT * FROM customers WHERE country = 'Pakistan'
)
SELECT name FROM pakistan WHERE city = 'Lahore';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on customers
   Filter: ((country = 'Pakistan'::text) AND (city = 'Lahore'::text))
(2 rows)

There is no CTE in the plan. PostgreSQL read customers once and checked both conditions together. Now add MATERIALIZED:

EXPLAIN (COSTS OFF)
WITH pakistan AS MATERIALIZED (
    SELECT * FROM customers WHERE country = 'Pakistan'
)
SELECT name FROM pakistan WHERE city = 'Lahore';
                   QUERY PLAN                   
------------------------------------------------
 CTE Scan on pakistan
   Filter: (city = 'Lahore'::text)
   CTE pakistan
     ->  Seq Scan on customers
           Filter: (country = 'Pakistan'::text)
(5 rows)

Now the CTE runs first (CTE pakistan: all customers from Pakistan), and the main query filters that stored result for Lahore (CTE Scan on pakistan). Both plans return the same two rows, Ayesha Malik and Omar Haddad.

When the main query uses a CTE more than once, PostgreSQL materializes it by default, so it is calculated only once. NOT MATERIALIZED forces it “to be merged into the parent query” anyway, as the manual puts it.

Usually you need neither keyword. On big tables, folding in can let PostgreSQL use an index on the real table, while materializing makes an expensive calculation run “only once per table row, not twice” (both cases are in the manual). Add a keyword only after you measure a problem. SQLite copied these keywords from PostgreSQL as “non-binding hints” (SQLite docs: WITH clause); the MySQL 8.4 WITH page does not have them.

CTEs with UPDATE, DELETE and INSERT

A WITH can also come before an UPDATE, DELETE or INSERT. To keep the data unchanged, we run these inside BEGIN … ROLLBACK (Lesson 19).

First, a CTE that finds big orders, used by an UPDATE (Lesson 17):

BEGIN;

WITH big_orders AS (
    SELECT order_id
    FROM order_items
    GROUP BY order_id
    HAVING SUM(quantity * unit_price) > 150
)
UPDATE orders
SET status = 'shipped'
WHERE status = 'pending'
  AND id IN (SELECT order_id FROM big_orders)
RETURNING id, status;
BEGIN
 id | status  
----+---------
 22 | shipped
(1 row)

UPDATE 1

Order 22 (174.00) was the only pending order above 150.

PostgreSQL also lets the CTE itself change data. Here a DELETE inside WITH removes the items of cancelled orders, and RETURNING * passes the removed rows to an INSERT that copies them into a new table:

CREATE TABLE cancelled_items (LIKE order_items);

WITH moved AS (
    DELETE FROM order_items
    WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled')
    RETURNING *
)
INSERT INTO cancelled_items
SELECT * FROM moved;

SELECT * FROM cancelled_items;
SELECT count(*) AS order_items_left FROM order_items;
CREATE TABLE
INSERT 0 2
 order_id | product_id | quantity | unit_price 
----------+------------+----------+------------
        4 |          4 |        1 |      45.00
       12 |         10 |        2 |      54.90
(2 rows)

 order_items_left 
------------------
               38
(1 row)

The lines of orders 4 and 12 moved in one statement. The manual notes that RETURNING “is the only way to communicate changes between different WITH sub-statements and the main query”. Now undo everything:

ROLLBACK;

SELECT count(*) AS order_items_now FROM order_items;
ROLLBACK
 order_items_now 
-----------------
              40
(1 row)

All 40 lines are back, order 22 is pending again, and the cancelled_items table is gone.

Try it yourself

Exercise 1: Revenue per category

Write a CTE called sold with each order line’s product category and amount (quantity * unit_price), leaving out cancelled orders. Then show the revenue per category, highest first.

Exercise 2: More orders than average

Use two CTEs: order_counts (orders per customer, all statuses) and average (the average of those counts). List the customers with more orders than the average.

Exercise 3: Rewrite a subquery as a CTE

Rewrite this query with a CTE called order_totals. The result must not change.

SELECT country, ROUND(AVG(total), 2) AS avg_order
FROM (
    SELECT c.country, o.id, SUM(oi.quantity * oi.unit_price) AS total
    FROM customers c
    JOIN orders o       ON o.customer_id = c.id
    JOIN order_items oi ON oi.order_id = o.id
    GROUP BY c.country, o.id
) AS t
GROUP BY country
ORDER BY avg_order DESC;

Solutions

Solution 1

WITH sold AS (
    SELECT p.category, oi.quantity * oi.unit_price AS amount
    FROM order_items oi
    JOIN orders o   ON o.id = oi.order_id
    JOIN products p ON p.id = oi.product_id
    WHERE o.status <> 'cancelled'
)
SELECT category, SUM(amount) AS revenue
FROM sold
GROUP BY category
ORDER BY revenue DESC;
  category   | revenue 
-------------+---------
 Electronics | 1478.21
 Stationery  |  283.00
 Home Office |  160.00
 Accessories |  135.00
(4 rows)

The CTE does the joins and the filter; the main query only groups.

Solution 2

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS orders
    FROM orders
    GROUP BY customer_id
),
average AS (
    SELECT AVG(orders) AS avg_orders FROM order_counts
)
SELECT c.name, oc.orders, ROUND(a.avg_orders, 2) AS avg_orders
FROM order_counts oc
JOIN customers c ON c.id = oc.customer_id
CROSS JOIN average a
WHERE oc.orders > a.avg_orders
ORDER BY oc.orders DESC, c.name;
     name     | orders | avg_orders 
--------------+--------+------------
 Ayesha Malik |      4 |       2.44
 Lucas Martin |      3 |       2.44
 Sofia Rossi  |      3 |       2.44
(3 rows)

average has one row, so CROSS JOIN adds it to every row; 22 orders from 9 customers is about 2.44.

Solution 3

WITH order_totals AS (
    SELECT c.country, o.id, SUM(oi.quantity * oi.unit_price) AS total
    FROM customers c
    JOIN orders o       ON o.customer_id = c.id
    JOIN order_items oi ON oi.order_id = o.id
    GROUP BY c.country, o.id
)
SELECT country, ROUND(AVG(total), 2) AS avg_order
FROM order_totals
GROUP BY country
ORDER BY avg_order DESC;
 country  | avg_order 
----------+-----------
 Italy    |    137.49
 UK       |    115.50
 Germany  |    102.75
 France   |    102.10
 Pakistan |     99.62
 Canada   |     81.49
 USA      |     58.20
(7 rows)

The inner query moved to the top unchanged, and the alias t became the CTE name; both versions return these 7 rows.

Common mistakes

  • Using the CTE in the next statement. A CTE lives only inside its own statement: relation "order_totals" does not exist.
  • A semicolon after the CTE. WITH pakistan AS (...); ends the statement before the main query, and PostgreSQL says syntax error at or near ";".
  • Writing WITH before every CTE. Write WITH once and separate the CTEs with commas. A second WITH gives syntax error at or near "WITH".
  • Forgetting the comma between CTEs. WITH a AS (...) b AS (...) gives syntax error at or near "b".
  • Reading a CTE that comes later in the list. Put the CTEs in the order they are used; the error says cannot be referenced from this part of the query.

Frequently asked questions

Is a CTE faster than a subquery?

In PostgreSQL 12 and newer, usually neither: a simple CTE used once is folded into the main query and gets the same plan as the subquery. Choose the form that is easier to read, and check with EXPLAIN when speed matters.

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

A CTE exists only during one statement. A temporary table (CREATE TEMP TABLE) is a real table that PostgreSQL drops “at the end of a session”, so many statements can use it (PostgreSQL docs: CREATE TABLE).

Does MySQL support CTEs?

Yes. The MySQL 8.4 manual calls a CTE “a named temporary result set that exists within the scope of a single statement”, the same idea as in PostgreSQL (MySQL docs: WITH). SQLite supports WITH too: our SQLite 3.53.1 test ran order_totals and AS MATERIALIZED.

Summary

  • A SQL CTE names a query with WITH name AS (...); the main query uses it like a table, only inside that one statement.
  • Write several CTEs after one WITH, separated by commas; each can read the ones above it.
  • Compared with nested subqueries, CTEs read from top to bottom and can be used more than once without copying.
  • Since PostgreSQL 12, a simple CTE used once is folded into the main query; MATERIALIZED and NOT MATERIALIZED change that choice.
  • In PostgreSQL, a CTE can come before UPDATE or INSERT, and can contain DELETE ... RETURNING that feeds another statement.

Lesson 20 wrapped a query in a subquery to filter on its result; a CTE can do the same for window functions (Module 7), as the SQL window functions practice problems show.

Next lesson

Next: Lesson 22, SQL Recursive CTE: WITH RECURSIVE for Hierarchies, Trees and Date Series.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *