Finding "Leaf" in a Tree using MySQL-CodePudding

I am trying to identify the "Leaf" in a tree but I am confused why my query is not giving me what I want.

Here is the Problem:

So my idea is that as long as the id is not in p_id column, then it should be a "Leaf"

select id, 'Leaf' as type
from Tree
where id not in (select distinct(p_id)
                 from Tree)

However, the query above is returning nothing to me.

The solution is pretty much the same as mine except it specify that the p_id cannot be NULL, then it is returning what I want.

select id
from Tree
where id not in(select distinct(p_id) 
                from Tree 
                where p_id IS NOT NULL)

I am confused as why does adding the where clause will make a difference?

CodePudding user response：

You guessed it. It is because NULL does not compare to anything. A value is not different from null and a value is not same as null.

You can get your resut with following query:

select distinct t.id, 
  if (p.id is null, 'Root', if (d.id is null, 'Leaf', 'Inner'))
from Tree t
  left join Tree p on p.id=t.p_id
  left join Tree d on d.p_id=t.id;

See dbfiddle.