Tree Node

You can write a MySQL query to identify the type of each node in the given tree structure. Here’s the query:

1
2
3
4
5
6
7

SELECT id,
       CASE
           WHEN p_id IS NULL THEN 'Root'
           WHEN id NOT IN (SELECT p_id FROM Tree WHERE p_id IS NOT NULL) THEN 'Leaf'
           ELSE 'Inner'
       END AS type
FROM Tree;

Explanation:

• We use the CASE expression to determine the type of each node.
• If p_id is NULL, the node is the root of the tree.
• If the id is not present in the p_id column (excluding NULL), the node is a leaf node, meaning it has no children.
• If none of the above conditions are met, the node is an inner node, meaning it has both a parent and child nodes.