SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Inner' ELSE 'Leaf' END AS NodeType FROM BST ORDER BY N;

Here is the correct answer: SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Inner' ELSE 'Leaf' END AS NodeType FROM BST ORDER BY N;

SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N NOT IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf' ELSE 'Inner'
END FROM BST ORDER BY N

Why is this wrong?

SELECT t.N, CASE WHEN t.P IS NULL THEN 'Root' WHEN gc.N IS NULL THEN 'Leaf' ELSE 'Inner' END FROM BST AS t LEFT JOIN BST AS gc ON t.N = gc.P ORDER BY t.N ASC

SELECT
N, if (N = ANY(Select P from BST) AND (P IS NULL) ,"Root", if(N = ANY(Select P from BST),"Inner","Leaf")) as output from BST order by N