CTEs and views: writing long SQL queries that stay readable

SQL queries tend to grow. You start with a simple SELECT, then add a join, a subquery to exclude a few cases, another to calculate a comparison average. A few months later the query fills two screens, has subqueries nested four levels deep and nobody, including whoever wrote it, is keen to change it. SQL offers precise tools to prevent this drift: common table expressions and views.

Common table expressions (CTEs)

A CTE is a query given a name at the start of the statement, with the WITH clause, and then used as if it were a table. It exists only for the duration of that single query.

Take a concrete question: which customers spent more in 2025 than twice the average spending of customers in the same region?

Written with nested subqueries:

SELECT x.customer_id, x.spent
FROM (SELECT o.customer_id, c.region, SUM(o.total) AS spent
      FROM orders o JOIN customers c ON c.id = o.customer_id
      WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
      GROUP BY o.customer_id, c.region) x
JOIN (SELECT region, AVG(spent) AS avg_spent
      FROM (SELECT o.customer_id, c.region, SUM(o.total) AS spent
            FROM orders o JOIN customers c ON c.id = o.customer_id
            WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
            GROUP BY o.customer_id, c.region) y
      GROUP BY region) m ON m.region = x.region
WHERE x.spent > 2 * m.avg_spent;

The same spending calculation appears twice, and to understand it you have to read from the inside out. With CTEs:

WITH spending_2025 AS (
  SELECT o.customer_id, c.region, SUM(o.total) AS spent
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id, c.region
),
region_average AS (
  SELECT region, AVG(spent) AS avg_spent
  FROM spending_2025
  GROUP BY region
)
SELECT s.customer_id, s.region, s.spent, r.avg_spent
FROM spending_2025 s
JOIN region_average r ON r.region = s.region
WHERE s.spent > 2 * r.avg_spent;

The query reads from top to bottom, as a sequence of named steps. Spending per customer is calculated once and reused. If the definition of «spending» changes in future, for example to exclude cancelled orders, it is changed in one place only.

Checking step by step

A huge practical advantage of CTEs is being able to check the intermediate steps. To verify that spending_2025 is correct, just temporarily replace the final query with SELECT * FROM spending_2025 and look at the result, perhaps comparing the total with a known value. With nested subqueries, isolating a step means taking the query apart.

Performance

For a long time, in some databases, CTEs were always computed in full and stored in a temporary result, which prevented certain optimisations. PostgreSQL, for example, changed this behaviour in version 12: today a CTE used only once is normally folded into the main query and optimised along with it. When in doubt, the execution plan shows what really happens.

Recursive CTEs

A special variant, WITH RECURSIVE, lets a CTE refer to itself. It is the tool for walking hierarchies of unknown depth, such as an organisation chart:

WITH RECURSIVE reports AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE id = 10
  UNION ALL
  SELECT e.id, e.name, e.manager_id, r.level + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;

The first part is the starting point; the second adds, on each pass, the employees reporting to those already found, until there are none left. If the data contains a cycle by mistake, the recursion never ends: it is wise to add a limit on the level.

Views

A view is a query saved in the database under a name. You query it like a table, but each time it runs the underlying query on the current data:

CREATE VIEW valid_orders AS
SELECT *
FROM orders
WHERE status <> 'cancelled' AND is_test = false;

Views are mainly for sharing definitions. If every report must exclude cancelled and test orders, writing the rule in a view stops each analyst from reinterpreting it in their own way. They are also a security tool: you can give a user permission to read a view showing only some columns, without giving access to the full table.

Materialized views

A materialized view physically stores the query result, like a table. Reading it is fast, but the data is frozen at the last refresh, which must be requested explicitly or scheduled:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', order_date) AS month, SUM(total) AS revenue
FROM valid_orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue;

The example uses PostgreSQL syntax; other databases offer similar features under different names. They are ideal for frequently viewed dashboards on data that can be refreshed every hour or every night.

Which tool to use

In short

• CTEs name the steps of a query and make it readable from top to bottom.
• Each step of a CTE can be checked on its own.
• WITH RECURSIVE walks hierarchies of unknown depth.
• Views share common definitions and restrict access to data.
• Materialized views speed up heavy summaries, at the cost of data that is not real-time.