create table tree(id integer primary key,
p_id integer);

insert into tree(id, p_id)
values(1,null),
       (2,1),
       (3,1),
       (4,2),
       (5,2);
       
Select id,
case when p_id is null then 'Root'
when p_id is not null and id in (select p_id from tree) then 'Inner'
else 'Leaf'
end as type
from tree