In MySQL SELECT 2+NULL FROM tbl_name will return NULL, since MySQL cannot interpret NULL as a number.
在MySQL SELECT 2 + NULL FROM tbl_name将返回NULL,因为MySQL无法将NULL解释为数字。
But why will SELECT SUM(quantity) FROM tbl_name not return NULL if just one of the values is NULL? The same goes for MIN, MAX, AVG, etc. Since MySQL doesn't know what NULL could be, shouldn't it return NULL for all the specified functions?
但是,如果其中一个值为NULL,为什么SELECT SUM(quantity)FROM tbl_name不会返回NULL?对于MIN,MAX,AVG等也是如此。由于MySQL不知道NULL可能是什么,它不应该为所有指定的函数返回NULL吗?
3 个解决方案
#1
This is a good question, and one that does not have a good answer. The treatment of NULL in your two examples is different.
这是一个很好的问题,而且没有一个好的答案。在两个例子中对NULL的处理是不同的。
The fundamental problem is what NULL means. Commonly, it is used to denote missing values. However, in the ANSI standard, it stands for an unknown value. I'm sure philosophers could devote tomes to the difference between "missing" and "unknown".
根本问题是NULL意味着什么。通常,它用于表示缺失值。但是,在ANSI标准中,它代表一个未知值。我确信哲学家们可以将“失踪”和“未知”之间的差异用于大部分。
In a simple expression (boolean or arithmetic or scalar of another sort), ANSI defines the result of "unknown" in almost all cases where any of the operands are "unknown". There are some exceptions: NULL AND FALSE is false and NULL IS NULL is true, but these are rare.
在一个简单的表达式(布尔或算术或另一种类型的标量)中,ANSI几乎在所有操作数都是“未知”的情况下定义了“未知”的结果。有一些例外:NULL和FALSE为false,NULL IS NULL为true,但这些很少见。
For the aggregation operations, think of SUM() as "sum all the known values", and so on. SUM() treats NULL values differently from +. But, this behavior is also standard so that is how all databases work.
对于聚合操作,将SUM()视为“求和所有已知值”,依此类推。 SUM()以不同于+的方式处理NULL值。但是,这种行为也是标准的,所以这就是所有数据库的工作方式。
If you want a NULL value for an aggregation when any of its operands is NULL, then you need to use CASE. I think the easiest way for a single column is:
如果在任何操作数为NULL时希望聚合为NULL值,则需要使用CASE。我认为单列的最简单方法是:
(CASE WHEN COUNT(col) = COUNT(*) THEN SUM(COL) END)
#2
According to the MySQL Reference, NULL values are ignored in the aggregate functions. Here is the direct quote from the page:
根据MySQL Reference,聚合函数中忽略NULL值。以下是该页面的直接引用:
Unless otherwise stated, group functions ignore NULL values.
除非另有说明,否则组函数会忽略NULL值。
#3
To be simple,
简单来说,
2 + NULL, probably deals with one row only...
2 + NULL,可能只处理一行......
Where-as SUM( 2 and NULL ) will be with 2 different rows. So, NULLs are simply ignored while you group! What ever happens with null with the same row only will be resolved into NULL again.
其中,SUM(2和NULL)将包含2个不同的行。因此,在您分组时,只会忽略NULL!只有同一行的null所发生的事情将再次解析为NULL。
So
COUNT(NULL,1) = 1 (not 2) MAX(NULL,1) = 1 MIN(NULL,1) = 1 AVG(NULL,1) = 1 (not .5)
This behaviour is same in most of the DBMS versions!
大多数DBMS版本中的行为都是相同的!
#1
This is a good question, and one that does not have a good answer. The treatment of NULL in your two examples is different.
这是一个很好的问题,而且没有一个好的答案。在两个例子中对NULL的处理是不同的。
The fundamental problem is what NULL means. Commonly, it is used to denote missing values. However, in the ANSI standard, it stands for an unknown value. I'm sure philosophers could devote tomes to the difference between "missing" and "unknown".
根本问题是NULL意味着什么。通常,它用于表示缺失值。但是,在ANSI标准中,它代表一个未知值。我确信哲学家们可以将“失踪”和“未知”之间的差异用于大部分。
In a simple expression (boolean or arithmetic or scalar of another sort), ANSI defines the result of "unknown" in almost all cases where any of the operands are "unknown". There are some exceptions: NULL AND FALSE is false and NULL IS NULL is true, but these are rare.
在一个简单的表达式(布尔或算术或另一种类型的标量)中,ANSI几乎在所有操作数都是“未知”的情况下定义了“未知”的结果。有一些例外:NULL和FALSE为false,NULL IS NULL为true,但这些很少见。
For the aggregation operations, think of SUM() as "sum all the known values", and so on. SUM() treats NULL values differently from +. But, this behavior is also standard so that is how all databases work.
对于聚合操作,将SUM()视为“求和所有已知值”,依此类推。 SUM()以不同于+的方式处理NULL值。但是,这种行为也是标准的,所以这就是所有数据库的工作方式。
If you want a NULL value for an aggregation when any of its operands is NULL, then you need to use CASE. I think the easiest way for a single column is:
如果在任何操作数为NULL时希望聚合为NULL值,则需要使用CASE。我认为单列的最简单方法是:
(CASE WHEN COUNT(col) = COUNT(*) THEN SUM(COL) END)
#2
According to the MySQL Reference, NULL values are ignored in the aggregate functions. Here is the direct quote from the page:
根据MySQL Reference,聚合函数中忽略NULL值。以下是该页面的直接引用:
Unless otherwise stated, group functions ignore NULL values.
除非另有说明,否则组函数会忽略NULL值。
#3
To be simple,
简单来说,
2 + NULL, probably deals with one row only...
2 + NULL,可能只处理一行......
Where-as SUM( 2 and NULL ) will be with 2 different rows. So, NULLs are simply ignored while you group! What ever happens with null with the same row only will be resolved into NULL again.
其中,SUM(2和NULL)将包含2个不同的行。因此,在您分组时,只会忽略NULL!只有同一行的null所发生的事情将再次解析为NULL。
So
COUNT(NULL,1) = 1 (not 2) MAX(NULL,1) = 1 MIN(NULL,1) = 1 AVG(NULL,1) = 1 (not .5)
This behaviour is same in most of the DBMS versions!
大多数DBMS版本中的行为都是相同的!