go to content go to search box go to global site navigation

Tech Blog

Recursive SQL Queries

Sometimes data in a DB table needs to point to parent or child data. The result is a hierarchy. Here's an example of how folders on a file system might be represented:

idparent_idname
1 Top
21Child 1
31Child 2
42Grand child
5 Other Top

In the example, Top and Other Top are in the root (/ or C:\, depending whether you're from Unix world or Windows land).

So, if I want to get all of the children and grandchildren of "Top" I can use a common table expression. This acts like a recursive query (technically it's iterative, not recursive - see the "how it works" part later for details).

A few parts are involved (jump to see the whole thing first if you prefer then come back here for the parts. Go on, I'll wait...):

  • A definition for the recursive query
    WITH RECURSIVE foldertree(id, parentid, name) AS
  • A starting point. In our case we want to begin at id=1;
    SELECT id, parent_id, name
    FROM folder
    WHERE id = 1
  • A UNION to add rows from the following query...
  • A query that finds the child rows we want, based on what we found last time. In the first iteration, what we found last time will be the row(s) from the starting point. In second iteration, what we found last time will be the things from the first iteration, etc.
    SELECT folder.id, folder.parentid, folder.name
    FROM folder, foldertree
    WHERE folder.parentid = foldertree.id
    The results from each iteration are also set aside to be returned as results via the final part as follows...
  • Finally, something that uses the WITH query to return results. In this case, all of them will do:
    SELECT * FROM folder_tree

The whole query looks like this (back to the parts):

WITH RECURSIVE folder_tree(id, parent_id, name) AS (
  SELECT id, parent_id, name
  FROM folder
  WHERE id = 1
  UNION
  SELECT folder.id, folder.parent_id, folder.name
  FROM folder, folder_tree
  WHERE folder.parent_id = folder_tree.id
)
SELECT * FROM folder_tree

The results include everything except the "Other Top" row:

idparent_idname
1 Top
31Child 2
21Child 1
42Grand child

We can also build a path as we go, to show how we got to a given folder:

WITH RECURSIVE folder_tree(id, parent_id, name) AS (
  SELECT id, parent_id, name, name AS path
  FROM folder
  WHERE id = 1
  UNION
  SELECT folder.id, folder.parent_id, folder.name, folder_tree.path || '/' || folder.name as path
  FROM folder, folder_tree
  WHERE folder.parent_id = folder_tree.id
)
SELECT * FROM folder_tree
idparent_idnamepath
1 TopTop
31Child 2Top/Child 2
21Child 1Top/Child 1
42Grand childTop/Child 1/Grand child

How it works

How recursive SQL works

Published