Chain Multiple CTEs in a Single WITH Clause

Define several named subqueries in one `WITH` block, each building on the previous, before the final `SELECT`.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find customers whose total order value exceeds 200

order_totals aggregates raw rows into per-customer sums: Alice 230, Bob 290, Carol 50. high_value reads from order_totals and keeps only customers over 200. The final SELECT reads from high_value. Chaining CTEs keeps each step named and independently readable, avoiding deeply nested subqueries.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE orders (id INT, customer VARCHAR(50), amount INT);

INSERT INTO
  orders (id, customer, amount)
VALUES
  (1, 'Alice', 80),
  (2, 'Alice', 150),
  (3, 'Bob', 200),
  (4, 'Carol', 50),
  (5, 'Bob', 90);

Validated query Shared across supported engines.

SQL

WITH
  order_totals AS (
    SELECT
      customer,
      SUM(amount) AS total
    FROM
      orders
    GROUP BY
      customer
  ),
  high_value AS (
    SELECT
      customer,
      total
    FROM
      order_totals
    WHERE
      total > 200
  )
SELECT
  customer,
  total
FROM
  high_value
ORDER BY
  total DESC;

Expected result Returned rows for the shared example.

customer	total
Bob	290
Alice	230

All engines return the same rows ordered by total descending: Bob (290) first, then Alice (230).

Useful when

Where this command helps.

staging intermediate aggregations before a final filter or join
splitting a multi-step transformation into named, readable steps

Explanation

What the command is doing.

A single WITH clause can contain multiple comma-separated CTEs. Each CTE can reference any CTE defined before it in the same list. This lets you break a complex query into readable, named steps without nesting subqueries. All four engines support chained non-recursive CTEs using the same syntax. MySQL 8+ and MariaDB 10.2+ added CTE support; older versions require equivalent subqueries.

Chain Multiple CTEs in a Single WITH Clause

Find customers whose total order value exceeds 200

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.