SQL COUNT(*) vs COUNT(column): The NULL Difference — sqlfmt

sqlfmt▍

SQL COUNT(*) vs COUNT(column): The NULL Difference — sqlfmt

Key takeaways

COUNT(*) counts result rows, including rows whose columns are NULL.
COUNT(column) counts only non-NULL values in the expression.
COUNT(DISTINCT column) counts unique non-NULL values.

What does each COUNT form return?

COUNT is an aggregate function: it returns one count for a result set or for each GROUP BY group. The important choice is whether SQL evaluates a nullable expression.

A five-row example

WITH visits (id, coupon_code) AS (
  VALUES (1, 'WELCOME'), (2, NULL), (3, 'WELCOME'), (4, 'SPRING'), (5, NULL)
)
SELECT COUNT(*) AS rows_seen,
       COUNT(coupon_code) AS rows_with_coupon,
       COUNT(DISTINCT coupon_code) AS unique_coupons
FROM visits;

This returns 5 rows_seen, 3 rows_with_coupon, and 2 unique_coupons. COUNT(*) counts every row, COUNT(coupon_code) skips the NULL values, and DISTINCT removes the repeated WELCOME value.

MySQL documents that aggregates ignore NULL values, with COUNT(*) as the exception because it counts rows rather than one expression.

Why LEFT JOIN changes the answer

A LEFT JOIN preserves an unmatched left row and fills right-table columns with NULL. COUNT(*) can be 1 for a customer with no order while COUNT(o.id) is 0. Choose the expression that matches the business question.

For the related three-valued-logic issue, read why = NULL never works.

Which COUNT should you use?

Use COUNT(*) for result rows.
Use COUNT(column) for populated values.
Use COUNT(DISTINCT column) for unique populated values.

Frequently asked questions

Does COUNT(column) count zero?

Yes. Zero is a value, not NULL, so COUNT(column) includes it. Only NULL values are excluded.

Should I use COUNT(*) or COUNT(id)?

Use COUNT(*) for rows. COUNT(id) is equivalent only when id cannot be NULL, and after an outer join the distinction can be essential.