Lesson 25 of 26 · see the practice database

Views and WITH (common table expressions)

Queries grow. A report on customer spending needs four tables joined, cancelled orders filtered out and a sum, before the interesting part even starts. Writing that out every time is slow, and every copy is a chance for a mistake. SQL offers two ways to give a query a name and build on it: views, which are saved in the database, and the WITH clause, which names a step inside a single query.

Views

A view is a saved query with a name. After it is created, you use it like a table.

CREATE VIEW order_totals AS
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status,
       SUM(oi.quantity * p.price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
GROUP BY o.id, o.customer_id, o.order_date, o.status;

SELECT * FROM order_totals ORDER BY total DESC LIMIT 5;

The joins and the sum are hidden behind the name. Now questions about order values are short.

SELECT status, COUNT(*) AS orders, SUM(total) AS value
FROM order_totals
GROUP BY status
ORDER BY value DESC;

A view can be joined like any table.

SELECT c.name, COUNT(*) AS orders, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON t.customer_id = c.id
WHERE t.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT 5;

What a view is and is not

• A view stores the query, not the data. Each time you read the view, the database runs the query again, so the result is always up to date.
• It takes almost no space.
• It does not make anything faster by itself. It makes things shorter and more consistent.
• If everyone uses the same view for "order total", everyone's reports agree. That is its real value.

Views are also used to control access: a view can show only some columns of a table, such as staff names and departments without salaries, and people can be given permission to read the view and not the table.

CREATE VIEW staff_directory AS
SELECT id, name, department FROM employees;

SELECT * FROM staff_directory WHERE department = 'Sales';

Remove a view with DROP VIEW. The tables underneath are not affected.

DROP VIEW staff_directory;

To change a view, MySQL, PostgreSQL and Oracle offer CREATE OR REPLACE VIEW. In SQLite, drop it and create it again.

Some databases also have materialised views, which do store the result and refresh it on request or on a schedule. They trade freshness for speed and are used for heavy reports.

WITH: naming a step inside a query

A view lasts until someone drops it. Often you want to name a step just for the query you are writing. The WITH clause does that. The named step is called a common table expression, or CTE.

WITH customer_orders AS (
  SELECT customer_id, COUNT(*) AS n
  FROM orders
  GROUP BY customer_id
)
SELECT ROUND(AVG(n), 2) AS average_orders_per_customer
FROM customer_orders;

This is the "summary of a summary" from the lesson on subqueries, written the other way up. With a subquery in FROM, you read from the inside out. With WITH, you read from top to bottom: first this step, called customer_orders, then the main query that uses it. Most people find that easier.

Several steps

Separate the steps with commas. A later step can use an earlier one.

WITH line_values AS (
  SELECT oi.order_id, oi.quantity * p.price AS value
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
),
totals AS (
  SELECT o.customer_id, SUM(lv.value) AS spent
  FROM orders o
  JOIN line_values lv ON lv.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
)
SELECT c.name, t.spent
FROM totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > (SELECT AVG(spent) FROM totals)
ORDER BY t.spent DESC;

Customers who have spent more than the average customer. Notice that totals is used twice: once in the FROM and once in the subquery that finds the average. A subquery in FROM would have had to be written out twice. That is a real advantage of a CTE.

A good way to build such a query is one step at a time. Write the first step and run it alone. When it is right, wrap it in WITH and write the next.

Each customer compared with the best

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity * p.price) AS spent
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON oi.product_id = p.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
)
SELECT c.name, s.spent,
       ROUND(100.0 * s.spent / (SELECT MAX(spent) FROM spend)) AS percent_of_top
FROM spend s
JOIN customers c ON c.id = s.customer_id
ORDER BY s.spent DESC
LIMIT 5;

A CTE that refers to itself

A recursive CTE can use its own name inside its definition. It starts with one or more rows, then keeps adding rows built from the ones it already has, until nothing new appears. The simplest use is to make a run of numbers.

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 6
)
SELECT n FROM numbers;

The part before UNION ALL is the starting row. The part after it runs again and again, each time on the rows just added. The WHERE n < 6 is what stops it. Leave it out and the query never ends.

That trick solves the "missing months" problem from the lesson on dates: make the list of months, then left join the orders to it.

WITH RECURSIVE months(m) AS (
  SELECT '2026-01-01'
  UNION ALL
  SELECT date(m, '+1 month') FROM months WHERE m < '2026-12-01'
)
SELECT strftime('%Y-%m', m) AS month, COUNT(o.id) AS orders
FROM months
LEFT JOIN orders o ON strftime('%Y-%m', o.order_date) = strftime('%Y-%m', m)
GROUP BY m
ORDER BY m;

Now November and December appear with a zero, where a plain GROUP BY would have left them out.

Walking a hierarchy

The classic use of recursion is a tree, such as who reports to whom. Start with the person at the top, then repeatedly add the people who report to someone already found.

WITH RECURSIVE chain(id, name, level, path) AS (
  SELECT id, name, 1, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, c.level + 1, c.path || ' > ' || e.name
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT level, name, path
FROM chain
ORDER BY path;

A self join can show one level of managers. A recursive CTE handles any depth. SQLite, PostgreSQL and MySQL (from version 8) write WITH RECURSIVE. SQL Server and Oracle write just WITH.

View, CTE or subquery?

They are different ways of writing the same logic. Choose the one that makes the query easiest for the next person to read.

Try it yourself

WITH counts AS (SELECT category, COUNT(*) AS n FROM products GROUP BY category) SELECT category FROM counts WHERE n = 4 ORDER BY category;

CREATE VIEW in_stock AS SELECT id, name, stock FROM products WHERE stock > 0;

WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 10) SELECT n FROM nums;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.