count(*) and count(column_name), what's the diff?

mysql

Solution

- `COUNT(*)` counts all rows in the result set (or group if using GROUP BY).

- `COUNT(column_name)` only counts those rows where `column_name` is NOT NULL. This may be slower in some situations even if there are no NULL values because the value has to be checked (unless the column is not nullable).

- `COUNT(1)` is the same as `COUNT(*)` since 1 can never be NULL.

To see the difference in the results you can try this little experiment:

CREATE TABLE table1 (x INT NULL);
INSERT INTO table1 (x) VALUES (1), (2), (NULL);
SELECT
    COUNT(*) AS a,
    COUNT(x) AS b,
    COUNT(1) AS c
FROM table1;

Result:

a   b   c
3   2   3

Problem

Original source