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

Question

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(*)?

This question was inspired by How do I find duplicate values in a table in 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 null and the count of null values in the column.

score 252 · Accepted Answer · edited Sep 11 '11 at 12:50

252

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

[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

edited Sep 11 '11 at 12:50

Vishwanath Dalvi

35,388
41
123
155

answered Sep 12 '08 at 15:28

SQLMenace

132,095
25
206
225

9

Just curious: if you have a row with _all_ NULLs, would count(*) still count it, or is just count(column) for all columns? – Joel Coehoorn Sep 12 '08 at 15:29
7

Is this standard accross DBMSs? – Eclipse Jan 23 '09 at 20:41
DB2 v9 is issuing a warning: SQLSTATE 01003: Null values were eliminated from the argument of a column function – Boune Jun 17 '09 at 15:31
57

It's worth mentioning that if you have a non-nullable column such as ID, then count(ID) will significantly improve performance over count(*). – tsilb Aug 25 '09 at 18:06
13

@tsilb: The answer posted by @Alan states "count(*) is computed by looking at the indexes on the table in question rather than the actual data rows" which, if true, invalidates your comment. I appreciate that @Alan may be wrong but I'm interested in the source of your information in order to find out which is correct. – Tony Apr 06 '10 at 20:55
13

@tsilb: Many modern query optimizers will optimize count(*) to use indexes when it makes sense to. – Shannon Severance Aug 20 '10 at 03:42
2

What is interesting is that this results in count(DISTINCT column name) counting only non-null distinct values, whereas SELECT DISTINCT also returns a null row if there is one or more null values. – Aaron Silverman Mar 24 '11 at 15:49
2

@SQLMenace What if i use count(1)? Where does that leave me? – renegadeMind Mar 01 '12 at 21:53
@renegadeMind: if your target is just to check the table is not empty while looking for specific conditions you can do this `if Exists (Select 1 from sys.tables where name = 'SalesChannel_PartyRole')` – Michael Bahig Aug 07 '12 at 10:58
3

The ought to be no improvement in using count(column_name) over count(\*) where column_name is not null. any benefit would come from using an index scan instead of a table scan, but the optimiser can choose to do so for count(*) anyway. In Oracle this has been the case for a very long time. – David Aldridge Apr 04 '13 at 13:12
1

It's not correct to say that "count(\*) counts nulls" -- the presence or absence of nulls is irrelevant as count(\*) counts the number of rows regardless of their content. – David Aldridge Apr 04 '13 at 13:13

Brannon · Answer 2 · 2008-09-12T22:32:12.347

37

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:

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

edited Sep 12 '08 at 22:32

answered Sep 12 '08 at 15:47

Brannon

25,687
5
39
44

2

Should the group by column and the one being counted be different? otherwise you would get nothing from this query – steevc Sep 12 '08 at 16:06
Yes, sorry.. I hadn't noticed that they were the same column in the example. I'll update the post. – Brannon Sep 12 '08 at 22:31
The code is plain useless. `select column_a, count(distinct column_a) from table group by column_a having count(distinct column_a) > 1;` makes it meaningful. – Payel Senapati Aug 16 '21 at 07:18

score 19 · Answer 3 · answered Sep 14 '08 at 03:13

19

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.

answered Sep 14 '08 at 03:13

Alan

3,815
1
26
35

5

+1 Yes, certainly true for Oracle, and for PostgreSQL from 9.2 onwards. – David Aldridge Apr 04 '13 at 13:18
@DavidAldridge Can you provider a pointer to documentation (especially for postgresql) where this is mentioned? Thanks. – Bhushan Apr 17 '19 at 02:08
1

@Bhushan here's you go https://wiki.postgresql.org/wiki/Index-only_scans – David Aldridge Apr 17 '19 at 08:00

Peter C · Answer 4 · 2021-08-25T10:20:16.327

13

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.

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

edited Aug 25 '21 at 10:20

answered Sep 12 '08 at 15:41

Peter C

2,257
2
25
28

For SQL newbs: To what help file are you referring? – Bill the Lizard Jul 15 '09 at 15:33

score 11 · Answer 5 · edited Aug 29 '17 at 13:14

We can use the Stack Exchange Data Explorer to 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.

-- 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 Id column doesn't allow null values. The WebsiteUrl count is much lower, though, because that column allows null.

score 2 · Answer 6 · edited Aug 02 '19 at 09:54

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.

score 2 · Answer 7 · answered Mar 24 '19 at 19:14

2

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

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

answered Mar 24 '19 at 19:14

Arun Solomon

421
3
13

score 2 · Answer 8 · edited Feb 16 '12 at 19:58

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.

Please see following code for test executions 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

score 1 · Answer 9 · answered Aug 16 '21 at 07:41

Further elaborating upon the answer given by @SQLMeance and @Brannon making use of GROUP BY clause which has been mentioned by OP but not present in answer by @SQLMenace

CREATE TABLE table1 ( 
id INT 
);

INSERT INTO table1 VALUES 
(1), 
(2), 
(NULL), 
(2), 
(NULL), 
(3), 
(1), 
(4), 
(NULL), 
(2);

SELECT * FROM table1;

+------+
| id   |
+------+
|    1 |
|    2 |
| NULL |
|    2 |
| NULL |
|    3 |
|    1 |
|    4 |
| NULL |
|    2 |
+------+
10 rows in set (0.00 sec)

SELECT id, COUNT(*) FROM table1 GROUP BY id;

+------+----------+
| id   | COUNT(*) |
+------+----------+
|    1 |        2 |
|    2 |        3 |
| NULL |        3 |
|    3 |        1 |
|    4 |        1 |
+------+----------+
5 rows in set (0.00 sec)

Here, COUNT(*) counts the number of occurrences of each type of id including NULL

SELECT id, COUNT(id) FROM table1 GROUP BY id;

+------+-----------+
| id   | COUNT(id) |
+------+-----------+
|    1 |         2 |
|    2 |         3 |
| NULL |         0 |
|    3 |         1 |
|    4 |         1 |
+------+-----------+
5 rows in set (0.00 sec)

Here, COUNT(id) counts the number of occurrences of each type of id but does not count the number of occurrences of NULL

SELECT id, COUNT(DISTINCT id) FROM table1 GROUP BY id;

+------+--------------------+
| id   | COUNT(DISTINCT id) |
+------+--------------------+
| NULL |                  0 |
|    1 |                  1 |
|    2 |                  1 |
|    3 |                  1 |
|    4 |                  1 |
+------+--------------------+
5 rows in set (0.00 sec)

Here, COUNT(DISTINCT id) counts the number of occurrences of each type of id only once (does not count duplicates) and also does not count the number of occurrences of NULL

Ali Adravi · Answer 10 · 2012-06-07T19:50:14.187

-1

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

edited Jun 07 '12 at 19:50

answered Feb 07 '11 at 19:12

Ali Adravi

21,707
9
87
85

4

Incorrect for Oracle at least, and for other RDBMS's too I suspect. Internally count(1) is transformed to count\(*). In particular, performance of count(\*) is not adversely affected by the size of the rows, which is a common misconception. – David Aldridge Apr 04 '13 at 13:17
This is true for SQL Server. As @Ali Adravi said, `COUNT(*)` in comparison with `COUNT(columnName)` will not go to check column value, because it just enumerate rows. But `COUNT(columnName)` is slower even the `count` applied on an `id` column! At least in SQL Server, of course. – ABS Jul 25 '17 at 08:48

Hiren gardhariya · Answer 11 · 2012-10-29T04:44:31.117

-2

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,

edited Oct 29 '12 at 04:44

answered Sep 21 '12 at 12:06

Hiren gardhariya

1,247
10
29

score -2 · Answer 12 · edited May 02 '16 at 17:05

-2

As mentioned in the previous answers, Count(*) counts even the NULL columns, whereas count(Columnname) counts only if the column has values.

It's always best practice to avoid * (Select *, count *, …)

edited May 02 '16 at 17:05

Paul Roub

36,322
27
84
93

answered May 02 '16 at 16:50

Unna

42
2

1

It's not at all a best practice to avoid `COUNT(*)` – David Faber Mar 21 '18 at 00:32

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

12 Answers12

Linked

Related