Command guide

advanced recursion MySQL MariaDB SQL Server PostgreSQL SQLite

Build A Path String While Traversing A Hierarchy

Accumulate a breadcrumb path string as a recursive CTE walks a parent-child tree.

Created Apr 26, 2026 Last updated Apr 26, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Generate the full path for each folder in a file system hierarchy

The anchor selects the root folder and seeds path with its name. Each recursive step finds folders whose parent_id matches a row already in paths and concatenates the parent's path, a slash, and the current folder's name. docs and images are both direct children of root, so their paths are root/docs and root/images. photos is a child of images, so its path becomes root/images/photos.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE folders (id INT, name VARCHAR(50), parent_id INT);

INSERT INTO
  folders
VALUES
  (1, 'root', NULL),
  (2, 'docs', 1),
  (3, 'images', 1),
  (4, 'photos', 3);

Validated query

SQL

WITH RECURSIVE
  paths AS (
    SELECT
      id,
      CAST(name AS CHAR(200)) AS path
    FROM
      folders
    WHERE
      parent_id IS NULL
    UNION ALL
    SELECT
      f.id,
      CONCAT (p.path, '/', f.name)
    FROM
      folders f
      JOIN paths p ON f.parent_id = p.id
  )
SELECT
  path
FROM
  paths
ORDER BY
  path;

Expected result

path
root
root/docs
root/images
root/images/photos

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE folders (id INT, name VARCHAR(50), parent_id INT);

INSERT INTO
  folders
VALUES
  (1, 'root', NULL),
  (2, 'docs', 1),
  (3, 'images', 1),
  (4, 'photos', 3);

Validated query

SQL

WITH
  paths AS (
    SELECT
      id,
      CAST(name AS VARCHAR(500)) AS path
    FROM
      folders
    WHERE
      parent_id IS NULL
    UNION ALL
    SELECT
      f.id,
      CAST(p.path + '/' + f.name AS VARCHAR(500))
    FROM
      folders f
      JOIN paths p ON f.parent_id = p.id
  )
SELECT
  path
FROM
  paths
ORDER BY
  path;

Expected result

path
root
root/docs
root/images
root/images/photos

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE folders (id INT, name VARCHAR(50), parent_id INT);

INSERT INTO
  folders
VALUES
  (1, 'root', NULL),
  (2, 'docs', 1),
  (3, 'images', 1),
  (4, 'photos', 3);

Validated query

SQL

WITH RECURSIVE paths AS (SELECT id, name::text AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, p.path || '/' || f.name FROM folders f JOIN paths p ON f.parent_id = p.id) SELECT path FROM paths ORDER BY path;

Expected result

path
root
root/docs
root/images
root/images/photos

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE folders (id INT, name VARCHAR(50), parent_id INT);

INSERT INTO
  folders
VALUES
  (1, 'root', NULL),
  (2, 'docs', 1),
  (3, 'images', 1),
  (4, 'photos', 3);

Validated query

SQL

WITH RECURSIVE
  paths AS (
    SELECT
      id,
      name AS path
    FROM
      folders
    WHERE
      parent_id IS NULL
    UNION ALL
    SELECT
      f.id,
      p.path || '/' || f.name
    FROM
      folders f
      JOIN paths p ON f.parent_id = p.id
  )
SELECT
  path
FROM
  paths
ORDER BY
  path;

Expected result

path
root
root/docs
root/images
root/images/photos

SQL Server omits RECURSIVE and uses + for string concatenation. PostgreSQL and SQLite use ||. MySQL and MariaDB use CONCAT with an explicit cast on the anchor column. All engines produce identical paths.

Useful when

Where this command helps.

displaying breadcrumb trails for a folder or category tree
generating full path identifiers for nodes in a hierarchical dataset

Explanation

What the command is doing.

A recursive CTE can carry computed values across levels, not just raw column data. The anchor member seeds the path with the root node's name. Each recursive step concatenates the parent's path with a separator and the current node's name. The result is a full path string for every node in the tree — useful for folder breadcrumbs, category hierarchies, and org chart paths. SQL Server omits the RECURSIVE keyword and uses + for string concatenation. SQLite and PostgreSQL use the || operator. MySQL and MariaDB use CONCAT(). To prevent silent truncation in MySQL, the anchor should cast the path column to a wide character type.

Build A Path String While Traversing A Hierarchy

Generate the full path for each folder in a file system hierarchy

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.