Queries

Traverse Hierarchical Data with Recursive SQL CTEs

Hierarchical data appears everywhere: employees report to managers, comments reply to other comments, folders contain folders, and categories form parent-child trees. The table structure is usually simple. The query is the hard part. A normal join follows a fixed number of relationships. A recursive common table expression, or recursive CTE, can follow the same relationship repeatedly until there are no more rows to visit. The key mental model is: start with an anchor set, repeatedly derive the next set from the previous one, then return the accumulated rows.