“不在”不按预期工作(“NOT IN” not working as expected)

 我正在写一个查询来查找表BST中由节点的值排序的节点类型。 表BST有两列N和P，其中N代表BST中节点的值，P代表N的父亲。例如，BST具有以下记录： BST表 
 
 我成功执行了如下查询： 
 
SELECT n,CASE
                WHEN p IS NULL THEN 'Root'
                WHEN n IN (SELECT DISTINCT p FROM BST) THEN 'Inner'
                ELSE 'Leaf'
             END
FROM BST
ORDER BY n;
 
 结果： 按预期结果 
 
 但是，当我使用“NOT IN”尝试相同的查询时，不是使用“IN”，如下所示： 
 
SELECT n,CASE
            WHEN p IS NULL THEN 'Root'
            WHEN n NOT IN (SELECT DISTINCT p FROM BST) THEN 'Leaf'
            ELSE 'Inner'
         END
FROM BST
ORDER BY n;
 
 它没有按预期工作。 为什么这样？ 

I was writing a query to find the node type from the table BST ordered by the value of the node. table, BST, had two columns N and P, where N represents the value of a node in BST, and P is the parent of N. say, BST has following records: BST Table
 
I successfully executed the query as follows:
 
SELECT n,CASE
                WHEN p IS NULL THEN 'Root'
                WHEN n IN (SELECT DISTINCT p FROM BST) THEN 'Inner'
                ELSE 'Leaf'
             END
FROM BST
ORDER BY n;
 
Result: Result as expected
 
But instead of using "IN", when I tried the same query using "NOT IN" as given below:
 
SELECT n,CASE
            WHEN p IS NULL THEN 'Root'
            WHEN n NOT IN (SELECT DISTINCT p FROM BST) THEN 'Leaf'
            ELSE 'Inner'
         END
FROM BST
ORDER BY n;
 
it didn't work as expected. Why so?

原文：https://stackoverflow.com/questions/42837073