quick solution with case and no join
\nselect id,\ncase when p_id is null then \'Root\'\n     when id in (select p_id from tree) then \'Inner\'\n     else \'Leaf\'\n     end as type\nfrom tree;\n
```\nselect id,\ncase when p_id is null then \'Root\'\n     when id in (select p_id from tree) then \'Inner\'\n     else \'Leaf\'\n     end as type\nfrom tree;\n```
[]