SQL Recursive Queries - Solve Hierarchies and Sequences with SQL

Introduction

Imagine you want to generate a sequence of roll numbers, calculate cumulative totals, or traverse a hierarchy like class monitors → captains → principal. You could use loops in programming — but in SQL, you can use recursive queries. These powerful tools let your query call itself, opening doors to solving problems that are inherently stepwise or hierarchical.

What Are Recursive Queries?

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.

Basic Syntax

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;

Example 1 – Generate Roll Numbers 1 to 5

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

Example 2 – Class Hierarchy (School Leaders)

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

How It Works

• 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

Use Cases in a School System

• 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)

Things to Watch Out For

• ❗ 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+

Summary

Recursive queries are SQL’s way of thinking step-by-step. Whether you're generating data sequences, traversing hierarchies, or building dynamic relationships in school data, recursive CTEs give your SQL logic a structured, powerful toolset — all without writing loops or procedures.

What’s Next?

Up next: SQL Interview Questions — test your understanding with real-world problems and their solutions.