Differences Between count(1), count(*), and count(column) in SQL

This article discusses how the three common SQL counting expressions— count(1) , count(*) , and count(column) —behave differently in terms of result semantics and execution performance.

1. count(1) vs. count(*) Both expressions return the total number of rows, but count(1) may appear slightly faster on small tables (under 10,000 rows) after table analysis. In practice the optimizer treats them the same, and the difference is negligible.

2. count(1) vs. count(column) count(1) counts every row, including those where the column value is NULL . In contrast, count(column) only counts rows where the specified column is not NULL , effectively ignoring null values.

3. count(*) , count(1) and count(column) – functional and performance comparison count(*) counts all rows regardless of column values, similar to count(1) . count(column) counts only non‑null values of that column. Performance varies: if the column is a primary key, count(column) is fastest; if it is not a primary key, count(1) tends to be faster; with a single‑column table, count(*) is optimal.

4. Example analysis

Below is a MySQL session that creates a test table, inserts sample data (including NULL and empty strings), and runs several COUNT queries to illustrate the differences.

mysql> create table counttest(name char(1), age char(2));
mysql> insert into counttest values
    ('a','14'),('a','15'),('a','15'),
    ('b',NULL),('b','16'),
    ('c','17'),('d',NULL),
    ('e','');
mysql> select * from counttest;
+------+------+
| name | age  |
+------+------+
| a    | 14   |
| a    | 15   |
| a    | 15   |
| b    | NULL |
| b    | 16   |
| c    | 17   |
| d    | NULL |
| e    |      |
+------+------+

mysql> select name, count(name), count(1), count(*), count(age), count(distinct age)
       from counttest group by name;
+------+-------------+----------+----------+------------+----------------------+
| name | count(name) | count(1) | count(*) | count(age) | count(distinct age) |
+------+-------------+----------+----------+------------+----------------------+
| a    | 3           | 3        | 3        | 3          | 2                    |
| b    | 2           | 2        | 2        | 1          | 1                    |
| c    | 1           | 1        | 1        | 1          | 1                    |
| d    | 1           | 1        | 1        | 0          | 0                    |
| e    | 1           | 1        | 1        | 1          | 1                    |
+------+-------------+----------+----------+------------+----------------------+

The results confirm that count(1) and count(*) return the same row count, while count(column) excludes rows where the column is NULL . The choice of counting method should consider both the presence of nulls and the indexing of the column for optimal performance.