Simple MySQL solution using case when (No Union, No join)
\nselect id,\n       case when p_id is null then \'Root\'\n       when p_id is not null and id in (select p_id from tree) then \'Inner\'\n       else \'Leaf\'\n
```\nselect id,\n       case when p_id is null then \'Root\'\n       when p_id is not null and id in (select p_id from tree) then \'Inner\'\n       else \'Leaf\'\n       end as Type\nfrom tree\norder by id\n\n```
[]