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:
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.
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.
@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
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(*):
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().
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).