- 论坛徽章:
- 0
|
不知道大家有没有注意这个问题，平时都怎么写这个query
看完这里，一句话，使用count(*)，，
By Rudy Limeback, SQL Consultant, r937.com
SearchOracle.com
Digg This Stumble Delicious
How do you specify difference between count(*) and count(1) in a select statement? Which is best to use and when?
The difference is simple: COUNT(*) counts the number of rows produced by the query, whereas COUNT(1) counts the number of 1 values. Note that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause. This also applies to literals in aggregate functions, such as COUNT(1). It's hard to imagine a scenario where the COUNT(*) and COUNT(1) values would be different, so please do let me know if you find one.
In general, you should always use COUNT(*). This is because the database can often count rows by accessing an index, which is much faster than accessing a table. If you use COUNT(column), the database must actually inspect the individual values in the column, since it will not count NULLs. Aggregate functions like COUNT and SUM always ignore NULLs.
One would like to assume that the database optimizer is smart enough to realize that the literal value 1 will never be NULL, and not need to inspect it on every row, but it also would not surprise me to find out that the actions of appending the 1s into every row produced by the FROM clause and counting the 1s are handled by two separate logic modules, and maybe the module doing the counting doesn't know where the 1s came from. So just use COUNT(*).
17 Jan 2007 |
|
免费注册
发表于 2010-10-13 16:29
