IN 运算符 SQL [英] IN Operator SQL

问题描述

我有一个名为 NUMS 的表，只有一列 n.
我在其中填充值 1,2,3,4,5,null .

I have a table called NUMS with a single column n.
And I fill values 1,2,3,4,5,null in it.

现在查询

SELECT n FROM Nums 
 WHERE n IN (1, 2, null)

在这种情况下，我想它已转换为

In this case I guess it's converted to

SELECT n FROM Nums 
 Where n = 1 OR n = 2 OR n = null   

我还将 n 与一个应该产生未知的空值进行比较，它应该返回一个空集.但它返回 1,2(null 不返回，虽然包含在 IN 运算符中)

I am also comparing n with a null value which should yield unknown and it should return an empty set.But it's returning 1,2 (null is not returning, although included in IN operator)

现在查询

SELECT n FROM Nums WHERE n NOT IN(1, 2, null)  

...被转换为:

SELECT n FROM Nums 
 Where n!=1 AND n!=2 AND n!=null  

这里我上面说的有效，它不返回任何东西.

Here what I said above works and it does not return anything.

谁能详细解释一下发生了什么.

Can anyone explain in detail what's happening.

推荐答案

好的我已经找到答案了

OK I have found the answer

SELECT n FROM Nums    
WHERE n NOT IN (1, 2, null)

评估为

SELECT n FROM Nums  
n!=1 AND n!=2 AND n!=null

上次比较的结果总是未知的.
AND 的真值表表明，只要其中包含一个 Unknown (U,T)(U,F),(U,U)，结果只能是 U 或 F (U=Unknown, F=False)因此它不会包含在结果集中.

The outcome of last comparison will always be UNKNOWN.
and the truth table of AND shows that as soon as one Unknown is invloved in it (U,T)(U,F),(U,U) the reult can only be U or F (U=Unknown, F=False) and hence it will not be included in the result set.

如果

SELECT n FROM Nums
WHERE n IN (1, 2, null) 

等于

SELECT n FROM Nums
WHERE n = 1 OR n =2 OR n=null

现在对于 n=1 的行，操作 n=1 将成为真
对于 n=2 的行，操作 n=2 将成为真
并且对于所有行 n=null 将是未知的

Now for the row with n=1, the operation n=1 will come as true
and for the row with n=2, the operation n=2 will come as true
and for all rows n=null will be unknown

所以它在结果集中给出了 1 和 2.
希望你们喜欢它.
任何人都可以将我的回复标记为答案

So it gives 1 and 2 in the result set.
Hope u people liked it.
CAN ANYONE PLEASE MARK MY REPLY AS ANSWER

这篇关于IN 运算符 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！