Mysql clean solution
SELECT id,\nCASE\n    WHEN p_id IS NULL THEN \'Root\'\n    WHEN p_id IS NOT NULL AND id IN (SELECT DISTINCT(p_id) FROM tree) THEN \'Inner\'\n    ELSE \'Leaf\'\n
SELECT id,\nCASE\n    WHEN p_id IS NULL THEN \'Root\'\n    WHEN p_id IS NOT NULL AND id IN (SELECT DISTINCT(p_id) FROM tree) THEN \'Inner\'\n    ELSE \'Leaf\'\nEND AS type\nFROM tree;
[]