这是AND和OR运算符与NULL的真值表吗?

huangapple go评论68阅读模式
英文:

Why is this the truth table for AND,OR with NULL?

问题

在下面的ANDOR的真值表中,使用了NULL值(来自MySQL查询):

我猜想NULL AND NULL | NULL OR NULL 永远不会短路,所以返回最终的NULL

但是其他三值逻辑如何工作呢?例如,为什么 FALSE OR NULL 不会返回 FALSE?为什么 FALSE AND NULL 不会返回 NULL

我能找到的最好的资源是:https://modern-sql.com/concept/three-valued-logic

顺便说一句,要在SQL中进行测试,你可以这样做:

select 'AND' op, true and null , false and null, null and null 
union all
select 'OR', true or null, false or null, null or null
英文:

In the following truth table for AND and OR using a NULL value (taken from a MySQL query):

这是AND和OR运算符与NULL的真值表吗?

I am guessing that NULL AND NULL|NULL OR NULL will never short-circuit so return the final NULL.

But how do the other 3VL work? For example, why wouldn't FALSE OR NULL return FALSE? Why wouldn't FALSE AND NULL return NULL?

The best resource I could find here is: https://modern-sql.com/concept/three-valued-logic


By the way, to test in SQL you can do:

select 'AND' op, true and null , false and null, null and null 
union all
select 'OR', true or null, false or null, null or null

答案1

得分: 2

SQL标准将null定义为缺失值。它存在,但只是缺失的,因此它不在数据中[尚未]。

此外,SQL标准定义了三个逻辑值:

  • True(真)
  • False(假)
  • Unknown(未知)

最后一个由null表示。

然后,false OR null(假或null)可以评估为false(假)或true(真),具体取决于右侧的缺失值最终是false(假)还是true(真)。因此,结果是Unknown(未知,null)。

另一方面,false AND null(假且null)只能评估为false(假)。右侧的缺失值是false(假)还是true(真)都不重要。在这两种情况下,表达式的结果都将是false(假)。

英文:

The SQL Standard defines null as a missing value. It does exist, but it's just missing, so it's not in the data [yet].

Furthermore, the SQL Standard defines three logical values:

  • True
  • False
  • Unknown

The last one is represented by a null.

Then, false OR null can evaluate to false or true, depending if the missing value in the right side ends up being false or true respectively. Therefore the result is Unknown (null).

On the other hand, false AND null can only evaluate to false. It doesn't matter if the missing value in the right side evaluates to false or true. In both cases the result of the expression would be false.

答案2

得分: 1

以下是翻译好的部分:

  1. NULL AND NULL --> TRUE AND TRUE (TRUE), FALSE AND FALSE (FALSE) --> NULL
  2. NULL OR NULL --> TRUE OR TRUE (TRUE), FALSE OR FALSE (FALSE) --> NULL
  3. NULL AND TRUE --> TRUE AND TRUE (TRUE), FALSE AND TRUE (FALSE) --> NULL
  4. NULL OR TRUE --> FALSE OR TRUE (TRUE), TRUE OR TRUE (TRUE) --> TRUE
  5. NULL AND FALSE --> FALSE AND FALSE (FALSE), TRUE AND FALSE (FALSE) --> FALSE
  6. NULL OR FALSE --> FALSE OR FALSE (FALSE), TRUE OR FALSE (TRUE) --> NULL

所以,通过观察结果是否可以预测,我们可以确定结果是单一逻辑值还是不确定的 NULL

英文:

Here's one way to conceptualize it. Imagine that the NULL value, in the context of a logical SQL expression, may evaluate to either TRUE or FALSE since it is an indeterminate value. Now, let's substitute in both TRUE and FALSE where there may be a NULL value -- if it gives a single logical value, then we know it will be that, if it gives both, then it is still indeterminate and thus we can set it to NULL. Let's try it out:

  1. NULL AND NULL --> TRUE AND TRUE (TRUE), FALSE AND FALSE (FALSE) --> NULL
  2. NULL OR NULL --> TRUE OR TRUE (TRUE), FALSE OR FALSE (FALSE) --> NULL
  3. NULL AND TRUE --> TRUE AND TRUE (TRUE), FALSE AND TRUE (FALSE) --> NULL
  4. NULL OR TRUE --> FALSE OR TRUE (TRUE), TRUE OR TRUE (TRUE) --> TRUE
  5. NULL AND FALSE --> FALSE AND FALSE (FALSE), TRUE AND FALSE (FALSE) --> FALSE
  6. NULL OR FALSE --> FALSE OR FALSE (FALSE), TRUE OR FALSE (TRUE) --> NULL

So there, by seeing if the resultant value can be predicted or not we can see whether the result is a single logical value or the indeterminate NULL.

huangapple
  • 本文由 发表于 2023年3月7日 06:52:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/75656558.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定