Recursive CTE Example: Directory Structure

Managing and querying directory structures in SQL can be effectively accomplished using recursive Common Table Expressions (CTEs). Below is a detailed example that demonstrates how to use a recursive CTE to retrieve the full path of each directory in a hierarchical structure.

Directory Table Structure

Suppose we have a directories table with the following schema:

sql
CREATE TABLE directories (
    directory_id INT PRIMARY KEY,
    directory_name VARCHAR(100),
    parent_directory_id INT
);

Sample Data

Here's some sample data to work with:

sql
INSERT INTO directories (directory_id, directory_name, parent_directory_id) VALUES
(1, 'root', NULL),
(2, 'home', 1),
(3, 'user', 2),
(4, 'documents', 3),
(5, 'photos', 3),
(6, 'work', 1);

SQL Query to Retrieve Full Directory Paths

We want to construct a query to retrieve the full path for each directory. Here’s how to do it using a recursive CTE:

sql
WITH RECURSIVE DirectoryPaths AS (
    -- Anchor member: start with root directory
    SELECT 
        directory_id,
        directory_name,
        parent_directory_id,
        directory_name AS full_path
    FROM 
        directories
    WHERE 
        parent_directory_id IS NULL

    UNION ALL

    -- Recursive member: append current directory to its parent's path
    SELECT 
        d.directory_id,
        d.directory_name,
        d.parent_directory_id,
        CONCAT(dp.full_path, '/', d.directory_name) AS full_path
    FROM 
        directories d
    INNER JOIN 
        DirectoryPaths dp ON d.parent_directory_id = dp.directory_id
)
SELECT 
    directory_id,
    directory_name,
    parent_directory_id,
    full_path
FROM 
    DirectoryPaths
ORDER BY 
    full_path;

Explanation

1. Anchor Member:

   sql
   SELECT 
       directory_id,
       directory_name,
       parent_directory_id,
       directory_name AS full_path
   FROM 
       directories
   WHERE 
       parent_directory_id IS NULL
   
   

   This initializes the CTE with the root directory.

2. Recursive Member:

   sql
   SELECT 
       d.directory_id,
       d.directory_name,
       d.parent_directory_id,
       CONCAT(dp.full_path, '/', d.directory_name) AS full_path
   FROM 
       directories d
   INNER JOIN 
       DirectoryPaths dp ON d.parent_directory_id = dp.directory_id
   
   

   This part of the CTE appends each directory to its parent’s full path. It joins the directories table with the DirectoryPaths CTE on the parent_directory_id to build the full path.

3. Final Select:

   sql
   SELECT 
       directory_id,
       directory_name,
       parent_directory_id,
       full_path
   FROM 
       DirectoryPaths
   ORDER BY 
       full_path;

   This selects and orders the results by the full path.

Result

The result will be a table listing each directory with its full path:

 directory_id | directory_name | parent_directory_id |      full_path      
--------------|----------------|---------------------|---------------------
            1 | root           |                NULL | root
            2 | home           |                   1 | root/home
            3 | user           |                   2 | root/home/user
            4 | documents      |                   3 | root/home/user/documents
            5 | photos         |                   3 | root/home/user/photos
            6 | work           |                   1 | root/work

This query effectively builds the full directory paths by leveraging the power of recursive CTEs, making it easier to manage and query hierarchical data in SQL.