3 solutions (NOT EXISTS, NOT IN, LEFT JOIN)
Code
# Code
```
-- First solution : NOT EXIST 

select id, 
(case when T1.p_id is null then 'Root'
when not exists(select * from Tree T2 where T2.p_id = T1.id) then 'Leaf'
else 'Inner'
end
) as type from Tree T1

-- Second solution: NOT IN
--  Note: ensure the table selected in 'not in' must not have null values or it would automatically return false
select T1.id, 
(case when T1.p_id is null then 'Root'
when T1.id not in (select T2.p_id from Tree T2 where T2.p_id is not null) then 'Leaf'
else 'Inner'
end
) as type from Tree T1

-- Third solution: LEFT JOIN

select distinct T1.id, (
    case
    when T1.p_id is null then 'Root'
    when T2.id is not null then 'Inner'
    else 'Leaf'
    end
) as type
from Tree T1
left join Tree T2
on T1.id = T2.p_id
```
['MS SQL Server']