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:

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:

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

How it works