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.
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:
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:
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:
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 |