Solution with CASE without any JOIN
\nselect id,\ncase\n    when p_id is null then \'Root\'\n    when id not in (select distinct p_id from Tree where p_id is not null) then \'Leaf\'\n    else \'In
```\nselect id,\ncase\n    when p_id is null then \'Root\'\n    when id not in (select distinct p_id from Tree where p_id is not null) then \'Leaf\'\n    else \'Inner\'\nend as type\nfrom Tree\norder by id\n```\nSo here I am just trying to select:-\n1. All points where there is no parent as \'Root\' \n2. Points whose id is not there in the parent id column, which means they are not parent to any other point as \'Leaf\'\n3. Rest of the points must be \'Inner\' \n\nSince all the inputs are valid trees, not having any other conditions
['MySQL']