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

can u provide the URL of the hackerrank question? – Pawan Kumar May 10 '18 at 05:15 — Pawan Kumar, May 10 '18 at 05:15

score 5 · Accepted Answer · answered May 10 '18 at 05:11

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.

score 1 · Answer 2 · answered May 10 '18 at 05:14

1

Here's a good working example of the behaviour of NULL when used with not in:

select 
case 
  when NULL not in ('a','b') then 'not in'
  when NULL in ('a','b') then 'in'
  else 'What?' 
end

This returns What?, which might seem unexpected.

answered May 10 '18 at 05:14

mjsqu

5,151
1
17
21

score 0 · Answer 3 · answered May 10 '18 at 11:55

Learn to use not exists with a subquery, rather than not in. It has the correct semantics, so you are much less prone to an error.

You can write your case expression as:

select n,
       (case when p is null then 'Root'
             when exists (select 1 from BST bst2 where bst2.p = bst.n)
             then 'Inner' 
             else 'Leaf'
        end)
from BST
order by n;

Notice that I've also simplified the case expression. You don't need to repeat the conditions, because case expressions are evaluated in order.

Shankar Naikwadi · Answer 4 · 2020-09-10T14:36:02.567

0

So, Instead of using 'NOT NULL' and 'AND' there you can simply try..

select N, case when B.p is NULL then 'Root' when B.N IN(select p from BST) then 'Inner' else 'Leaf' End case from BST B order by N;

edited Sep 10 '20 at 14:36

answered Sep 09 '20 at 17:42

Shankar Naikwadi

1
2

Please format your code sections and try not to link to external sources (they might come unavailable in the future). You can leave the link but quote the relevant section(s) in your answer. – Sander Sep 09 '20 at 19:58

not in operator in sql server not working

4 Answers4