What's the difference between COUNT(1), COUNT(*), and COUNT(column_name)?

Question

What’s the difference between COUNT(1), COUNT(*), and COUNT(column_name)?

Answer

It’s important to note that depending on the ‘flavor’ of SQL you are using (MySQL, SQLite, SQL Server, etc.), there may be very slight differences in performance between COUNT(1) and COUNT(*), but generally speaking COUNT(1) and COUNT(*) will both return the number of rows that match the condition specified in your query.

As for COUNT(column_name), this statement will return the number of rows that have a non-null value for the specified column.

Let’s say we have the following table called people:

image

When we run either of these queries:

SELECT COUNT(1) FROM people;
SELECT COUNT(*) FROM people;

we’re going to get a result of 3 because there are three rows in the table. But If we run this query:

SELECT COUNT(favorite_color) FROM people;

we will get a result of 2 because the third row contains a value of NULL for favorite_color, therefore that row does not get counted.

15 Likes

I noticed it’s also possible to use count() instead of count(*)
Is there any difference?

3 Likes

It may take some digging, but my guess would be that count(*) does not include null rows.

It may be possible but its not documented anywhere that I`ve been able to find, and I’d guess therefore best avoided as likely to return unpredictable results.

1 Like

This answer should be part of the main content of SQL curriculum, when COUNT() is introduced here.

I see so many people in this Forum (including myself) get confused about the difference between COUNT(*) and COUNT(column_name).

3 Likes

@factoradic may be able to shed some light on this and prompt the curriculum team to consider your suggestion. Thanks for posting it.

2 Likes

@masakudamatsu I am sorry, I don’t understand the problem. What information is missing in the instructions of this exercise? Or maybe the instructions should be rephrased? This is na honest question, I simply do not see what is missing.

I will be happy to pass this on, but there are a few fields in the bug report that I have to fill, so I need a bit of help :slight_smile:


Sorry for digging this out, but I was curious and I had to check.

We can precede the statement with the keyword EXPLAIN, this will return information about how the SQL statement would be executed (read more in the linked doc if that sounds interesting!). So I decided to check if there is any difference between count() and count(*):

> EXPLAIN SELECT COUNT(*) FROM artists

addr	opcode		p1	p2	p3	p4	p5	comment
0		Init		0	7	0		00	
1		OpenRead	1	4	0	1	00	
2		Count		1	1	0		00	
3		Close		1	0	0		00	
4		Copy		1	2	0		00	
5		ResultRow	2	1	0		00	
6		Halt		0	0	0		00	
7		Transaction	0	0	34	0	01	
8		Goto		0	1	0		00	
> EXPLAIN SELECT COUNT() FROM artists

addr	opcode		p1	p2	p3	p4	p5	comment
0		Init		0	7	0		00	
1		OpenRead	1	4	0	1	00	
2		Count		1	1	0		00	
3		Close		1	0	0		00	
4		Copy		1	2	0		00	
5		ResultRow	2	1	0		00	
6		Halt		0	0	0		00	
7		Transaction	0	0	34	0	01	
8		Goto		0	1	0		00	

Exactly the same output. This means that these two queries will always give the same results. But as @patrickd314 cleverly pointed out - this is not documented, so it might be changed in the future versions of the SQLite. It is advisable not to use COUNT().

10 Likes

thanks for checking!

2 Likes

What’s missing is the example of COUNT(column_name). The lesson only gives us an example of COUNT(*).

The lesson in its current form gives us an incorrect impression that COUNT(*) “counts the number of non-empty values in that column” or COUNT(column_name) will “count every row”. I know this is the misunderstanding of what’s written. But for the beginners who have no idea what COUNT() is, the sentence

COUNT() is a function that takes the name of a column as an argument and counts the number of non-empty values in that column.

makes us expect some code example of this sentence. But the example actually given is a different thing to learn, that is COUNT(*). That surely confuses beginners (and it did to me).

Am I clear enough now?

2 Likes

Thank you very much for this explanation since I did not know the difference between

COUNT(*)
COUNT(COLUMN_NAME)