✏️ Explanatory Question
Level: Intermediate — A popular question that trips up candidates on NULL handling and performance myths.
All three count rows, but the crucial difference is how they handle NULL values:
COUNT(*) (the constant 1 is never NULL).COUNT(1) is faster than COUNT(*). In modern MySQL (InnoDB), they are optimized identically — there is no performance difference. Use COUNT(*) as the standard.
| Expression | Counts NULLs? | Result | Speed |
|---|---|---|---|
COUNT(*) |
Yes | Total rows | Fast |
COUNT(1) |
Yes | Total rows (same as *) | Fast (identical) |
COUNT(column) |
No | Non-NULL values only | Slightly more work |
COUNT(DISTINCT col) |
No | Unique non-NULL values | Slower (dedupe) |
Imagine an employees table with 10 rows, where 3 rows have a NULL email:
COUNT(*) → 10 (all rows)COUNT(1) → 10 (all rows)COUNT(email) → 7 (NULLs excluded)-- Total number of rows
SELECT COUNT(*) FROM employees; -- e.g., 10
-- Same as COUNT(*)
SELECT COUNT(1) FROM employees; -- 10
-- Only rows where email is filled in
SELECT COUNT(email) FROM employees; -- e.g., 7 (3 NULLs skipped)
-- Unique non-NULL departments
SELECT COUNT(DISTINCT dept_id) FROM employees;