select all leaf nodes in sql tree

Given a table (simplified):

CREATE TABLE tree ( id INTEGER NOT NULL, node_id INTEGER, parent INTEGER ) 

Where node_id is id of the node, and parent is id of parent node. Root node has id=0.

How to find all "leaf" nodes, i.e. all nodes that is not a parent for any else node?

2 Answers

Try a LEFT self JOIN and check for NULL values:

SELECT leaf.node_id FROM tree AS leaf LEFT OUTER JOIN tree AS child on child.parent = leaf.node_id WHERE child.node_id IS NULL 

I would use not exists:

select t.* from t where not exists (select 1 from tree t2 where t2.parent = t.node_id); 

This is pretty equivalent to the left join method. I just think that not exists is clearer about the intent of the query.