not in operator in sql server not working

Question

I was solving hackerranks binary tree question.PFB the question

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

Root: If node is root node.
Leaf: If node is leaf node.
Inner: If node is neither root nor leaf node.

I was able to solve this using the below query

select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner' else 'Leaf'
end
from BST order by n

but before this, I was trying the below query which was not working

select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner'
 when p is not null and (n not in (select p from BST)) then 'Leaf'
end
from BST order by n

The above query was giving the root note and inner nodes but instead of leaf it was giving null, Can somebody please explain why is it showing this behaviour.

You can try the question here

Thankyou

Answer 1

This is because of how sql treats IN and NOT IN query.

NOT IN evaluates to != clause for every element in the list and your list (column p) contains NULL value. Hence, value != NULL evaluates to UNKNOWN.

Duplicate: NOT IN clause and NULL values

Try this:

select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner'
 when p is not null and (n not in (select p from BST where p IS NOT null)) then 'Leaf'
end
from BST order by n

This should give the expected result.