COUNT vs COUNT 1 vs COUNT pk which is better

Question

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

User · Answer

Two of them always produce the same answer:

COUNT(*) counts the number of rows
COUNT(1) also counts the number of rows

Assuming the pk is a primary key and that no nulls are allowed in the values, then

COUNT(pk) also counts the number of rows

However, if pk is not constrained to be not null, then it produces a different answer:

COUNT(possibly_null) counts the number of rows with non-null values in the column possibly_null.
COUNT(DISTINCT pk) also counts the number of rows (because a primary key does not allow duplicates).
COUNT(DISTINCT possibly_null_or_dup) counts the number of distinct non-null values in the column possibly_null_or_dup.
COUNT(DISTINCT possibly_duplicated) counts the number of distinct (necessarily non-null) values in the column possibly_duplicated when that has the NOT NULL clause on it.

Normally, I write COUNT(*); it is the original recommended notation for SQL. Similarly, with the EXISTS clause, I normally write WHERE EXISTS(SELECT * FROM ...) because that was the original recommend notation. There should be no benefit to the alternatives; the optimizer should see through the more obscure notations.

User · Answer

I feel the performance characteristics change from one DBMS to another. It's all on how they choose to implement it. Since I have worked extensively on Oracle, I'll tell from that perspective.

COUNT(*) - Fetches entire row into result set before passing on to the count function, count function will aggregate 1 if the row is not null

COUNT(1) - Will not fetch any row, instead count is called with a constant value of 1 for each row in the table when the WHERE matches.

COUNT(PK) - The PK in Oracle is indexed. This means Oracle has to read only the index. Normally one row in the index B+ tree is many times smaller than the actual row. So considering the disk IOPS rate, Oracle can fetch many times more rows from Index with a single block transfer as compared to entire row. This leads to higher throughput of the query.

From this you can see the first count is the slowest and the last count is the fastest in Oracle.

User · Answer

Asked and answered before     Books on line says  COUNT         ALL   DISTINCT   expression              1  is a non-null expression so it s the same as COUNT     The optimiser recognises it as trivial so gives the same plan  A PK is unique and non-null  in SQL Server at least  so COUNT PK    COUNT     This is a similar myth to EXISTS  SELECT       or EXISTS  SELECT 1      And see the ANSI 92 spec  section 6 5  General Rules  case 1          a  If COUNT    is specified  then the result is the cardinality           of T           b  Otherwise  let TX be the single-column table that is the           result of applying the  lt value expression gt  to each row of T           and eliminating null values  If one or more null values are           eliminated  then a completion condition is raised  warning-           null value eliminated in set function

User · Answer

At least on Oracle they are all the same  http   www oracledba co uk tips count speed htm

User · Answer

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  It s a one-trick pony  which rarely does what you want  and in those rare cases is equivalent to count     Use count    for counting  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      An alternate syntax  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     Counting with null  And also  it is not a good practice to make a field nullable if its name matches the table name  Say you have values  Banana    Apple   NULL   Pears  on fruit field  This will not count all rows  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   count 1   The one-trick pony  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   By careful thought  you can infer 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

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

The answer is

Examples related to sql

Examples related to select

Examples related to count

Tags