What do COUNT(*) and COUNT(city) return for a 10-row table where 3 rows have NULL city?
answer
- one form ignores nothing at all
- the argument decides what gets skipped
- unknown values are not counted
- star has no expression to be NULL
- ten versus seven
basics
~10 sCOUNT() returns 10 and COUNT(city) returns 7. COUNT() counts rows; COUNT(expression) counts only the rows where that expression is not NULL, so the three NULL cities are skipped.
solid answer
~40 s`COUNT(*)` is defined as the number of rows in the group, whatever those rows contain, so it returns **10**. `COUNT(city)` evaluates `city` once per row and counts only the rows where the value is not NULL, so it returns **7**. The star is not shorthand for "all columns" — it is a grammar token meaning "count rows", and there is no argument that could be NULL. This is the reason a report that shows `COUNT(*)` next to `COUNT(some_column)` legitimately shows two different numbers on the same data: the second one is really answering "how many rows have a value here?". Both forms return 0, never NULL, when the group has no qualifying rows.
code
sql · 5 lines-- users has 10 rows; 3 of them have city IS NULL
SELECT COUNT(*) AS rows_total, -- 10
COUNT(city) AS rows_with_city, -- 7
COUNT(DISTINCT city) AS distinct_cities -- distinct non-NULL cities
FROM users;go deeper
Memorise the one-line rule: star counts rows, a column argument counts rows where that column is not NULL. Be ready to give both numbers instantly for a small worked example.
Explain why NULL is skipped — it is an unknown, not a value — and show the third form, COUNT(DISTINCT col), so the interviewer hears that you know all three answer different questions.
Show where this quietly corrupts real reports: ratios with a casually chosen denominator, and counts taken after an outer join. Say how you sanity-check a new aggregate query before shipping it.
Frame it as a data-contract issue: whether a column is nullable decides what your metrics mean, so column nullability and the definition of each reported metric belong together in review, not in a dashboard's SQL alone.
## The two forms answer different questions `COUNT` is an aggregate function: it consumes all the rows of a group and returns one integer. SQL gives it two shapes, and they ask different questions. - `COUNT(*)` is defined by the standard as the **cardinality of the group** — how many rows are in it. It looks at no column at all. - `COUNT(<expression>)` evaluates the expression once per row and counts the rows where the result **is not NULL**. So on a `users` table with 10 rows of which 3 have `city IS NULL`: ```sql SELECT COUNT(*) AS rows_total, -- 10 COUNT(city) AS rows_with_city -- 7 FROM users; ``` The difference is exactly the number of NULLs in that column. ## Why NULLs are skipped NULL in SQL means "unknown / no value", not "zero" and not "empty string". A value that is unknown is not a value you can count, so every SQL aggregate ignores NULL inputs. `COUNT(city)` therefore means "how many rows actually have a city recorded", which is often the number a business question wants — but only if you asked for it deliberately. ## The star is not "all the columns" A persistent piece of folklore says `COUNT(*)` is slow because the engine has to materialise every column, or that `COUNT(*)` counts rows only when at least one column is non-NULL. Neither is true. The `*` inside `COUNT` is a special token in the grammar, unrelated to the `*` in a select list. A row consisting entirely of NULLs still counts as one row. ## Empty groups `COUNT` of an empty input returns **0**, not NULL. `SELECT COUNT(*) FROM users WHERE 1 = 0` returns one row containing `0` — the query still produces a row because an aggregate with no `GROUP BY` always yields exactly one row. This differs from `SUM`, which returns NULL over an empty input, and it is why `COUNT(...) = 0` is a safe test while `SUM(...) = 0` is not. ## The third form `COUNT(DISTINCT city)` narrows further: it counts distinct non-NULL values. On values `Rome, Rome, NULL, Oslo` the three forms give 4, 3 and 2 respectively. Reading a query, the mental checklist is: star = rows, column = rows with a value, DISTINCT = different values. ## Where the difference bites Two places account for most real bugs: 1. **Percentages and ratios.** `COUNT(promo_code) * 100.0 / COUNT(*)` is the share of rows that carry a promo code — correct if that was the intent, silently wrong if you meant to divide by the number of promo-code rows. 2. **After an outer join.** A `LEFT JOIN` manufactures a row with NULLs on the unmatched side, and `COUNT(*)` dutifully counts it, so "customers with no orders" appear with a count of 1. Counting a NOT NULL column from the joined side (`COUNT(o.order_id)`) is what produces the 0 you expected. ## Choosing the right one Ask what the number means in the sentence you would speak. "How many orders were placed?" is `COUNT(*)` over the orders. "How many orders have a delivery date?" is `COUNT(delivered_at)`. "How many customers ordered?" is `COUNT(DISTINCT customer_id)`. Writing `COUNT(id)` as a habitual synonym for `COUNT(*)` is fine while `id` is the table's own primary key, but the moment that column arrives through an outer join it becomes nullable and the two forms diverge. ## What interviewers listen for The crisp answer names the rule ("star counts rows, column counts non-NULL values") rather than reciting the two numbers, and then volunteers where it matters: outer joins, and any ratio whose denominator you chose casually.
- Does COUNT(city) count repeated city values more than once?Yes. It counts non-NULL occurrences, not different values — ten rows all reading 'Rome' give 10. Counting different values is `COUNT(DISTINCT city)`, which would return 1 for that data.
- What does COUNT(*) return over an empty table, and can COUNT ever return NULL?It returns 0, in a single result row, because an aggregate without GROUP BY always produces one row. COUNT never returns NULL for a group it evaluates — that is what makes it safe in comparisons, unlike SUM, which is NULL over empty input.
- If city were declared NOT NULL, would the two forms still differ?No — with no NULLs possible they return the same number. `COUNT(*)` is still the better spelling because it states the intent ("count rows") and does not silently change meaning if the column later becomes nullable or arrives through an outer join.
- Says COUNT(*) counts rows where at least one column is non-NULL
- Claims COUNT(*) reads every column and is therefore slow
- Thinks COUNT(col) counts distinct values
- Believes COUNT(col) returns NULL when every value is NULL
- Treats COUNT(id) as always identical to COUNT(*)
context
resolving link…