Reddit mentions of Joe Celko's Trees and Hierarchies in SQL for Smarties (The Morgan Kaufmann Series in Data Management Systems)

How much control do you have over the database and any associated applications? I can think of three main ways of tackling this:

1. SQL Server 2008 supports the hierarchy data type, which is a fast way of doing these sorts of queries. This could involve a fair bit of rewrite, however.
   
2. Pre-materialise the data - essentially, you create a copy of the data (for example, using hierarchy, or a more "flat" structure), and keep this in synch with the original hierarchy.
   
3. Have a read of Joe Celko's Trees and Hierarchies in SQL for Smarties (wow, second edition just released in February!)
   
   3 will give you some ideas of the best way to queries these without changing the schema, whereas 2 is probably the quickest short-term fix (especially if you only need to update the hierarchy once per day).

Unless, of course, you need to join a whole lot of things in complex patterns. Then you should check graph databases. I used to work as a MS SQL Server DBA, and I clearly remember the problems we had with hierarchical data. There's even books describing the patterns you can use to work around the limitations of SQL.

(Disclaimer: I work as a dev on the Neo4j team)

This has been discussed so often one could write a book about it.

You never "need" to use a graph database. Relational databases are capable of storing graph data in them. The real question is which is more advantageous for your use case. Since you haven't specified anything about your use case it's hard to give any advice, but here are some things to think about:

1. Graph databases are often not ACID compliant.
   
2. Graph databases are not as well supported and generally have smaller communities, they aren't bad communities mind you, just smaller.
   
3. Graph databases often have less tooling. (UI, backup tools, Replication Tools.)
   
4. Graph databases don't have built in support in Cloud Environments, so you would have to manage your own boxes.
   
5. Graph databases are good at Graphs, but can't do normal relational stuff well or at all.
   
   If you have a graph with millions or billions of nodes in it, a Graph database will be faster doing most "graph" things, however if you have mixed use data. where you want to treat it like a graph sometimes and just rows of data other times you will have a hard time with a graph database.
   
   There are many great ways to do graphs in an RDBMS. The book by Joe Selko https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/0123877334 is probably the best book on the subject, it covers all the different ways you can store a graph in a SQL database. Thanks to Postgres Recursive CTEs usually an adjacency table is the easiest and best option but sometimes you will want something else for a specific use case. Also for very simple trees you can often use the ltree extension. Ltree stores the path through the tree to the node in the label and has fast search and other functions thanks to Postgres' GIST indexes.
   
   One last thing, If you are implementing graphs in Postgres and your recursive CTE's are slowing down one thing you can do to really speed things up is build a materialized view of your graph with the data denormalized.

>This has been discussed so often one could write a book about it.

Yup, highly recommended

I would recommend Joe Celko's Trees and Hierarchies in SQL for Smarties. All the sql that you can ever want! Has great problems and solutions.

Storing a tree structure (eg.. parent -> children -> grandchildren) in a relational database is tricky.

The book Joe Celko's Trees and Hierarchies in SQL for Smarties contains a variety of detailed solutions (plus good discussions of pros and cons of each).

Not BigQuery specific, but this is the book on hierarchical querying in SQL:

https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/0123877334