COUNT (*) vs. COUNT (1) vs. COUNT (pk): qual é melhor?

Eu geralmente encontro essas três variantes:

SELECT COUNT(*) FROM Foo; SELECT COUNT(1) FROM Foo; SELECT COUNT(PrimaryKey) FROM Foo; 

Tanto quanto eu posso ver, todos eles fazem a mesma coisa, e eu me encontro usando os três na minha base de código. No entanto, eu não gosto de fazer a mesma coisa de maneiras diferentes. A qual devo ficar? Qualquer um deles é melhor que os outros dois?

Linha de fundo

Use COUNT(field) ou COUNT(*) e COUNT(tableHere) com ele de forma consistente, e se o seu database permitir COUNT(tableHere) ou COUNT(tableHere.*) , Use-o.

Resumindo, não use COUNT(1) para nada. É um pônei de um truque, que raramente faz o que você quer, e nesses casos raros é equivalente a count(*)

Use count(*) para contar

Use * para todas as suas consultas que precisam contar tudo, mesmo para junções, use *

 SELECT boss.boss_id, COUNT(subordinate.*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id 

Mas não use COUNT(*) para associações LEFT, pois isso retornará 1 mesmo que a tabela subordinada não corresponda a nada da tabela pai

 SELECT boss.boss_id, COUNT(*) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id 

Não se deixe enganar por aqueles que aconselham que ao usar * em COUNT, ele busca toda a linha da sua tabela, dizendo que * é lento. O * em SELECT COUNT(*) e SELECT * não tem nenhum sentido um para o outro, eles são totalmente diferentes, eles apenas compartilham um token comum, ou seja, * .

Uma syntax alternativa

Na verdade, se não for permitido nomear um campo como o mesmo nome da tabela, o designer de idioma do RDBMS poderia dar a COUNT(tableNameHere) a mesma semântica que COUNT(*) . Exemplo:

Para contar as linhas, poderíamos ter isto:

 SELECT COUNT(emp) FROM emp 

E eles poderiam simplificar:

 SELECT COUNT() FROM emp 

E para o LEFT JOINs, podemos ter isto:

 SELECT boss.boss_id, COUNT(subordinate) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id 

Mas eles não podem fazer isso ( COUNT(tableNameHere) ), pois o padrão SQL permite nomear um campo com o mesmo nome que o nome da tabela:

 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 ) 

Contando com nulo

E também, não é uma boa prática tornar um campo anulável se seu nome corresponder ao nome da tabela. Digamos que você tenha valores ‘Banana’, ‘Apple’, NULL, ‘Pears’ no campo de fruit . Isso não contará todas as linhas, só produzirá 3, não 4

 SELECT count(fruit) FROM fruit 

Embora alguns RDBMS façam esse tipo de princípio (para contar as linhas da tabela, ele aceita o nome da tabela como parâmetro COUNT), isso funcionará no Postgresql (se não houver campo subordinate em nenhuma das duas tabelas abaixo, ou seja, enquanto houver nenhum conflito de nome entre o nome do campo e o nome da tabela):

 SELECT boss.boss_id, COUNT(subordinate) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id 

Mas isso pode causar confusão mais tarde se adicionarmos um campo subordinate na tabela, pois ele contará o campo (que pode ser anulável), não as linhas da tabela.

Então, para estar no lado seguro, 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) : O pônei de um truque

Em particular, para COUNT(1) , é um pônei de um truque , funciona bem apenas em uma consulta de tabela:

 SELECT COUNT(1) FROM tbl 

Mas quando você usa joins, esse truque não funciona em consultas de múltiplas tabelas sem que sua semântica seja confundida e, em particular, você não pode escrever:

 -- 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 

Então, qual é o significado de COUNT (1) aqui?

 SELECT boss.boss_id, COUNT(1) FROM boss LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id GROUP BY boss.id 

É isso…?

 -- 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 

Ou isto…?

 -- 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 

Com cuidado, você pode inferir que COUNT(1) é o mesmo que COUNT(*) , independentemente do tipo de associação. Mas, para o resultado LEFT JOINs, não podemos moldar COUNT(1) para funcionar como: COUNT(subordinate.boss_id) , COUNT(subordinate.*)

Então, basta usar um dos seguintes:

 -- 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 

Funciona no Postgresql, é claro que você quer contar a cardinalidade do conjunto

 -- 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 

Outra forma de contar a cardinalidade do conjunto, muito parecido com o inglês (não crie uma coluna com o mesmo nome que o nome da tabela): 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 

Você não pode fazer isso: 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 

Você pode fazer isso, mas isso produz um resultado errado: 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 

Dois deles sempre produzem a mesma resposta:

  • COUNT (*) conta o número de linhas
  • COUNT (1) também conta o número de linhas

Assumindo que o ‘ pk ‘ é uma chave primária e que nenhum nulo é permitido nos valores, então

  • COUNT (pk) também conta o número de linhas

No entanto, se ‘ pk ‘ não for restrito para não ser nulo, produzirá uma resposta diferente:

  • COUNT (possivelmente_null) conta o número de linhas com valores não nulos na coluna possibly_null .

  • COUNT (DISTINCT pk) também conta o número de linhas (porque uma chave primária não permite duplicatas).

  • COUNT (DISTINCT possibly_null_or_dup) conta o número de valores não nulos distintos na coluna possibly_null_or_dup .

  • COUNT (DISTINCT possivelmente_duplicated) conta o número de valores distintos (necessariamente não nulos) na coluna possibly_duplicated quando a cláusula NOT NULL está presente.

Normalmente escrevo COUNT(*) ; é a notação recomendada original para o SQL. Da mesma forma, com a cláusula EXISTS, eu normalmente escrevo WHERE EXISTS(SELECT * FROM ...) porque essa era a notação original recomendada. Não deve haver benefício para as alternativas; o otimizador deve ver através das notações mais obscuras.

Isso dependerá do tipo de database que você está usando, bem como do tipo de tabela em alguns casos.

Por exemplo, usando o MySQL, count(*) será rápido sob uma tabela MyISAM, mas lento sob um InnoDB. Sob InnoDB você deve usar count(1) ou count(pk) .

Perguntado e respondido antes …

Livros on-line dizem ” COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

“1” é uma expressão não nula, portanto, é igual a COUNT(*) . O otimizador reconhece isso como trivial, então dá o mesmo plano. Uma PK é exclusiva e não nula (pelo menos no SQL Server), portanto, COUNT(PK) = COUNT (*)

Este é um mito semelhante ao EXISTS (SELECT * ... ou EXISTS (SELECT 1 ...

E veja a especificação ANSI 92 , seção 6.5, Regras Gerais, caso 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  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. 

Pelo menos no Oracle eles são todos iguais: http://www.oracledba.co.uk/tips/count_speed.htm

Eu sinto o desempenho charecteristics muda de DBMS para DBMS. É tudo sobre como eles escolhem para implementá-lo. Desde que eu trabalhei extensivamente em oracle, vou dizer a partir dessa perspectiva.

COUNT (*) – Obtém a linha inteira no conjunto de resultados antes de passar para a function count, a function count agregará 1 se a linha não for nula

COUNT (1) – Não buscará nenhuma linha, em vez disso, count é chamado com um valor constante 1 para cada linha da tabela quando o campo corresponde.

Count (PK) – O PK no oracle é indexado. Isso significa que o Oracle precisa ler apenas o índice. Normalmente, uma linha no Index B + Tree é muitas vezes menor que a linha real. Portanto, considerando a taxa de IOPS do disco, o Oracle pode buscar muitas vezes mais linhas do Index com uma transferência de bloco único em comparação à linha inteira. Isso leva a um maior througput da consulta.

Com isso, você pode ver que a primeira contagem é a mais lenta e a última é a mais rápida no Oracle.