Home What is wrong with using 'Not In' in this SQL query?
Reply: 5

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

Saket
1#
Saket Published in 2017-11-14 06:46:20Z

I have table called BST as shown below:

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:

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:

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

Caius Jard
2#
Caius Jard Reply to 2017-11-14 07:16:45Z

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

Mureinik
3#
Mureinik Reply to 2017-11-14 06:50:12Z

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.

gotqn
4#
gotqn Reply to 2017-11-14 06:59:15Z

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];

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.

Mopetite
5#
Mopetite Reply to 2017-11-14 07:02:35Z

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.

sanjay singh
6#
sanjay singh Reply to 2017-11-16 06:48:17Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.306558 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO