Can this query explained please?

Question

I have a table where n is the node and p is the parent of the node. I need to find which is the parent, inner and leaf. Parent is the parent of all if P in NULL. Inner is the parent of atleast one leaf and leaf has nothing.

 n    | p    |
+------+------+
|    1 |    2 |
|    3 |    2 |
|    6 |    8 |
|    9 |    8 |
|    2 |    5 |
|    8 |    5 |
|    5 | NULL |
+------+------+ 

The table is listed above. I dont understand how this query works.

SELECT N, IF(P IS NULL,'Root',IF((SELECT COUNT(*) FROM BST WHERE P=B.N)>0,'Inner','Leaf')) FROM BST AS B ORDER BY N;

Here the part I could not understand is P=B.N. When I execute this query alone it shows count as zero but when I execute this whole query answer is correct. Please Can someone illustrate how this query works with values taken from this above table as an example. Thanks in advance

The answer for this query is

 N    | asnwer |
+------+--------+
|    1 | Leaf   |
|    2 | Inner  |
|    3 | Leaf   |
|    5 | Root   |
|    6 | Leaf   |
|    8 | Inner  |
|    9 | Leaf   |
+------+--------+

In the above query P=B.N there is no value for eg., the first value of P is 2 and the first value of N is 1. Both are not equal and throughout the table no value of P is equal to N. Then How the result set is correct. Please explain

Answer 1

I prefer CASE expressions over the function IF():

SELECT N, 
       CASE 
         WHEN P IS NULL THEN 'Root'
         ELSE CASE
           WHEN (SELECT COUNT(*) FROM BST AS BB WHERE BB.P = B.N) = 0 THEN 'Leaf'
           ELSE 'Inner'
         END
       END
FROM BST AS B 
ORDER BY N;

I changed the condition to check first to = instead of >.
I also use an alias BB for the table inside the correlated subquery just to get the idea that this is not the original queried table but another copy of the table.

For every row of the table, the column P is checked if it is null.
If it is null then the result is of course 'Root'.

If it is not null then what must be checked is the number of rows in the table where the value of the column N of the table (this is what B.N represents) is equal to the column P (this is what BB.P represents).

In order to get this result what is needed is another copy of the table where the counting is done.
If the result is equal to 0 this means that there are not any rows of the table that have as parent that N (B.N) so the result is 'Leaf'.
In any other case the result is 'Inner'