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.
saying these in an interview costs you the question
- 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(*)