MySQL

SELECT n, CASE WHEN n NOT IN (SELECT DISTINCT p FROM bst WHERE p IS NOT NULL ) THEN "Leaf" when p is NULL THEN "Root" ELSE "Inner" END AS 'node_type' FROM bst ORDER BY n ASC;

((MYSQL))

SELECT N, CASE WHEN P IS NULL THEN "Root" WHEN N IN(SELECT P FROM BST) THEN "Inner" ELSE "Leaf" END AS node FROM BST ORDER BY N;

For MySQL

SELECT
    n,
    CASE
            WHEN n NOT IN (SELECT DISTINCT p FROM bst WHERE p IS NOT NULL) THEN "Leaf"
            WHEN p IS NULL THEN "Root"
            ELSE "Inner"
    END AS node_type
FROM bst
ORDER BY n;

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

* WHEN N NOT IN(SELECT DISTINCT(P) FROM BST) THEN "Leaf" ELSE * We want N NOT IN(SELECT DISTINCT(P) FROM BST) to return True for nodes which are not present in attribute P In SQL, NOT IN fails if the subquery returns even one NULL. The subquery return Null for Root node. When you do N NOT IN (...) and the list contains NULL, the result is unknown, so the whole WHEN condition is false, even for correct leaves. * ....WHEN N NOT IN(SELECT DISTINCT(P) FROM BST WHERE P IS NOT NULL) THEN "Leaf" ....* for desired result NOTE :- ...WHERE P IS NOT NULL... for avoiding error due to edge cases