Solution with explanation: CASE + LEFT JOIN
 \n\n\nSELECT DISTINCT t1.id, (\n    CASE\n    WHEN t1.p_id IS NULL  THEN 'Root'\n    WHEN t1.p_id IS NOT NULL AND t2.id IS NOT NULL THEN 'Inner'\n    WHEN t1.p
![0_1499271537059_2c061b42-ed70-421a-8bd2-88f20a257d6d-image.png](/assets/uploads/files/1499271536894-2c061b42-ed70-421a-8bd2-88f20a257d6d-image.png) \n\n````\nSELECT DISTINCT t1.id, (\n    CASE\n    WHEN t1.p_id IS NULL  THEN 'Root'\n    WHEN t1.p_id IS NOT NULL AND t2.id IS NOT NULL THEN 'Inner'\n    WHEN t1.p_id IS NOT NULL AND t2.id IS NULL THEN 'Leaf'\n    END\n) AS Type \nFROM tree t1\nLEFT JOIN tree t2\nON t1.id = t2.p_id\n````
[]