MySQL / Reliable applications and analytical queries / Lesson 10

Views, CTEs, and recursive reasoning

Name intermediate relations without assuming they are cached tables or automatic optimization barriers.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Choose a view, derived table, or CTE for the required lifetime.
Write terminating recursive CTEs and understand security context.

Give a relation a useful name

Use the original shop rows. A derived table names a subquery inside FROM; a common table expression names a relation for one statement; a view stores a query definition for reuse across statements. These tools organize reasoning and expose a stable projection. None inherently stores a permanent copy of its result. A readable name should describe the fact and grain, such as line_totals per order.

Separate detail aggregation from comparison

A nonrecursive CTE is useful when the report has several conceptual stages. First calculate one subtotal per order, then join to the stored order summary. MySQL may merge or materialize intermediate relations according to optimizer rules. Reusing a CTE name does not guarantee a particular execution cost. Inspect EXPLAIN when performance matters rather than equating visual query stages with physical execution stages.

sql Reconcile stored and computed amounts

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price) AS computed_total
 FROM order_items GROUP BY order_id
)
SELECT o.id,o.total,l.computed_total
FROM orders o JOIN line_totals l ON l.order_id=o.id
ORDER BY o.id;

Expected output

id | total | computed_total
101 | 25.00 | 25.00
102 | 30.00 | 30.00
103 | 10.00 | 10.00

The CTE has one row per order, preventing line multiplication. All three fixture orders reconcile: 101 has 25.00, 102 has 30.00, and 103 has 10.00. An inner join alone would hide missing line groups; a full audit should use a left join and explicitly test missing totals.

Publish a narrow reusable interface

A view can expose only order id, customer id, and total for paid orders. Prefer explicit columns over SELECT * so a schema addition does not unexpectedly widen the interface. Aggregated or otherwise complex views may not be updatable. Review SQL SECURITY DEFINER versus INVOKER: default definer behavior can expose privileges through the stored object. A view is a deliberate permission boundary only when its definition and grants are designed accordingly.

Define an anchor and a stopping rule

A recursive CTE combines an anchor with a recursive member. The anchor determines column types, so widen a string there if later iterations append text. The recursive member must make progress toward a finite stop. MySQL enforces a recursion-depth limit, but that limit is a safety boundary rather than a correct termination argument. Cyclic business graphs require explicit cycle handling, not just a larger limit.

sql A fully bounded recursive relation

WITH RECURSIVE steps(n) AS (
 SELECT 1
 UNION ALL
 SELECT n+1 FROM steps WHERE n<3
)
SELECT n FROM steps ORDER BY n;

Expected output

The anchor produces 1, the next iteration 2, then 3. At 3 the predicate fails, so no further row is generated. ORDER BY determines display order independently of how recursion is evaluated. This miniature sequence teaches termination without assuming a hierarchy absent from the shop schema.

Respect scope and portability

The CTE name exists only for its statement; a later SELECT needs a new WITH clause or a real view. MySQL 8.4 supports recursive CTEs but does not promise every syntax available in another database. MariaDB has its own recursion restrictions and features, so verify rather than copying vendor-specific clauses. Keep version notes with reusable queries and test the exact target before packaging migrations.

Common errors and fixes

ERROR 1146 (42S02) reading a previous CTE name.

Why it happens: The new statement has no WITH definition in scope.
How to fix it: Repeat the CTE or create a reviewed reusable view.
Prevent it next time: Choose relation lifetime explicitly instead of treating CTEs as temporary tables.

ERROR 3636 (HY000) during recursion.

Why it happens: The recursive query exceeded its configured recursion depth.
How to fix it: Repair the stopping condition or cycle detection before considering a larger limit.
Prevent it next time: Prove progress and bound expected depth using representative graphs.

ERROR 1406 (22001) in a recursive string path.

Why it happens: The anchor inferred a string too narrow for appended path values.
How to fix it: CAST the anchor to a justified wider CHAR type and retain a depth bound.
Prevent it next time: Review output type growth as carefully as recursion termination.

Check your understanding

1. Return only orders whose stored total differs from line totals. What should the fixture show?

Show worked solution

Add WHERE o.total <> l.computed_total to the first example. The fixture returns no rows because all three reconcile. For an audit that also detects absent lines, switch to LEFT JOIN and use WHERE l.order_id IS NULL OR o.total <> l.computed_total.

2. Extend the recursive sequence to five values and explain the stop.