SQL Antipatterns, Volume 1

Solution: Use Alternative Tree Models

You can use any one of the following solutions to work with hierarchical data in SQL. Each has strengths and weaknesses, and any of them might be the right choice for a given application.

Recursive Queries

Some brands of RDBMS implement SQL syntax features to support hierarchies stored in the Adjacency List format. The SQL-99 standard defines recursive query syntax using the WITH keyword followed by a recursive common table expression.

Trees/soln/cte.sql

​WITH​ RECURSIVE CommentTree
    (comment_id, bug_id, parent_id, author, comment_date, ​comment​, depth)
​AS​ (
    ​SELECT​ comment_id, bug_id, parent_id, author, comment_date,
      ​comment​, 0 ​AS​ depth
    ​FROM ...