Tree structures in SQL


Suppose you have a table called 'tree' that contains a column of nodes as well as corresponding parent nodes:

Table: tree

node parent
1 2
2 5
3 5
4 3
5 null

Write a SQL query that will label each node as a 'root' (no parent node), 'leaf' (no child nodes), or 'inner' (contains both parent and child nodes). Given the table above, your query should return the following:

node label
2 Inner
5 Root
3 Inner
1 Leaf
4 Leaf

You can view/query this table in an interactive SQL fiddle here.


Access restricted

Subscribe to premium account to see the solution.

Get premium now