CTEs and Recursive Queries

Introduction

Deeply nested subqueries are hard to read, because you have to start from the middle and work outwards. A common table expression (CTE) lets you name a step, put it at the top, and then use it like a table. Your query reads from top to bottom, like a recipe.

Your first CTE

Here is the total value of each order, written as a named step, then used:

order_totals exists only for this one query. It is not stored in the database.

Chaining several CTEs

Separate steps with commas. Later steps can use earlier ones, so you can build an answer one idea at a time.

Compare this with writing it as one nested query. Each step now has a name that explains what it does.

Recursive CTEs

A recursive CTE refers to itself. It has two parts joined by UNION ALL: a starting row (the anchor), and a step that builds the next rows from the previous ones. It stops when the step produces nothing new.

Count from 1 to 10:

The WHERE n < 10 is what stops it. Remove it and the query would run forever (our editor stops it after five seconds).

Use it to make a calendar of dates, for example every day of a week:

Walking a hierarchy

Recursion shines for data that points to itself, like staff and their managers, or folders inside folders.

The anchor picks the top person, who has no manager. Each round finds the people who report to someone already found.

Try it yourself

Rewrite this nested query so the inner part becomes a CTE called big_lines. It lists order lines worth more than 20 in value.

Summary

• WITH name AS (query) gives a step a name so queries read top to bottom.
• Chain several CTEs with commas, and later ones can use earlier ones.
• WITH RECURSIVE has an anchor, a recursive step and a stopping condition.
• Recursion generates series and walks hierarchies such as org charts and folders.