(opens in a new tab)

What Is a CTE in SQL? Common Table Expressions

September 11, 2026 by Chat2DB Team

A common table expression (CTE) is a named, temporary result set that you define at the top of a single SQL statement with the WITH clause and then reference like a table in the rest of that statement. It exists only while the statement runs. Nothing is stored, no permissions are needed beyond those for the underlying tables, and no cleanup is required afterwards.

If you have ever written a query with a subquery nested inside another subquery, and then had to scroll left and right to understand what it did, a SQL CTE is the tool that fixes that. This article walks through the syntax, the scoping rules, the places where a CTE can be used, a short introduction to recursion, dialect support, and the errors people hit most often.

Sample schema

Every example in this article runs against the two tables below. The SQL is written for PostgreSQL, and the notes at the end explain where other databases differ. Create the tables first.

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');

Customer 5 (Emeka) has no orders on purpose. That gap is useful later.

The WITH clause syntax

The basic shape of a common table expression in SQL is:

WITH cte_name AS (
    -- any SELECT statement
    SELECT ...
)
SELECT ...
FROM cte_name;

Here is a first real example. It computes the total paid amount per customer, then joins that result back to the customers table.

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

Expected output:

 name  | country | total_paid
-------+---------+------------
 Alice | US      |     700.50
 Bruno | BR      |     300.00
 Chen  | CN      |     210.00

Read the query top to bottom: first the named block paid_totals is defined, then the main SELECT uses it. That is the whole idea. The CTE gives an intermediate result a name so the final query can be read as a sentence.

Several CTEs in one statement

You can define more than one CTE by separating them with commas. Only the first one gets the WITH keyword. A later CTE may reference an earlier one.

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

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

The second CTE builds on the first. This chaining is where CTEs really pay off: each step is small, named and testable on its own.

The column list form

Instead of aliasing columns inside the CTE body, you can list the output column names right after the CTE name. This is the WITH t(a, b) AS (...) form.

WITH order_counts (customer_id, order_count) AS (
    SELECT customer_id, COUNT(*)
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, COALESCE(o.order_count, 0) AS order_count
FROM customers AS c
LEFT JOIN order_counts AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id;

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

The number of names in the list must match the number of columns the inner SELECT produces. The column list form is required in some recursive CTEs (Oracle in particular) and is generally a good habit when the inner query uses expressions without obvious names.

A CTE is scoped to one statement

This is the single most important rule to understand. A CTE lives only for the statement that defines it. The moment the statement finishes, the name is gone.

WITH us_customers AS (
    SELECT customer_id, name FROM customers WHERE country = 'US'
)
SELECT * FROM us_customers;
 
-- A separate statement. This fails.
SELECT * FROM us_customers;

The first statement returns Alice and Dana. The second statement produces:

ERROR:  relation "us_customers" does not exist

A CTE is not a temporary table and not a view. If you need the same named result set across several statements, you want a view (a saved query definition) or a temporary table (real stored rows for the session). If you need it once, in one statement, a CTE is the right choice.

Why CTEs beat nested subqueries for readability

CTEs and derived-table subqueries are largely interchangeable in terms of what they can compute. The difference is the shape of the code. Here is the two-step "big spenders" query rewritten with nested subqueries:

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

It returns the same two rows. But to understand it you read from the innermost parentheses outward, which is the opposite of how the logic flows. With the CTE version, the logic and the reading order match. Three more practical benefits:

• A CTE can be referenced more than once in the same statement. A derived table has to be copied and pasted.
• You can test a CTE in isolation by temporarily replacing the final SELECT with SELECT * FROM cte_name.
• Recursive queries are only possible with a CTE; a plain subquery cannot refer to itself.

Using a CTE with INSERT, UPDATE and DELETE

A SQL CTE is not limited to SELECT. All the major databases let the final statement be a data-modifying statement.

CTE feeding an INSERT

Suppose you keep a small summary table and want to fill it from the orders table.

CREATE TABLE customer_summary (
    customer_id INT PRIMARY KEY,
    total_paid  NUMERIC(10, 2),
    order_count INT
);
 
WITH agg AS (
    SELECT customer_id,
           SUM(amount) FILTER (WHERE status = 'paid') AS total_paid,
           COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
INSERT INTO customer_summary (customer_id, total_paid, order_count)
SELECT customer_id, COALESCE(total_paid, 0), order_count
FROM agg;

Four rows are inserted (customers 1 through 4). The FILTER clause is PostgreSQL syntax; in other databases use SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END).

CTE feeding an UPDATE

Mark every order of any customer whose paid total is at least 500 as a VIP order. Note the WITH comes before UPDATE.

ALTER TABLE orders ADD COLUMN vip BOOLEAN DEFAULT FALSE;
 
WITH vip_customers AS (
    SELECT customer_id
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
    HAVING SUM(amount) >= 500
)
UPDATE orders
SET vip = TRUE
WHERE customer_id IN (SELECT customer_id FROM vip_customers);

Three rows are updated, all belonging to Alice.

CTE feeding a DELETE

Remove refunded orders older than a cutoff:

WITH stale_refunds AS (
    SELECT order_id
    FROM orders
    WHERE status = 'refunded'
      AND order_date < '2026-06-01'
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM stale_refunds);

One row (order 104) is deleted.

PostgreSQL data-modifying CTE with RETURNING

PostgreSQL goes a step further and allows INSERT, UPDATE or DELETE inside the CTE itself, as long as the statement uses RETURNING. That turns the modified rows into a result set that later parts of the statement can use. This is a clean way to archive and delete in one atomic statement.

CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
 
WITH moved AS (
    DELETE FROM orders
    WHERE status = 'pending'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

Order 106 (Dana's pending order) is removed from orders and inserted into orders_archive in a single statement. If either part fails, neither happens. This feature is specific to PostgreSQL; SQL Server, MySQL and Oracle allow a CTE in front of a data-modifying statement but not a data-modifying statement inside the CTE.

Recursive CTEs in brief

A recursive CTE references its own name inside its definition. It has two parts joined by UNION ALL: an anchor query that produces the starting rows, and a recursive member that produces the next rows from the previous ones. Execution stops when the recursive member returns no rows.

The simplest useful example is generating a series of dates, which you can then use to report months with zero orders.

WITH RECURSIVE months (month_start) AS (
    SELECT DATE '2026-01-01'
    UNION ALL
    SELECT (month_start + INTERVAL '1 month')::DATE
    FROM months
    WHERE month_start < DATE '2026-04-01'
)
SELECT m.month_start,
       COUNT(o.order_id) AS orders_in_month
FROM months AS m
LEFT JOIN orders AS o
       ON o.order_date >= m.month_start
      AND o.order_date <  (m.month_start + INTERVAL '1 month')::DATE
GROUP BY m.month_start
ORDER BY m.month_start;

 month_start | orders_in_month
-------------+-----------------
 2026-01-01  |               2
 2026-02-01  |               1
 2026-03-01  |               2
 2026-04-01  |               0

(Counts reflect the table after the delete steps above. Run this before the DELETE examples to see the original counts of 2, 2, 3 and 0.)

Recursive CTEs are also how you walk hierarchies such as org charts, category trees and bill-of-materials graphs. Always include a termination condition in the recursive member; without one the query runs until it hits a recursion limit or memory. Recursive CTEs deserve their own article, so this section stays short.

Dialect notes

The core WITH ... AS (...) SELECT syntax is part of the SQL standard and works the same way in every major database. Differences appear at the edges.

The most common surprise in this list is MySQL 5.7. Many hosted environments still run it, and the error message (You have an error in your SQL syntax ... near 'WITH') does not make it obvious that the feature simply does not exist there.

If you work with several of these databases, a client that shows you the server version and lets you switch connections quickly saves time. You can run all the queries in this article in Chat2DB, either by downloading it at https://chat2db.ai/download (opens in a new tab) or using the web version at https://app.chat2db.ai (opens in a new tab).

MATERIALIZED and NOT MATERIALIZED in PostgreSQL 12+

Before PostgreSQL 12, every CTE was an optimization fence: the planner computed it fully and separately, and could not push predicates from the outer query into it. From version 12 onward, a non-recursive CTE that is referenced exactly once and has no side effects is inlined into the outer query, so it is planned like a subquery. You can override the decision with a hint: WITH t AS MATERIALIZED (...) forces the old fence behavior, which is occasionally useful when you want a CTE evaluated once and reused, and WITH t AS NOT MATERIALIZED (...) forces inlining even when the CTE is referenced more than once. In most queries the default is correct, and you should only reach for the hint after looking at EXPLAIN output.

Common errors and how to fix them

relation "cte_name" does not exist

You referenced a CTE in a different statement than the one that defined it. Either fold both statements into one WITH block, or switch to a temporary table or view. This also appears when a CTE name is misspelled in the main query.

WITH clause must be followed by SELECT, INSERT, UPDATE or DELETE

The WITH block is not a standalone statement. Writing a WITH block and stopping, or following it with CREATE TABLE or ALTER TABLE, produces a syntax error in every dialect. If you want to create a table from CTE output, use CREATE TABLE new_table AS WITH ... SELECT ... (the WITH goes after AS).

Incorrect syntax near 'WITH' (SQL Server)

The previous statement in the batch was not terminated with a semicolon, so the parser thinks WITH is a table hint on the previous statement. Add the semicolon, or start the CTE with ;WITH.

Column count mismatch

With the WITH t(a, b) AS (...) form, the number of listed names must equal the number of columns in the inner SELECT. Add or remove names until they match.

Recursive query returns too many rows or never finishes

The recursive member is missing a stopping condition, or the data contains a cycle. Add a WHERE clause that bounds the depth, or track visited ids with an array or path column.

Key takeaways

• A common table expression is a named result set introduced with WITH that exists only for the one statement that defines it.
• CTEs are mostly equivalent to derived-table subqueries in what they compute, but read top to bottom and can be referenced more than once.
• The final statement after WITH can be SELECT, INSERT, UPDATE or DELETE. PostgreSQL also allows data-modifying statements inside the CTE with RETURNING.
• Use WITH RECURSIVE (PostgreSQL, MySQL 8, SQLite) or plain WITH (SQL Server, Oracle) for recursive queries, and always include a termination condition.
• MySQL 5.7 does not support CTEs at all. PostgreSQL 12+ inlines single-reference CTEs unless you write MATERIALIZED.

FAQ

Is a CTE faster than a subquery?

Usually not, and usually not slower either. On modern optimizers (PostgreSQL 12+, SQL Server, Oracle, MySQL 8) a non-recursive CTE referenced once is planned the same way as the equivalent subquery. The reason to use a CTE is readability and reuse within a statement, not speed. The exception is PostgreSQL before version 12 and any case where you deliberately use MATERIALIZED, where the CTE acts as an optimization fence.

Can I use a CTE inside a view or a stored procedure?

Yes. A view definition is a SELECT, and that SELECT may start with WITH. Inside stored procedures and functions, each statement may have its own WITH block. The CTE still lives only for that one statement.

Can a CTE reference a CTE defined later in the same WITH block?

No. CTEs in one WITH list are visible only to CTEs that come after them and to the main statement. Reorder the definitions so that each CTE appears after the ones it depends on. The only exception is a recursive CTE, which may reference itself.