Simple answer can be used both for Ms sql & Mysql, faster than 90%, don't need join
SELECT\n       id,\n      CASE WHEN p_id is null THEN \'Root\'\n           WHEN id IN (SELECT DISTINCT p_id FROM tree) AND p_id is NOT null THEN \'Inner\'\n    
SELECT\n       id,\n      CASE WHEN p_id is null THEN \'Root\'\n           WHEN id IN (SELECT DISTINCT p_id FROM tree) AND p_id is NOT null THEN \'Inner\'\n           ELSE \'Leaf\' END AS Type\nFROM tree
[]