sql - COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? -

this question has answer here:

• count(*) vs count(1) 9 answers

i find these 3 variants:

select count(*) foo; select count(1) foo; select count(primarykey) foo; 

as far can see, same thing, , find myself using 3 in codebase. however, don't same thing different ways. 1 should stick? 1 of them better 2 others?

bottom line

use either count(field) or count(*), , stick consistently, , if database allows count(tablehere) or count(tablehere.*), use that.

in short, don't use count(1) anything. it's one-trick pony, want, , in rare cases equivalent count(*)

use count(*) counting

use * queries need count everything, joins, use *

select boss.boss_id, count(subordinate.*) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

but don't use count(*) left joins, return 1 if subordinate table doesn't match parent table

select boss.boss_id, count(*) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

don't fooled advising when using * in count, fetches entire row table, saying * slow. * on select count(*) , select * has no bearing each other, entirely different thing, share common token, i.e. *.

an alternate syntax

in fact, if not permitted name field same table name, rdbms language designer give count(tablenamehere) same semantics count(*). example:

for counting rows have this:

select count(emp) emp 

and make simpler:

select count() emp 

and left joins, have this:

select boss.boss_id, count(subordinate) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

but cannot (count(tablenamehere)) since sql standard permits naming field same name table name:

create table fruit -- orm-friendly name ( fruit_id int not null, fruit varchar(50), /* same name table name,                  , let's say, forgot put not null */ shape varchar(50) not null, color varchar(50) not null ) 

counting null

and also, not practice make field nullable if name matches table name. have values 'banana', 'apple', null, 'pears' on fruit field. not count rows, yield 3, not 4

select count(fruit) fruit 

though rdbms sort of principle (for counting table's rows, accepts table name count's parameter), work in postgresql (if there no subordinate field in of 2 tables below, i.e. long there no name conflict between field name , table name):

select boss.boss_id, count(subordinate) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

but cause confusion later if add subordinate field in table, count field(which nullable), not table rows.

so on safe side, use:

select boss.boss_id, count(subordinate.*) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

count(1): one-trick pony

in particular count(1), one-trick pony, works on 1 table query:

select count(1) tbl 

but when use joins, trick won't work on multi-table queries without semantics being confused, , in particular cannot write:

-- count subordinates belongs boss select boss.boss_id, count(subordinate.1) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

so what's meaning of count(1) here?

select boss.boss_id, count(1) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

is this...?

-- counting subordinates select boss.boss_id, count(subordinate.boss_id) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

or this...?

-- or count(1) count 1 boss regardless if boss has subordinate select boss.boss_id, count(*) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

by careful thought, can infer count(1) same count(*), regardless of type of join. left joins result, cannot mold count(1) work as: count(subordinate.boss_id), count(subordinate.*)

so use either of following:

-- count subordinates belongs boss select boss.boss_id, count(subordinate.boss_id) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

works on postgresql, it's clear want count cardinality of set

-- count subordinates belongs boss select boss.boss_id, count(subordinate.*) boss left join subordinate on subordinate.boss_id = boss.boss_id group boss.id 

another way count cardinality of set, english-like (just don't make column name same table name) : http://www.sqlfiddle.com/#!1/98515/7

select boss.boss_name, count(subordinate) boss left join subordinate on subordinate.boss_code = boss.boss_code group boss.boss_name 

you cannot this: http://www.sqlfiddle.com/#!1/98515/8

select boss.boss_name, count(subordinate.1) boss left join subordinate on subordinate.boss_code = boss.boss_code group boss.boss_name 

you can this, produces wrong result: http://www.sqlfiddle.com/#!1/98515/9

select boss.boss_name, count(1) boss left join subordinate on subordinate.boss_code = boss.boss_code group boss.boss_name