What is wrong with using 'Not In' in this SQL query?

user_2990310 0
Viewed 133

I have table called BST as shown below:

enter image description here

Here N is value of node of Binary Tree and P is its Parent node. I have to write a query that will determine if a node is a Root Node, Leaf Node or Inner Node. I wrote below SQL query for this:

select N, 
case
when P is null then 'Root'
when N in (select distinct P from BST) then 'Inner'
when N not in (select distinct P from BST) then 'Leaf'
end as type
from BST

However, this is not giving me desired result as last condition for 'Leaf' in Case statement doesn't satisfy for leaf node. I am getting below output in this case:

enter image description here

I have a workaround for now as below query which is giving me expected output:

select N, 
case
when P is null then 'Root'
when N in (select distinct P from BST) then 'Inner'
else 'Leaf'
end as type
from BST

Expected Output:

enter image description here

But I can't figure out what's wrong with the first one. Could someone explain me this?

select null tsql sql-server sql
5 Answers

in is a shorthand for a series of = checks. null, is not a value - it's the lack thereof. Whenever applying it to an operator expecting a value (like =, or in), it results in null, which is not "true".

You can think of null as an "unknown" value. I.e. - is an unknown value in a list of values selected from a table? We can't know.

Thus, you have to handle nulls explicitly, as you did in your second query.

user_24227760

Try this:

DECLARE @DataSource TABLE
(
    [N] TINYINT
   ,[P] TINYINT
);

INSERT INTO @DataSource ([N], [P])
VALUES (1, 2)
      ,(3, 2)
      ,(5, 6)
      ,(7, 6)
      ,(2, 4)
      ,(6, 4)
      ,(4, 15)
      ,(8, 9)
      ,(10, 9)
      ,(12, 13)
      ,(14, 13)
      ,(9, 11)
      ,(13, 11)
      ,(11, 15)
      ,(15, NULL);

SELECT DISTINCT 
       DS1.[N]
      ,CASE WHEN DS2.[N] IS NULL THEN 'IsLeaf' ELSE CASE WHEN DS3.[N] IS NOT NULL THEN 'Inner' ELSE ' Root' END END AS [Type]
FROM @DataSource DS1
LEFT JOIN @DataSource DS2
    ON DS1.[N] = DS2.[P]
LEFT JOIN @DataSource DS3
    ON DS1.[P] = DS3.[N]
ORDER BY [Type];

enter image description here

The idea is to use two LEFT JOINs in order to see if the current node is child and if the current not is parent.

user_10803540

The problem is because one of your P values is null. Remove this by saying select distinct p from t where p is not null in at least the Not In one of your subqueries

http://sqlfiddle.com/#!6/77fb8/3

hence:

select N, 
case
when P is null then 'Root'
when N in (select distinct P from BST) then 'Inner'
when N not in (select distinct P from BST where p is not null) then 'Leaf'
end as type
from BST

the null P value gets included in the list of distinct values selected, and not in can not determine if a given value of N is equal/not equal to the null coming from the root node of P.

It's somewhat counter intuitive but nothing is ever equal to or not equal to a null, not even null. using = with one side being null results in null, not true and not false

IN can be used to check if a value IS in the list, but not if it's not, if the list includes a null

1 IN (1,2,null) --true
3 IN (1,2,null) --null, not false, null which isn't true
3 NOT IN (1,2,null) --null, not false, null which isn't true

The ELSE form is the way to go here. Or put the disctinct query in as a subquery in the FROM block and do a left join to it

user_14106640

Because P has a null value.

You can't compare NULL with the regular (arithmetic) comparison operators. Any arithmetic comparison to NULL will return NULL, even NULL = NULL or NULL <> NULL will yield NULL.

Use IS or IS NOT instead.

user_73528840

Write where notExists instead of not in so that it will not consider nulls

select N,

case

when P is null then 'Root'

when N in (select distinct P from BST) then 'Inner'

when N not exists (select * from BST as t2 where t2.N=t1.N) then 'Leaf'

end as type from BST as t1

user_89494350
Related