Recursive CTEs

Every query you have written so far shares one quiet limitation: it can only return rows that already exist in a table. Ask the shop database “which days in July had no orders?” and you hit a wall, because the quiet days are exactly the ones no table mentions. Recursive CTEs remove that wall. They let a query build its own rows, one from the last, like a calendar, a number series, or a chain of steps, and then treat those generated rows like any other table.

The Recursive Shape: Anchor, UNION ALL, Recursive Step

A recursive CTE starts with WITH RECURSIVE and always has the same three-part body: an anchor query that produces the starting row, UNION ALL, and a recursive step that selects from the CTE itself to produce the next row from the previous one. The step keeps running on each new row it makes, until its WHERE clause stops matching. Here is the classic first example, counting from 1 to 10:

Before you run it, find the three parts: which line is the anchor, which is the recursive step, and what finally makes it stop?

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

n
1
2
3
4
5
6
7
8
9
10

Walk it through. The anchor SELECT 1 runs exactly once and produces the row 1. Then the recursive step takes that row: is 1 < 10? Yes, so it produces 2. It takes 2 and produces 3, and so on. When the step picks up the row 10, the check 10 < 10 fails, no new row is produced, and the recursion ends. The WHERE n < 10 is the termination bound, and it is not optional decoration: every recursive query you write must carry an explicit bound like this in the recursive step.

Bounds are also where off-by-one mistakes hide. The bound is checked against the row you already have, before the new one is built:

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

Generating a Calendar

Counting integers is the warm-up. The same shape generates dates: anchor on a starting date, and let the recursive step add one day at a time with date(day, '+1 day'). Here is the first week of July 2024, seven rows that exist in no table:

WITH RECURSIVE week(day) AS (
  SELECT '2024-07-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM week WHERE day < '2024-07-07'
)
SELECT day FROM week;

day
2024-07-01
2024-07-02
2024-07-03
2024-07-04
2024-07-05
2024-07-06
2024-07-07

The bound works because dates in this database are TEXT in YYYY-MM-DD form, so day < '2024-07-07' compares them correctly. Stretch the bound to '2024-07-31' and you have the whole month. On its own that is a neat trick. Joined to a real table, it answers questions nothing else can.

The Days Nothing Happened

The shop took orders on 8 days in July 2024. A plain query can list those 8 days, but it can never list the quiet ones, because a JOIN, a WHERE, a GROUP BY can only work with rows that exist. Now you can manufacture the missing rows: generate all 31 July days, LEFT JOIN orders onto them, and keep the days where nothing matched.

Notice which side of the LEFT JOIN the generated calendar is on, and why the WHERE looks for a NULL id.

WITH RECURSIVE july(day) AS (
  SELECT '2024-07-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM july WHERE day < '2024-07-31'
)
SELECT july.day
FROM july
LEFT JOIN orders ON orders.order_date = july.day
WHERE orders.id IS NULL;

day
2024-07-04
2024-07-05
2024-07-06
2024-07-07
2024-07-09
2024-07-11
2024-07-13
2024-07-14
2024-07-16
2024-07-17
2024-07-19
2024-07-20
2024-07-21
2024-07-22
2024-07-23
2024-07-24
2024-07-25
2024-07-26
2024-07-27
2024-07-28
2024-07-29
2024-07-30
2024-07-31

The generated calendar is the left table, so every one of the 31 days survives the join. Days with an order get real order columns; days without one get NULLs, and WHERE orders.id IS NULL keeps exactly those. This is the same unmatched-rows pattern you used to find the never-ordered Wool Beanie, except this time the left table came from nowhere. Explore it:

WITH RECURSIVE july(day) AS (
  SELECT '2024-07-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM july WHERE day < '2024-07-31'
)
SELECT COUNT(*) AS quiet_days
FROM july
LEFT JOIN orders ON orders.order_date = july.day
WHERE orders.id IS NULL;

quiet_days
23

WITH RECURSIVE july(day) AS (
  SELECT '2024-07-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM july WHERE day < '2024-07-31'
)
SELECT july.day
FROM july
LEFT JOIN orders ON orders.order_date = july.day
WHERE orders.id IS NULL
ORDER BY july.day
LIMIT 1;

day
2024-07-04

WITH RECURSIVE july(day) AS (
  SELECT '2024-07-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM july WHERE day < '2024-07-31'
)
SELECT july.day, strftime('%w', july.day) AS weekday
FROM july
LEFT JOIN orders ON orders.order_date = july.day
WHERE orders.id IS NULL
  AND strftime('%w', july.day) IN ('0', '6');

day | weekday
2024-07-06 | 6
2024-07-07 | 0
2024-07-13 | 6
2024-07-14 | 0
2024-07-20 | 6
2024-07-21 | 0
2024-07-27 | 6
2024-07-28 | 0

The last chip tells a small story: every single weekend day in July was quiet. All 8 of the shop’s July orders landed on weekdays. That insight was invisible until you generated the rows the data never recorded.

Practice: Your Own Series

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