How to Create a SQLite Hierarchical Recursive Query?
Last Updated :
12 Mar, 2024
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:
- Recursive Query using UNION ALL
- Using JOIN and LEVEL
- 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.
Share your thoughts in the comments
Please Login to comment...