Refactoring With CTEs

Rewrite a deeply nested query as named CTEs, and check EXPLAIN to confirm the plan is unchanged.

SyntaxWITH step_one AS (innermost_subquery), step_two AS (SELECT ... FROM step_one) SELECT ... FROM step_two

Most unreadable SQL is not complicated logic; it is simple logic written inside out. A derived table inside a derived table inside a join forces the reader to start at the innermost parenthesis and work outward. Lifting each level into a named CTE reverses that: the query reads top to bottom, and each step has a name that says what it is.

The refactor

Work outward-in. Take the innermost derived table, move it to a WITH clause under its existing alias, and leave the alias in place at the call site. Repeat for the next level. Nothing else changes — the same aliases, the same join conditions, the same column list. When every level has been lifted, rename the aliases from dt1, t2, x to what they hold.

Confirm the plan

Because MySQL merges mergeable CTEs exactly as it merges derived tables, a pure lift usually produces a byte-identical plan. Prove it rather than assume it: capture EXPLAIN FORMAT=TREE before and after and diff the output. If it changed, the refactor did more than rename something.

What trips people up

A CTE is a fine place for a name, but a bad place for a convenience LIMIT, an extra DISTINCT to tidy duplicates, or a UNION that used to be two branches — each of those blocks merging and turns a free rename into a materialized temporary table. Also watch for a CTE whose name matches a real table; it shadows it silently.

Example

Example · sql
-- BEFORE: correct, but you have to read it inside out.
SELECT c.email, x.order_count, x.revenue
FROM customers AS c
JOIN (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
    FROM (
        SELECT o.id, o.customer_id, o.total
        FROM orders AS o
        WHERE o.status = 'paid'
          AND o.placed_at >= '2026-01-01'
    ) AS y
    GROUP BY customer_id
) AS x ON x.customer_id = c.id
WHERE x.revenue > 500
ORDER BY x.revenue DESC;

-- AFTER: same aliases lifted out, then renamed. Same plan.
WITH paid_orders AS (              -- was the inner derived table `y`
    SELECT o.id, o.customer_id, o.total
    FROM orders AS o
    WHERE o.status = 'paid'
      AND o.placed_at >= '2026-01-01'
),
customer_totals AS (               -- was the outer derived table `x`
    SELECT customer_id,
           COUNT(*)   AS order_count,
           SUM(total) AS revenue
    FROM paid_orders
    GROUP BY customer_id
)
SELECT c.email, ct.order_count, ct.revenue
FROM customers AS c
JOIN customer_totals AS ct ON ct.customer_id = c.id
WHERE ct.revenue > 500
ORDER BY ct.revenue DESC;

When to use it

  • Make an inherited reporting query reviewable without changing its results.
  • Collapse a correlated subquery repeated in several WHERE conditions into one grouped step.
  • Give a business rule a single named definition inside a query.
  • Split a query before adding to it, so the new logic has somewhere to go.

More examples

Prove the plan did not change

This is the discipline that makes the refactor safe to do on a production query. A rename that changes the plan is not a rename, and EXPLAIN FORMAT=TREE is compact enough to diff line by line.

Example · sql
-- Capture before and after, then diff the two outputs.
EXPLAIN FORMAT=TREE
SELECT c.email FROM customers c
JOIN (SELECT customer_id FROM orders WHERE status = 'paid') AS x
  ON x.customer_id = c.id;

EXPLAIN FORMAT=TREE
WITH paid AS (SELECT customer_id FROM orders WHERE status = 'paid')
SELECT c.email FROM customers c JOIN paid ON paid.customer_id = c.id;

-- If the CTE version shows a materialized step the derived-table version
-- did not, something in the lift blocked merging -- look for LIMIT,
-- DISTINCT, GROUP BY, HAVING, UNION or a subquery in the select list.

Replacing a repeated correlated subquery

Here the refactor is deliberately not plan-neutral, and that is the point: two correlated scans of `reviews` become one grouped scan. Reach for this when the same correlated subquery appears more than once with only the aggregate changed.

Example · sql
-- BEFORE: the same correlated subquery twice, evaluated per outer row.
SELECT p.id, p.name
FROM products AS p
WHERE (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) >= 10
  AND (SELECT AVG(r.rating) FROM reviews r WHERE r.product_id = p.id) >= 4.0;

-- AFTER: one grouped pass, both conditions read from it.
WITH review_stats AS (
    SELECT product_id,
           COUNT(*)    AS review_count,
           AVG(rating) AS avg_rating
    FROM reviews
    GROUP BY product_id
)
SELECT p.id, p.name
FROM products AS p
JOIN review_stats AS rs ON rs.product_id = p.id
WHERE rs.review_count >= 10
  AND rs.avg_rating   >= 4.0;

Naming a step for a reviewer, not for the optimizer

A CTE name is documentation the database enforces: the business rule appears once, under a name, instead of being restated in three reports. This is worth doing even when the CTE is used only once and merges away to nothing.

Example · sql
WITH
  -- Rule: a trial counts as converted only if the first paid invoice
  -- lands within 30 days of signup. Written once, named once.
  converted_trials AS (
      SELECT t.account_id, MIN(i.paid_at) AS converted_at
      FROM trials AS t
      JOIN invoices AS i
        ON i.account_id = t.account_id
       AND i.paid_at < t.started_at + INTERVAL 30 DAY
      GROUP BY t.account_id
  )
SELECT DATE_FORMAT(converted_at, '%Y-%m') AS month,
       COUNT(*) AS conversions
FROM converted_trials
GROUP BY month
ORDER BY month;

Related

sql withsql multiple ctessql cte materializationsql derived tablessql explain

Discussion

  • Be the first to comment on this lesson.