在这个SQL查询中使用“Not In”有什么问题?

I have table called BST as shown below:

我有一个叫BST的表格，如下所示:

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:

这里N是二叉树节点的值，P是它的父节点。我必须编写一个查询来确定一个节点是根节点、叶节点还是内部节点。我为此写了下面的SQL查询:

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?

但是我不知道第一个有什么问题。有人能给我解释一下吗?

5 个解决方案

#1

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

问题是你的P值中有一个是空的。通过说从t中选择不同的p，其中p至少在一个子查询中不是空的

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

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.

null P值包含在选定的不同值列表中，而not in不能确定给定的N值是否等于/不等于来自P的根节点的null。

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

这有点违背直觉，但没有任何东西等于或不等于零，甚至不等于零。使用=，一方为null，结果为null，不是true，也不是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

IN可用于检查列表中是否有值，但如果列表包含空值，则不能用于检查列表中是否有值

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

用ELSE形式表示。或者将disctinct查询作为FROM块中的子查询，并对其进行左连接

#2

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".

in是一系列=检查的简写。null不是一个值，而是它的不足。每当将它应用到期望值(如=或in)的运算符时，都会导致null，这不是“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.

您可以将null视为一个“未知”值。例如-是否从表中选择的值列表中的未知值?我们不能知道。

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

因此，必须显式地处理nulls，就像在第二个查询中那样。

#3

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.

其思想是使用两个左连接，以查看当前节点是否为子节点，当前节点是否为父节点。

#4

Because P has a null value.

因为P有一个空值。

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.

不能将NULL与常规(算术)比较运算符进行比较。任何与NULL的算术比较都将返回NULL，甚至NULL = NULL或NULL <> NULL都会产生NULL。

Use IS or IS NOT instead.

用IS或NOT代替。

#5

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

在不存在的地方写，而不是在不存在的地方写，这样它就不会考虑为null

select N,

选择N,

case

情况下

when P is null then 'Root'

当P为空时，那么" Root "

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

当N in时(从BST中选择明显的P)则为“Inner”

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

当N不存在时(从BST中选择*为t2，其中t2.N=t1.N)则为“叶”

end as type from BST as t1

从BST到t1结束为类型

#1