在 SQL 中，count(column) 和 count(*) 之间有什么区别？

In SQL, what's the difference between count(column) and count(*)?

提问by Bill the Lizard

I have the following query:

我有以下查询：

select column_name, count(column_name)
from table
group by column_name
having count(column_name) > 1;

What would be the difference if I replaced all calls to count(column_name)to count(*)?

如果我替换了所有对count(column_name)to 的调用，会有什么不同count(*)？

This question was inspired by How do I find duplicate values in a table in Oracle?.

这个问题的灵感来自如何在 Oracle 的表中找到重复的值？.

---

---

To clarify the accepted answer (and maybe my question), replacing count(column_name)with count(*)would return an extra row in the result that contains a nulland the count of nullvalues in the column.

为了澄清已接受的答案（也许是我的问题），替换count(column_name)withcount(*)将在结果中返回一个额外的行，其中包含 anull和null列中的值计数。

回答by SQLMenace

count(*)counts NULLs and count(column)does not

count(*)计算 NULL 并且count(column)不

[edit] added this code so that people can run it

[编辑] 添加此代码以便人们可以运行它

create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)

select count(*),count(id),count(id2)
from #bla

results 7 3 2

结果 7 3 2

回答by Brannon

Another minor difference, between using * and a specific column, is that in the column case you can add the keyword DISTINCT, and restrict the count to distinct values:

使用 * 和特定列之间的另一个小区别是，在列的情况下，您可以添加关键字 DISTINCT，并将计数限制为不同的值：

select column_a, count(distinct column_b)
from table
group by column_a
having count(distinct column_b) > 1;

回答by Alan

A further and perhaps subtle difference is that in some database implementations the count(*) is computed by looking at the indexes on the table in question rather than the actual data rows. Since no specific column is specified, there is no need to bother with the actual rows and their values (as there would be if you counted a specific column). Allowing the database to use the index data can be significantly faster than making it count "real" rows.

另一个可能是细微的区别是，在某些数据库实现中，count(*) 是通过查看相关表上的索引而不是实际数据行来计算的。由于没有指定特定的列，因此无需理会实际的行及其值（如果您计算特定的列就会如此）。允许数据库使用索引数据比让它计算“真实”行要快得多。

回答by Bill the Lizard

We can use the Stack Exchange Data Explorerto illustrate the difference with a simple query. The Users table in Stack Overflow's database has columns that are often left blank, like the user's Website URL.

我们可以使用Stack Exchange Data Explorer通过一个简单的查询来说明差异。Stack Overflow 数据库中的 Users 表有一些列通常是空白的，比如用户的网站 URL。

-- count(column_name) vs. count(*)
-- Illustrates the difference between counting a column
-- that can hold null values, a  'not null' column, and  count(*)

select count(WebsiteUrl), count(Id), count(*) from Users

If you run the query above in the Data Explorer, you'll see that the count is the same for count(Id)and count(*)because the Idcolumn doesn't allow nullvalues. The WebsiteUrlcount is much lower, though, because that column allows null.

如果您在数据资源管理器中运行上面的查询，您将看到计数是相同的count(Id)，count(*)因为该Id列不允许null值。该WebsiteUrl数要低得多，但是，因为该列允许null。

回答by Peter C

The explanation in the docs, helps to explain this:

文档中的解释有助于解释这一点：

COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(*) 返回组中的项目数，包括 NULL 值和重复项。

COUNT(expression) 为组中的每一行计算表达式并返回非空值的数量。

So count(*) includes nulls, the other method doesn't.

所以 count(*) 包含空值，而另一种方法不包含。

回答by Ahmedul Kabir

Basically the COUNT(*)function return all the rows from a table whereas COUNT(COLUMN_NAME)does not; that is it excludes null values which everyone here have also answered here. But the most interesting part is to make queries and database optimized it is better to use COUNT(*)unless doing multiple counts or a complex query rather than COUNT(COLUMN_NAME). Otherwise, it will really lower your DB performance while dealing with a huge number of data.

基本上，该COUNT(*)函数返回表中的所有行，而COUNT(COLUMN_NAME)不会；也就是说，它排除了这里的每个人也在这里回答的空值。但最有趣的部分是对查询和数据库进行优化，COUNT(*)除非进行多次计数或复杂查询，否则最好使用而不是COUNT(COLUMN_NAME). 否则，它会在处理大量数据时真正降低您的数据库性能。

回答by Arun Solomon

COUNT(*)– Returns the total number of records in a table (Including NULL valued records).

COUNT(*)– 返回表中的记录总数（包括 NULL 值记录）。

COUNT(Column Name)?–?Returns the total number of Non-NULL records. It means that, it ignores counting NULL valued records in that particular column.

COUNT(Column Name)?–? 返回非NULL 记录的总数。这意味着，它会忽略对该特定列中的 NULL 值记录进行计数。

回答by G21

• The COUNT(*) sentence indicates SQL Server to return all the rows from a table, including NULLs.
• COUNT(column_name) just retrieves the rows having a non-null value on the rows.

• COUNT(*) 语句指示 SQL Server 返回表中的所有行，包括 NULL。
• COUNT(column_name) 只检索行上具有非空值的行。

Please see following code for test executions SQL Server 2008:

请参阅以下代码以执行 SQL Server 2008 的测试：

-- Variable table
DECLARE @Table TABLE
(
      CustomerId int NULL 
    , Name nvarchar(50) NULL
)

-- Insert some records for tests
INSERT INTO @Table VALUES( NULL, 'Pedro')
INSERT INTO @Table VALUES( 1, 'Juan')
INSERT INTO @Table VALUES( 2, 'Pablo')
INSERT INTO @Table VALUES( 3, 'Marcelo')
INSERT INTO @Table VALUES( NULL, 'Leonardo')
INSERT INTO @Table VALUES( 4, 'Ignacio')

-- Get all the collumns by indicating *
SELECT  COUNT(*) AS 'AllRowsCount'
FROM    @Table

-- Get only content columns ( exluce NULLs )
SELECT  COUNT(CustomerId) AS 'OnlyNotNullCounts'
FROM    @Table

回答by Hiren gardhariya

There is no difference if one column is fix in your table, if you want to use more than one column than you have to specify that how much columns you required to count......

如果表中的一列是固定的，则没有区别，如果要使用多于一列，则必须指定需要计算多少列......

Thanks,

谢谢，

回答by Ali Adravi

It is best to use

最好使用

Count(1) in place of column name or * 

to count the number of rows in a table, it is faster than any format because it never go to check the column name into table exists or not

计算表中的行数，它比任何格式都快，因为它从不检查表中的列名是否存在