CTEs in SQL

January 30, 2026

I was refactoring a long query and realized CTEs are the cleanest way to break it into readable steps.

It can be challenging to read, understand and maintain complex queries that have complex joins and subqueries. CTEs helps break these complex queries into smaller, more readable parts.

So we use a WITH keyword to define and name asubquery and then use it in the main query.

For example :

with cte_name as (
    select ...
)
select ...
from cte_name

lets take a simple example where we want to find the the total number of users in the US who have paid and ordered something.

Without CTEs :

SELECT u.email, o.id AS order_id, o.total
FROM (
  SELECT id, email
  FROM users
  WHERE country = 'US'
) u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'PAID';

With CTEs :

WITH us_users AS (
  SELECT id, email
  FROM users
  WHERE country = 'US'
)
SELECT u.email, o.id AS order_id, o.total
FROM us_users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'PAID';

We can chain multiple CTEs to build step-by-step logic.

Some databases (like PostgreSQL) materialize CTEs meaning they are created as a temporary table and then used in the main query. Unlike other databases (like MySQL) which do not materialize CTEs.(meaning they are not created as a temporary table and are used directly in the main query)

Non-materialized is more like a function call, where the CTE is evaluated and the result is used in the main query.
Materialized is more like writing the result into a temporary scratch table and then using it in the main query.

So check EXPLAIN if performance matters.

Materialization can be good when the CTE is used multiple times in the main query, because it avoids re-evaluating the CTE multiple times. (compute once, reuse , reuse meaning in the same sql statement, not across queries, only in the same query.)
Materialization can be bad when the CTE is used only once in the main query, or when the CTE is large but only needs a subset of the data.

WITH us_users AS MATERIALIZED (
  SELECT id, email
  FROM users
  WHERE country = 'US'
)
SELECT ...
-- (outer query reads will read from it using CTE SCAN)

WITH us_users AS NOT MATERIALIZED (
  SELECT id, email
  FROM users
  WHERE country = 'US'
)
SELECT ...