blocks|key|344973|text|这是因为sql如何对待IN和NOT+IN查询。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|344974|对于列表中的每个元素，NOT+IN计算结果为!=子句，您的列表(p列)包含NULL值。因此，value+!=+NULL的计算结果是未知的。|344975|复制：非IN子句和空值|344976|试试这个：|344977|select+n,
+case+
+when+p+is+null+then+'Root'
+when+p+is+not+null+and+(n+in+(select+p+from+BST))+then+'Inner'
+when+p+is+not+null+and+(n+not+in+(select+p+from+BST+where+p+IS+NOT+null))+then+'Leaf'
end
from+BST+order+by+n|code-block|syntax|javascript|344978|这将产生预期的结果。|344979|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/129077/not-in-clause-and-null-values?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa^0|B|2|E|6|0|B|6|M|2|11|4|0|3|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1C|8|@]|D|@$9|1D|A|1E|1|1F]]|E|$]]|$1|J|3|K|5|6|7|1G|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|1H|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|1I|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1J|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

This is because of how sql treats <code>IN</code> and <code>NOT IN</code> query.

<code>NOT IN</code> evaluates to <code>!=</code> clause for every element in the list and your list (column p) contains <code>NULL</code> value. Hence, value != NULL evaluates to UNKNOWN.

Duplicate: <a href="https://stackoverflow.com/questions/129077/not-in-clause-and-null-values?utm_medium=organic&amp;utm_source=google_rich_qa&amp;utm_campaign=google_rich_qa">NOT IN clause and NULL values</a>

Try this:

<pre><code>select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner'
 when p is not null and (n not in (select p from BST where p IS NOT null)) then 'Leaf'
end
from BST order by n
</code></pre>

This should give the expected result.

blocks|key|345025|text|下面是与NULL一起使用时not+in行为的一个很好的示例|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|345026|select+
case+
++when+NULL+not+in+('a','b')+then+'not+in'
++when+NULL+in+('a','b')+then+'in'
++else+'What?'+
end|code-block|syntax|javascript|345027|这将返回What?，这似乎是出乎意料的。|345028|entityMap^0|4|4|D|6|0|0|4|5|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Here's a good working example of the behaviour of <code>NULL</code> when used with <code>not in</code>:

<pre><code>select 
case 
 when NULL not in ('a','b') then 'not in'
 when NULL in ('a','b') then 'in'
 else 'What?' 
end
</code></pre>

This returns <code>What?</code>, which might seem unexpected.

blocks|key|345049|text|学习如何将not+exists与子查询一起使用，而不是not+in。它具有正确的语义，因此您不太容易出错。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|345050|您可以将case表达式编写为：|345051|select+n,
+++++++(case+when+p+is+null+then+'Root'
+++++++++++++when+exists+(select+1+from+BST+bst2+where+bst2.p+=+bst.n)
+++++++++++++then+'Inner'+
+++++++++++++else+'Leaf'
++++++++end)
from+BST
order+by+n;|code-block|syntax|javascript|345052|注意，我还简化了case表达式。您不需要重复这些条件，因为case表达式是按顺序计算的。|345053|entityMap^0|5|A|R|6|0|4|4|0|0|8|4|T|4|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

Learn to use <code>not exists</code> with a subquery, rather than <code>not in</code>. It has the correct semantics, so you are much less prone to an error.

You can write your <code>case</code> expression as:

<pre><code>select n,
 (case when p is null then 'Root'
 when exists (select 1 from BST bst2 where bst2.p = bst.n)
 then 'Inner' 
 else 'Leaf'
 end)
from BST
order by n;
</code></pre>

Notice that I've also simplified the <code>case</code> expression. You don't need to repeat the conditions, because <code>case</code> expressions are evaluated in order.

blocks|key|1456251|text|因此，与其使用“NULL”和“和”，不如尝试一下..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1456252|选择N，当B.p为NULL时，当B.N+IN(从BST中选择p)，然后从BST+B顺序中选择‘内部’否则‘叶子’结束的情况下，从BST+B顺序N；|1456253|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

So, Instead of using 'NOT NULL' and 'AND' there you can simply try..
select N,
case when B.p is NULL then 'Root'
when B.N IN(select p from BST) then 'Inner'
else 'Leaf'
End case
from BST B order by N;

I was solving hackerranks binary tree question.PFB the question

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

<pre><code>Root: If node is root node.
Leaf: If node is leaf node.
Inner: If node is neither root nor leaf node.
</code></pre>

I was able to solve this using the below query

<pre><code>select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner' else 'Leaf'
end
from BST order by n
</code></pre>

but before this, I was trying the below query which was not working

<pre><code>select n,
 case 
 when p is null then 'Root'
 when p is not null and (n in (select p from BST)) then 'Inner'
 when p is not null and (n not in (select p from BST)) then 'Leaf'
end
from BST order by n
</code></pre>

The above query was giving the root note and inner nodes but instead of leaf it was giving null, Can somebody please explain why is it showing this behaviour.

You can try the question <a href="https://www.hackerrank.com/challenges/binary-search-tree-1/problem" rel="nofollow noreferrer">here</a> 

Thankyou

not in operator in sql server not working

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在解决黑客队伍二叉树question.PFB的问题给出了一个表BST，包含两个列: N和P，其中N表示二叉树中节点的值，P是N的父列。编写查询，查找按节点值排序的二叉树节点类型。为每个节点输出以下内容之一：Root: If node is root node.Leaf: If node is leaf node.In...

问在sql server中没有在运算符中工作。
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在sql server中没有在运算符中工作。EN