SQL Recursive Queries — Solve Hierarchies and Sequences with SQL

A school leader hierarchy has several levels. One self join reaches only one relationship level, while the report needs the full chain.

A recursive query starts from anchor rows and repeatedly produces new rows from the previous stage until no more permitted rows are produced or an engine limit stops it.

A recursive CTE has a non-recursive anchor and a recursive member. The recursive member references the evolving CTE result and should express progress toward a finite boundary. For number generation, a condition such as n < 5 allows the next number only while the current number is below five.

Related concepts: Join a Table to Itself and Common Table Expressions.

A recursive query is a query that refers to itself. It’s most commonly written using a Recursive Common Table Expression (CTE). It starts with a base query, and then repeatedly applies a rule to build new rows from the previous result.

SQL · portable concepts; preserved examples identify their engine

WITH RECURSIVE cte_name AS (
  -- Anchor query (base case)
  SELECT ...

  UNION ALL

  -- Recursive query (refers to cte_name)
  SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

SQL · portable concepts; preserved examples identify their engine

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

roll_no
--------
1
2
3
4
5

SQL · portable concepts; preserved examples identify their engine

Let’s define a simple leadership structure:

CREATE TABLE school_leaders (
  name VARCHAR(50),
  reports_to VARCHAR(50)
);

INSERT INTO school_leaders VALUES
('Head Boy', 'Vice Principal'),
('Class Monitor', 'Head Boy'),
('Sports Captain', 'Head Boy'),
('Vice Principal', 'Principal'),
('Principal', NULL);

Find the reporting chain for 'Class Monitor':

WITH RECURSIVE leadership_chain AS (
  SELECT name, reports_to
  FROM school_leaders
  WHERE name = 'Class Monitor'

  UNION ALL

  SELECT sl.name, sl.reports_to
  FROM school_leaders sl
  JOIN leadership_chain lc ON sl.name = lc.reports_to
)
SELECT * FROM leadership_chain;

name             | reports_to
------------------+------------
Class Monitor     | Head Boy
Head Boy          | Vice Principal
Vice Principal    | Principal
Principal         | NULL

• Anchor member: Starts with 'Class Monitor'
• Recursive step: Joins to find who 'Class Monitor' reports to, then who that person reports to, and so on
• Stops when: reports_to is NULL

• Generating roll number or student ID sequences
• 🏫 Traversing administrative hierarchies (like teacher → HOD → Principal)
• Cumulative GPA or progressive mark calculation
• 📚 Tracing syllabus dependencies (e.g., Algebra depends on Arithmetic)

• ❗ You must include a UNION ALL — NOT just UNION
• ❗ Termination condition is critical — else it loops infinitely
• Use LIMIT or WHERE filters to restrict depth
• Not supported in all DBs — works in PostgreSQL, MySQL 8+, SQL Server (different syntax), Oracle 11g+

Hierarchy data may contain cycles, so depth limits and visited-path checks need deliberate design. UNION and UNION ALL have different duplicate behavior. Recursive syntax, limits and supported operations vary by database engine.

Generate roll-number candidates 1 through 5 and trace why no sixth row appears.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

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

The anchor produces one. Each stage adds one while the current value is less than five. The row five fails that condition, so it does not create six.

A recursive query starts from anchor rows and repeatedly produces new rows from the previous stage until no more permitted rows are produced or an engine limit stops it.

The anchor produces one. Each stage adds one while the current value is less than five. The row five fails that condition, so it does not create six.

Continue your understanding with Join a Table to Itself and Common Table Expressions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.