Recursive Queries in SQL

SQL's powerful feature of recursive queries allows you to work with hierarchical data.

Examples of hierarchical data are threaded comments, organizational structures, or other nested relationships.

Setting Up the Database Schema

First, we need to create a table to hold our comments. Each comment can either be a standalone comment or a reply to another comment. To represent this, we use a `ParentId` column that refers back to the `Id` of the comment it replies to.

CREATE TABLE Comment (
  Id INT PRIMARY KEY,
  Content TEXT,
  ParentId INT
);

The `Comment` table has three columns:

Inserting Sample Data

Next, we insert some sample data into the `Comment` table. This data represents a threaded comment structure, where some comments are replies to others:

INSERT INTO Comment (Id, Content, ParentId) VALUES (0, 'Python', NULL);
INSERT INTO Comment (Id, Content, ParentId) VALUES (1, 'Hello', NULL);
INSERT INTO Comment (Id, Content, ParentId) VALUES (2, 'World', 1);
INSERT INTO Comment (Id, Content, ParentId) VALUES (3, 'Hi', 2);
INSERT INTO Comment (Id, Content, ParentId) VALUES (4, 'Sql', NULL);
INSERT INTO Comment (Id, Content, ParentId) VALUES (5, 'Recursive', 4);

This data creates a comment structure like the following:

Using Recursive CTEs to Query Hierarchical Data

Now, let’s write a query to retrieve and display this hierarchical comment structure. We’ll use a Common Table Expression (CTE) with recursion:

WITH RECURSIVE RecursiveComment AS (
    SELECT Id, Content, ParentId, 1 AS Level
    FROM Comment
    WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.Content, c.ParentId, rc.Level + 1
    FROM Comment c
    INNER JOIN RecursiveComment rc ON c.ParentId = rc.Id
)
SELECT * FROM RecursiveComment ORDER BY Level, ParentId;

Let’s break this down:

Output

Id Content ParentId Level
0 Python null 1
1 Hello null 1
4 Sql null 1
2 World 1 2
5 Recursive 4 2
3 Hi 2 3