How to Create a SQLite Hierarchical Recursive Query?

SQLite is a powerful database management system that supports hierarchical recursive queries. These queries are useful for working with hierarchical data structures like organizational charts, file systems, and nested categories.

In this article, we will explore how to create hierarchical recursive queries, allowing us to efficiently write query and manipulate hierarchical data in our applications.

How to Create Hierarchical Recursive Query in SQLite?

Hierarchical data structures often require complex queries to retrieve and manipulate data. SQLite provides several approaches to handle hierarchical data efficiently Below are the approaches that help us to create a hierarchical recursive query SQLite as follows:

1. Recursive Query using UNION ALL
2. Using JOIN and LEVEL
3. Using Path Concatenation

Let’s set up an environment

To understand How to create a SQLite hierarchical recursive query we need a table on which we will perform various operations and queries. Here we will consider a table called Categories which contains CategoryID, ParentCategoryID, and CategoryName as Columns.

-- Create the Categories table
CREATE TABLE Categories (
    CategoryID INTEGER PRIMARY KEY,
    ParentCategoryID INTEGER,
    CategoryName TEXT
);

-- Insert sample data
INSERT INTO Categories (CategoryID, ParentCategoryID, CategoryName) VALUES 
    (1, NULL, 'Electronics'),
    (2, 1, 'Mobile Phones'),
    (3, 1, 'Laptops'),
    (4, 2, 'Smartphones'),
    (5, 2, 'Feature Phones'),
    (6, 3, 'Gaming Laptops'),
    (7, 3, 'Business Laptops');

Our Table Looks like:

Table

1. Recursive Query using UNION ALL

In this approach, a Common Table Expression (CTE) named OrgHierarchy is created with a recursive part using UNION ALL. The initial query selects top–level nodes where ParentNodeID is NULL, and the recursive part continues to select child nodes by joining the CTE with the original table using the relationship between NodeID and ParentNodeID. This method efficiently retrieves a hierarchical structure.

WITH RECURSIVE CategoryHierarchy AS (
    SELECT CategoryID, ParentCategoryID, CategoryName
    FROM Categories
    WHERE ParentCategoryID IS NULL
    
    UNION ALL
    
    SELECT c.CategoryID, c.ParentCategoryID, c.CategoryName
    FROM Categories c
    JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID
)

SELECT * FROM CategoryHierarchy;

Output:

Recursive Query using UNION ALL output

2. Using JOIN and LEVEL

This approach utilizes the WITH RECURSIVE clause and a CTE named OrgHierarchy. It introduces a Level column to keep track of the depth in the hierarchy. The anchor member selects top-level nodes, and the recursive member continues to join the CTE with the original table, updating the level as it traverses the hierarchy.

This technique provides a straightforward way to determine the depth of each node in the hierarchy.

WITH RECURSIVE CategoryHierarchy AS (
    SELECT CategoryID, ParentCategoryID, CategoryName, 0 AS Level
    FROM Categories
    WHERE ParentCategoryID IS NULL
    
    UNION ALL
    
    SELECT c.CategoryID, c.ParentCategoryID, c.CategoryName, ch.Level + 1
    FROM Categories c
    JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID
)

SELECT * FROM CategoryHierarchy;

Output:

Using JOIN and LEVEL output

3. Using Path Concatenation

In this approach, the OrgHierarchy CTE includes a column named Path, where the path to each node is represented as a concatenated string. The anchor member selects top-level nodes, and the recursive member appends each child node’s NodeID to the path of its parent. This method is useful when you need to track the path or lineage of each node in the hierarchy.

WITH RECURSIVE CategoryHierarchy AS (
    SELECT CategoryID, ParentCategoryID, CategoryName, CAST(CategoryID AS TEXT) AS Path
    FROM Categories
    WHERE ParentCategoryID IS NULL
    
    UNION ALL
    
    SELECT c.CategoryID, c.ParentCategoryID, c.CategoryName, ch.Path || '->' || c.CategoryID
    FROM Categories c
    JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID
)

SELECT * FROM CategoryHierarchy;

Output:

Using Path Concatenation

Conclusion

Overall, the implementation of hierarchical recursive queries using the WITH RECURSIVE clause and Common Table Expressions (CTEs) provides a powerful tool for traversing tree-like structures within tables, allowing efficient retrieval of hierarchical data.

The presented approaches, including recursive queries with UNION ALL, JOIN and LEVEL, and Path Concatenation, offer flexibility in managing hierarchical relationships in SQLite databases.