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


#1

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.