Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 15 down vote favorite
4

Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier than writing a loop or recursive function to run the query for each result. Thanks

EDIT: Since some people seem to be confused by the phrase "tree data" I will explain further. What I mean is with regards to tables which have a "parent_id" or similar field which contains a primary key from another row in the same table. The question comes from an experience where I was working with data stored in this way in an Oracle database and knew that the 'connect by' isn't implemented in other DBMSs. If one were to use standard SQL, one would have to create a new table alias for each parent one would want to go up. This could easily get out of hand.

share|improve this question
2  
What do you mean traverse tree like data? Do you want to process each row in sequential order? Do you mean a binary tree? Do you mean you want to join by the leaves? I don't understand. – jcolebrand♦ Jan 5 '11 at 5:45
Sybase doesn't seem to have any built-in syntax for this – Jack Douglas♦ Aug 4 '11 at 18:37
You have Joe Celko's solutions. Few samples: Trees in SQL, Trees and Hierarchies in Oracle, Nested set model. It's not necessary to have syntax sugar ;-). – Marian Aug 4 '11 at 22:08

3 Answers

up vote 15 down vote accepted

Celko's book is a good resource - if a bit overly "academic" at times. I also have really found this method, known as 'closure tables' to work fairly well. But if you're using a database that allows recursive CTE's (such as recent PostgreSQL or SQL Server 2005 or newer), they're really the best way to go. If you're on Oracle, there's always the venerable "connect by".

share|improve this answer
1  
One problem with recursive CTEs is that they can cause a lot of reads from disk. It's important to take a look at other options available if read performance is your biggest concern. – Jeremiah Peschka Jan 15 '11 at 14:28
1  
"recent PostgreSQL" can be read as "version 8.4 or newer". CTE's work great in PostgreSQL and indexes can be used to speed things up. – Frank Heikens Jan 19 '11 at 8:36
Great point, Jeremiah – TML Jan 20 '11 at 1:19
I will definitely check that book out some day. – indyK1ng Jan 28 '11 at 2:23
up vote 10 down vote

A recursive CTE is going to be your easiest solution. SQL 2005 and current versions of PostgreSQL support CTEs. If you're using SQL Server 2008 or newer, you could use the HIERARCHYID datatype. You can find a good example of this at HierarchyID: Model Your Data Hierarchies with SQL Server 2008

share|improve this answer
I agree with your conclusion - however, I haven't seen a lot of good information on using HIERARCHYID - if you could improve this answer a bit by digging up a relevant link, I'd gladly +1 this answer. – TML Jan 5 '11 at 6:56
1  
up vote 6 down vote

In MSSQL (2005 and later editions) you can use Common Table Expressions for reading hierarchies, see this MS blog post for a couple of examples.

I have been recommended a book on the subject more generally which is "Trees and Hierarchies in SQL for Smarties" by Joe Celko - though I've not actually looked at the book myself yet.

share|improve this answer
I've read Joe Celko's book and liked it. – AlexKuznetsov May 18 '12 at 20:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.