Anchor and recursive parts

Learn the two parts joined by UNION ALL.

Anchor and recursive

The anchor is the starting point, the recursive part calls itself.

-- anchor: SELECT 1
-- recursive: SELECT n + 1 FROM countdown WHERE n < 5

Anchor is base, recursive is step.

The anchor gives a fixed result. The recursive part builds on the previous step. Results merge with UNION ALL.

Each round only processes the rows produced by the previous round; recursion stops on its own in the first round that produces no new rows.

Write a recursive CTE: anchor SELECT 1, recursive SELECT n+1 ... WHERE n < 5.

Show example solution

WITH RECURSIVE countdown(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;

Add UNION ALL

  SELECT 1
  ____
  SELECT n + 1 FROM countdown WHERE n < 5

Show answer

  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5