COUNT(*) 总是返回结果吗？

发布于 2024-08-27 05:23:35 字数 148 浏览 7 评论 0原文

如果我运行一个查询，例如：

SELECT COUNT(*) as num FROM table WHERE x = 'y'

即使查询与任何记录都不匹配，它是否总是返回结果？或者我是否需要验证并确保返回一行作为结果？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情话墙 2024-09-03 05:23:35

是的，因为它是一个聚合并且返回零。
除非您添加 GROUP BY，在这种情况下没有结果，因为没有组...

MAX/SUM 等将返回 NULL，除非您添加 GROUP BY 然后没有行。
只有 COUNT 返回没有结果的数字

编辑，有点晚了：SUM 将像 MAX 一样返回 NULL

，2013 年 5 月编辑：这适用于所有主要 RDBMS。我猜按照 ANSI 标准

回复收藏 0 原文

三生一梦 2024-09-03 05:23:35

是的，“count”函数本身的返回值始终是非空整数，无一例外。

话虽如此，您可能对查询的返回值更感兴趣，它实际上是一个结果集。如果是这种情况，那么您只需考虑您的查询是否包含“group by”子句。

具有聚合函数（如）的非分组查询

select count(*), sum(*), max(*), min(*) from table

会生成一个包含单个记录的结果集，其中包含聚合函数的返回值。在这种情况下，您的结果集将始终只有一条记录，其中的列包含您所包含的任何聚合函数的返回值。在此单独的记录中，count 的返回值始终为非空整数，而其他聚合函数（例如 min、max 和 sum）的返回值可能为 null。在上面的示例中，返回的记录可能是“zero, null, null, null”，但永远不会是“null,null,null,null”，因为 count 永远不会返回 null。因此，如果您仅调用“count”，则返回的一条记录中的一列将始终包含 count 的非空返回值；因此，您可以放心，计数查询的标量返回值将始终是非空整数。

另一方面，具有聚合函数（如

select count(*), sum(*), max(*), min(*) from table group by column

）的分组查询会生成包含零个或多个记录的结果集，其中每条记录包含以下返回值已识别的每个组的聚合函数。在这种情况下，如果没有识别出组，则没有可供运行聚合函数的组，函数永远不会运行，并且“查询返回值”将是一个空集，根本没有记录。

在没有记录的表上进行测试以查看结果：

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;

因此，无论您是调用 count、min、max 还是 sum，重要的是不仅要了解各个聚合函数的返回值，还要了解“返回值”查询本身的“，以及结果集包含的内容。

Yes, the return value of the "count" function itself is always a non-null integer, without exception.

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.

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.

回复收藏 0 原文