The core difference: NULLs
Every COUNT variant does the same basic job — tally rows — but they disagree on one thing: whether NULLs and duplicates count. That's the whole story. To see it, we need data with both. Here's our users table:
| user_id | name | phone |
|---|---|---|
| 1 | Alice | 555-111 |
| 2 | Bob | NULL |
| 3 | Carol | 555-111 |
| 4 | Dave | NULL |
Two NULL phones, and one duplicate phone number (555-111). That's all we need.
COUNT(*) — count rows
COUNT(*) counts rows, full stop. It never looks at column values, so NULLs and duplicates are irrelevant — a row is a row.
SELECT COUNT(*) FROM users; -- 4 (all rows)COUNT(column) — count non-NULL values
COUNT(phone) counts only rows where phone is not NULL. Bob and Dave have NULL phones, so they're skipped — but the duplicate 555-111 is still counted twice.
SELECT COUNT(phone) FROM users; -- 2 (Alice + Carol; NULLs skipped)COUNT(some_column) meaning "how many rows" and get a smaller number because that column has NULLs. If you want rows, use COUNT(*).COUNT(DISTINCT column) — count unique values
COUNT(DISTINCT phone) ignores NULLs and collapses duplicates, so it counts distinct real values. Only one unique phone number exists (555-111):
SELECT COUNT(DISTINCT phone) FROM users; -- 1 (only 555-111)COUNT(1) and the performance myth
You'll see COUNT(1) in the wild, often with a claim that it's "faster than COUNT(*)." It isn't. COUNT(1) counts rows exactly like COUNT(*), and every major optimizer treats them identically. The 1 isn't evaluated per row.
SELECT COUNT(1) FROM users; -- 4 (identical to COUNT(*))Prefer COUNT(*) — it's the standard, clearest way to say "count rows."
Counting rows that match a condition
Because COUNT(column) skips NULLs, you can count matches by making non-matches evaluate to NULL. This is the idiomatic "count where condition":
-- How many users have a phone vs total, in one query
SELECT
COUNT(*) AS total_users,
COUNT(CASE WHEN phone IS NOT NULL THEN 1 END) AS with_phone,
SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS missing_phone
FROM users; -- 4, 2, 2COUNT(CASE WHEN cond THEN 1 END) and SUM(CASE WHEN cond THEN 1 ELSE 0 END) both count matching rows. Use whichever reads more clearly to you.COUNT with GROUP BY
All of this applies per group. A common report combines total rows with non-NULL counts to spot data-quality gaps:
SELECT country,
COUNT(*) AS users,
COUNT(phone) AS with_phone
FROM users
GROUP BY country
ORDER BY users DESC;If users and with_phone differ for a country, that gap is your missing phone numbers. (For more GROUP BY pitfalls, see GROUP BY mistakes that break SQL queries.)
Side-by-side comparison
| Expression | Counts | NULLs | Duplicates | Result (sample) |
|---|---|---|---|---|
| COUNT(*) | All rows | Included | Included | 4 |
| COUNT(1) | All rows (= COUNT(*)) | Included | Included | 4 |
| COUNT(phone) | Non-NULL values | Excluded | Included | 2 |
| COUNT(DISTINCT phone) | Unique non-NULL values | Excluded | Collapsed | 1 |
Key takeaways
COUNT(*)= rows (NULLs and duplicates included).COUNT(column)= non-NULL values (duplicates still counted).COUNT(DISTINCT column)= unique non-NULL values.COUNT(1)equalsCOUNT(*)— no speed difference.- Count matching rows with
COUNT(CASE WHEN cond THEN 1 END).