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

Question

我有一个名为BST的表格，如下所示：

这里，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

然而，这并没有给我期望的结果，因为在Case语句中'Leaf'的最后一个条件不满足叶节点。在这种情况下，我得到了以下输出： 我现在有一个解决方法，使用以下查询可以给我期望的输出：

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

预期输出：

但是我无法弄清楚第一个有什么问题。能有人解释一下吗？

- Saket Kumar

Answer 1

问题是因为您的一个P值为空。在您的子查询的Not In语句中加入select distinct p from t where p is not null以解决这个问题。 http://sqlfiddle.com/#!6/77fb8/3 因此：

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

当选择不同的值时，空P值会被包含在其中，而not in无法确定给定的N值是否等于/不等于来自P根节点的空值。

这有点违反直觉，但没有什么可以等于或不等于null，甚至null本身也不行。使用=时，其中一个值为null会导致结果为null，既不是true也不是false。

IN可用于检查值是否在列表中，但如果列表包含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

这里应该使用ELSE语句。或者将distinct查询作为子查询放入FROM块中，并左连接它。

- Caius Jard

Answer 2

试试这个：

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

这个想法是使用两个LEFT JOIN，以便查看当前节点是否为子节点和当前节点是否为父节点。

- gotqn

Answer 3

in是一系列=检查的简写。 null不是一个值，而是缺少值。每当将其应用于期望值的运算符（如=或in）时，它会导致null，这不是“true”。

您可以将null视为“未知”值。也就是说-在从表中选择的值列表中是否存在未知值？我们无法知道。

因此，您必须明确处理null，就像在第二个查询中所做的那样。

- Mureinik

Answer 4

因为 P 的值为空。

您不能使用常规（算术）比较运算符与 NULL 进行比较。任何与 NULL 的算术比较都会返回 NULL，即使是 NULL = NULL 或 NULL <> NULL 也会得到 NULL。

请使用 IS 或 IS NOT 代替。

- Suyanhanx

Answer 5

使用notExists替换not in，这样它就不会考虑null值

选择 N,

情况

当 P 为 null 时，则为“根”

当 N 在（从BST中选择不同的P）中时，则为“内部”

当N不存在（从BST作为t2中选择*的t2中不存在t1.N的情况下）时，则为“叶子”

end as type from BST as t1

- sanjay singh