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.
