COUNT（*）与COUNT（1）与COUNT（pk）：这更好吗？ [英] COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

问题描述

我经常找到这三个变体：

  SELECT COUNT（*）FROM Foo; 
 SELECT COUNT（1）FROM Foo; 
 SELECT COUNT（PrimaryKey）FROM Foo; 
  

据我所知，他们都做同样的事情，在我的代码库。但是，我不喜欢做同样的事情不同的方式。我应该坚持哪一个？

解决方案

使用 * 对于所有需要计数所有查询，即使对于连接，使用 *

  SELECT boss.boss_id，COUNT（subordinate。*）
从boss 
 LEFT JOIN从属于subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  

但是对于LEFT联接不要使用 COUNT（*）因为即使下级表不匹配父表中的任何内容，它也将返回1

  SELECT boss.boss_id，COUNT（* ）
 FROM boss 
 LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  

不要被那些建议在COUNT中使用 * 的人愚弄，它会从你的表中获取整行， c $ c> * 很慢。 * 在 SELECT COUNT（*）和 SELECT * 彼此没有关系，他们是完全不同的东西，他们只是共享一个共同的标记，即 * 。实际上，如果不允许将字段命名为与其表名相同，则RDBMS语言设计器可以给予 COUNT（tableNameHere）与 COUNT（*）。示例：

对于计数行，我们可以使用：

  SELECT COUNT（emp）FROM emp 
  

它们可以使其更简单：

  SELECT COUNT（）FROM emp 
  

对于LEFT JOIN，我们可以有：

  SELECT boss.boss_id，COUNT（从属）
 
 LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  

但是他们不能这样做（ COUNT（tableNameHere）），因为SQL标准允许命名与其表名称相同名称的字段：

  CREATE TABLE fruit  -  ORM友好名称
（
 fruit_id int NOT NULL，
 fruit varchar（50） / *与表名相同，
，让我们说，有人忘了放NOT NULL * / 
 shape varchar（50）NOT NULL，
 color varchar（50）NOT NULL 
）
  

此外，将字段设置为可空也不是一个好习惯， '香蕉'，'苹果'，NULL，'梨'在水果场上。这不会计算所有的水果，它只会产生3，而不是4。

  SELECT count（fruit）FROM fruit 
虽然一些RDBMS做这样的原则（计数表的行，它接受表名为COUNT的参数），这个将在Postgresql中工作（如果下面两个表中任何一个表中没有下级字段，即只要字段名和表名之间没有名称冲突）：
  SELECT boss.boss_id，COUNT（从属）
 FROM boss 
 LEFT JOIN从属于subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
但这可能会导致混乱，如果我们将在表中添加下级字段，因为它将计算字段（可以为空），而不是表行。
 
 
 
因此，为了安全起见，请使用：
  SELECT boss.boss_id，COUNT（subordinate。*）
 FROM boss 
 LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
特别是 COUNT（1），它是一个单把小马，它只适用于一个表查询：
  SELECT COUNT（1）FROM tbl 
  
但是当你使用连接时，这种技巧不会对多表查询工作，而不会使它的语义混淆，特别是你不能写：
   - 计算属于boss的下属
 SELECT boss.boss_id，COUNT（subordinate.1）
 FROM boss 
 LEFT JOIN从属于subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
那么这里COUNT（1）的意思是什么？
  SELECT boss.boss_id，COUNT（1）
 
 LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
这是...吗？
   - 仅计数所有下级
 SELECT boss.boss_id， COUNT（subordinate.boss_id）
 FROM boss 
 LEFT JOIN从属于subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
还是这个...？
  COUNT（1）也将为老板计数1，不管老板是否有一个从属
 SELECT boss.boss_id，COUNT（*）
从老大
 LEFT JOIN从属于subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
虽然不难推断无论加入类型如何， COUNT（1）与 COUNT（*）相同。但是对于LEFT JOIN的结果，我们不能模 COUNT（1）来工作： COUNT（subordinate.boss_id）  COUNT（subordinate。*） 
 
 
 
因此，只需使用以下方法之一：
   - 计算属于老板的下属
 SELECT boss.boss_id，COUNT（subordinate.boss_id）
 FROM boss 
 LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
在Postgresql上，很明显你要计算集合的基数
   - 计算属于老板的下属
 SELECT boss.boss_id，COUNT（subordinate。*）
从boss 
 LEFT JOIN从属于subordinate.boss_id = boss.boss_id 
 GROUP BY boss.id 
  
另一种计算集合的基数的方法，非常类似英语表名称）： http://www.sqlfiddle.com/#!1/98515/ 7  
 选择boss.boss_name，count（从属）
 from boss 
 left join从属于subordinate.boss_code = boss.boss_code 
 group by boss.boss_name 
  
此： http://www.sqlfiddle.com/#!1/98515/8  
  select boss.boss_name，count（subordinate.1）
 from boss 
 left join从属于subordinate.boss_code = boss.boss_code 
 group by boss.boss_name 
  
但这会产生错误的结果： http://www.sqlfiddle.com/#！ 1/98515/9  
 选择boss.boss_name，count（1）
 from boss 
 left join subordinate on subordinate.boss_code = boss.boss_code 
 group by boss.boss_name 
  
  底线  
 
 
 
使用 COUNT（字段）或 COUNT（*），如果您的数据库允许 COUNT（tableHere） code> COUNT（tableHere。*），使用它。总之，不要使用 COUNT（1）来代替任何东西
 
I often find these three variants:
SELECT COUNT(*) FROM Foo;
SELECT COUNT(1) FROM Foo;
SELECT COUNT(PrimaryKey) FROM Foo;
As far as I can see, they all do the same thing, and I find myself using the three in my codebase. However, I don't like to do the same thing different ways. To which one should I stick? Is any one of them better than the two others?
解决方案
Use * for all your queries that need to count everything, even for joins, use *
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
But don't use COUNT(*) for LEFT joins, as that will return 1 even if the subordinate table doesn't match anything from parent table
SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Don't be fooled by those advising that when using * in COUNT, it fetches entire row from your table, saying that * is slow.  The * on SELECT COUNT(*) and SELECT * has no bearing to each other, they are entirely different thing, they just share a common token, i.e. *. In fact, if it is not permitted to name a field as same as its table name, RDBMS language designer could give COUNT(tableNameHere) the same semantics as COUNT(*).  Example:

For counting rows we could have this:
SELECT COUNT(emp) FROM emp
And they could make it simpler:
SELECT COUNT() FROM emp
And for LEFT JOINs, we could have this:
SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
But they cannot do that (COUNT(tableNameHere)) since SQL standard permits naming a field with the same name as its table name:
CREATE TABLE fruit -- ORM-friendly name
(
fruit_id int NOT NULL,
fruit varchar(50), /* same name as table name, 
                and let's say, someone forgot to put NOT NULL */
shape varchar(50) NOT NULL,
color varchar(50) NOT NULL
)
And also, it is not a good practice to make a field nullable, say you have values 'Banana', 'Apple', NULL, 'Pears' on fruit field. This will not count all fruits, it will only yield 3, not 4
SELECT count(fruit) FROM fruit
Though some RDBMS do that sort of principle(for counting the table's rows, it accepts table name as COUNT's parameter), this will work in Postgresql (if there is no subordinate field in any of the two tables below, i.e. as long as there is no name conflict between field name and table name):
SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
But that could cause confusion later if we will add a subordinate field in the table, as it will count the field(which could be nullable), not the table rows.

So to be on the safe side, use:
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
In particular to COUNT(1), it is a one-trick pony, it works well only on one table query:
SELECT COUNT(1) FROM tbl
But when you use joins, that trick won't work on multi-table queries without its semantics being confused, and in particular you cannot write:
-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
So what's the meaning of COUNT(1) here?
SELECT boss.boss_id, COUNT(1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Is it this...?
-- counting all the subordinates only
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Or this...?
-- or is that COUNT(1) will also count 1 for boss regardless if boss has a subordinate
SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Though it isn't hard to infer (though some could be confused) that COUNT(1) is the same as COUNT(*) regardless of type of join.  But for LEFT JOINs result, we cannot mold COUNT(1) to work as: COUNT(subordinate.boss_id), COUNT(subordinate.*)

So just use either of the following:
-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Works on Postgresql, it's clear that you want to count the cardinality of the set
-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id
Another way to count the cardinality of the set, very English-like (just don't make a column with a name same as its table name) : http://www.sqlfiddle.com/#!1/98515/7
select boss.boss_name, count(subordinate)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
You cannot do this: http://www.sqlfiddle.com/#!1/98515/8
select boss.boss_name, count(subordinate.1)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
You can do this, but this produces wrong result: http://www.sqlfiddle.com/#!1/98515/9
select boss.boss_name, count(1)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
Bottom Line

Use either COUNT(field) or COUNT(*), and stick with it consistently, and if your database allows COUNT(tableHere) or COUNT(tableHere.*), use that.  In short, don't use COUNT(1) for anything 

                        
这篇关于COUNT（*）与COUNT（1）与COUNT（pk）：这更好吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！