select t1.id, (
case
    when t1.p_id is null then "Root"
    when t1.id in (select p_id from tree) then "Inner"
    else "Leaf"
end
)as Type
from tree t1