If I run a query such as:
如果我运行这样的查询:
SELECT COUNT(*) as num FROM table WHERE x = 'y'
Will it always return a result, even when the query doesn't match any record? Or do I need to validate and make sure a row is returned as the result?
它是否总是返回一个结果，即使查询与任何记录不匹配?还是需要验证并确保结果返回一行?
7 个解决方案
#1
44
Yes, because it's an aggregate and returns zero. Unless you add GROUP BY in which case no result because there is no group...
是的，因为它是一个集合，返回0。除非你添加组，在这种情况下没有结果，因为没有组…
MAX/SUM etc would return NULL unless you add GROUP BY then no rows. Only COUNT returns a number for no results
MAX/SUM等会返回NULL，除非你添加组，否则没有行。只有COUNT返回一个没有结果的数字
Edit, a bit late: SUM would return NULL like MAX
编辑，有点晚:SUM将返回NULL，就像MAX一样
Edit, May 2013: this applies to all main RDBMS. I guess as per the ANSI standard
编辑，2013年5月:这适用于所有主要的RDBMS。我猜是按照ANSI标准
#2
8
Yes, the return value of the "count" function itself is always a non-null integer, without exception.
是的，“count”函数本身的返回值总是一个非空整数，没有例外。
Having said that, you may be more interested in the return value of your query, which is actually a result set. If that's the case, then you simply have to consider whether your query includes a "group by" clause.
话虽如此，您可能对查询的返回值更感兴趣，它实际上是一个结果集。
A non-grouped query with aggregate functions like
具有聚合函数的非分组查询
select count(*), sum(*), max(*), min(*) from table
produces a result set with a single record containing the return value(s) of the aggregate function(s). In this case, your result set will always have exactly one record, with columns containing the return values of whatever aggregate functions you included. Within this lone record, the return value of count will always be a non-null integer, while the return values of the other aggregate functions such as min, max, and sum may be null. In the above example, your returned record may be "zero, null, null, null", but will never be "null,null,null,null", since count never returns null. So if you're calling only "count", then that one column in that one record returned will always contain the non-null return value of count; hence you can rest assured that the scalar return value of your count query will always be a non-null integer.
生成一个结果集，其中包含聚合函数的返回值。在这种情况下，您的结果集总是只有一个记录，包含包含您所包含的任何聚合函数的返回值的列。在这个唯一的记录中，count的返回值总是一个非空整数，而其他聚合函数(如min、max和sum)的返回值可能为空。在上面的示例中，返回的记录可能是“0、null、null、null”，但是不会是“null、null、null、null”，因为count从不返回null。因此，如果只调用“count”，那么返回的一个记录中的一个列将始终包含count的非空返回值;因此您可以放心，count查询的标量返回值始终是一个非空整数。
On the other hand, a grouped query with aggregate functions like
另一方面，具有聚合函数的分组查询
select count(*), sum(*), max(*), min(*) from table group by column
produces a result set with zero or more records where each record contains the return value of the aggregate function(s) for each group identified. In this case, if there are no groups identified, there are no groups for the aggregate functions to run on, the functions are never run, and the "query return value" will be an empty set with no records at all.
生成一个结果集，其中每个记录包含标识的每个组的聚合函数的返回值。在这种情况下，如果没有标识的组，则没有用于运行聚合函数的组，也不会运行函数，并且“查询返回值”将是一个空集，根本没有记录。
Test it out on a table with no records to see the results:
在没有记录的桌子上进行测试，看看结果如何:
create table #t (c int);
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t; --returns one record {0, null, null, null}
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t group by c; --returns no records
drop table #t;
So whether you're calling count, min, max, or sum, it is important to understand not only the return values of individual aggregate functions, but also the "return value" of the query itself, and what that result set contains.
因此，无论您是调用count、min、max还是sum，重要的是不仅要理解单个聚合函数的返回值，还要理解查询本身的“返回值”以及查询结果集包含的内容。
#3
5
Aggregate function Count() always returns value
聚合函数Count()总是返回值
#4
2
Yes it will return a numeric always
是的，它总是返回一个数字
#5
2
Yes, it'll return 0 in such cases.
是的，在这种情况下它会返回0。
#6
2
There will always be a row of result like this:
总会有一排这样的结果:
| COUNT(*) |
------------
| 0 |
if there are no matches.
如果没有匹配。
By the way, I would prefer to count only the primary key instead of *.
顺便说一下，我宁愿只计算主键而不计算*。
#7
2
if no record is matched the count will return 0. (so yes, count always returns a result, unless you have some syntax error)
如果没有匹配的记录，计数将返回0。(是的，count总是返回一个结果，除非有语法错误)
#1
44
Yes, because it's an aggregate and returns zero. Unless you add GROUP BY in which case no result because there is no group...
是的，因为它是一个集合，返回0。除非你添加组，在这种情况下没有结果，因为没有组…
MAX/SUM etc would return NULL unless you add GROUP BY then no rows. Only COUNT returns a number for no results
MAX/SUM等会返回NULL，除非你添加组，否则没有行。只有COUNT返回一个没有结果的数字
Edit, a bit late: SUM would return NULL like MAX
编辑，有点晚:SUM将返回NULL，就像MAX一样
Edit, May 2013: this applies to all main RDBMS. I guess as per the ANSI standard
编辑，2013年5月:这适用于所有主要的RDBMS。我猜是按照ANSI标准
#2
8
Yes, the return value of the "count" function itself is always a non-null integer, without exception.
是的，“count”函数本身的返回值总是一个非空整数，没有例外。
Having said that, you may be more interested in the return value of your query, which is actually a result set. If that's the case, then you simply have to consider whether your query includes a "group by" clause.
话虽如此，您可能对查询的返回值更感兴趣，它实际上是一个结果集。
A non-grouped query with aggregate functions like
具有聚合函数的非分组查询
select count(*), sum(*), max(*), min(*) from table
produces a result set with a single record containing the return value(s) of the aggregate function(s). In this case, your result set will always have exactly one record, with columns containing the return values of whatever aggregate functions you included. Within this lone record, the return value of count will always be a non-null integer, while the return values of the other aggregate functions such as min, max, and sum may be null. In the above example, your returned record may be "zero, null, null, null", but will never be "null,null,null,null", since count never returns null. So if you're calling only "count", then that one column in that one record returned will always contain the non-null return value of count; hence you can rest assured that the scalar return value of your count query will always be a non-null integer.
生成一个结果集，其中包含聚合函数的返回值。在这种情况下，您的结果集总是只有一个记录，包含包含您所包含的任何聚合函数的返回值的列。在这个唯一的记录中，count的返回值总是一个非空整数，而其他聚合函数(如min、max和sum)的返回值可能为空。在上面的示例中，返回的记录可能是“0、null、null、null”，但是不会是“null、null、null、null”，因为count从不返回null。因此，如果只调用“count”，那么返回的一个记录中的一个列将始终包含count的非空返回值;因此您可以放心，count查询的标量返回值始终是一个非空整数。
On the other hand, a grouped query with aggregate functions like
另一方面，具有聚合函数的分组查询
select count(*), sum(*), max(*), min(*) from table group by column
produces a result set with zero or more records where each record contains the return value of the aggregate function(s) for each group identified. In this case, if there are no groups identified, there are no groups for the aggregate functions to run on, the functions are never run, and the "query return value" will be an empty set with no records at all.
生成一个结果集，其中每个记录包含标识的每个组的聚合函数的返回值。在这种情况下，如果没有标识的组，则没有用于运行聚合函数的组，也不会运行函数，并且“查询返回值”将是一个空集，根本没有记录。
Test it out on a table with no records to see the results:
在没有记录的桌子上进行测试，看看结果如何:
create table #t (c int);
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t; --returns one record {0, null, null, null}
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t group by c; --returns no records
drop table #t;
So whether you're calling count, min, max, or sum, it is important to understand not only the return values of individual aggregate functions, but also the "return value" of the query itself, and what that result set contains.
因此，无论您是调用count、min、max还是sum，重要的是不仅要理解单个聚合函数的返回值，还要理解查询本身的“返回值”以及查询结果集包含的内容。
#3
5
Aggregate function Count() always returns value
聚合函数Count()总是返回值
#4
2
Yes it will return a numeric always
是的，它总是返回一个数字
#5
2
Yes, it'll return 0 in such cases.
是的，在这种情况下它会返回0。
#6
2
There will always be a row of result like this:
总会有一排这样的结果:
| COUNT(*) |
------------
| 0 |
if there are no matches.
如果没有匹配。
By the way, I would prefer to count only the primary key instead of *.
顺便说一下，我宁愿只计算主键而不计算*。
#7
2
if no record is matched the count will return 0. (so yes, count always returns a result, unless you have some syntax error)
如果没有匹配的记录，计数将返回0。(是的，count总是返回一个结果，除非有语法错误)