SQL, data modeling, data quality, EDA, visualization · insight-1044
Count a right-hand key, not COUNT(*), after a LEFT JOIN
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A query LEFT JOINs flights to ancillary sales and reports the number of sales per flight with COUNT(*), and flights with no sales show 1.
Action
Count a non-null column from the right-hand table (COUNT(s.sale_id)) or a distinct key, and test it on a flight known to have no match. Use COUNT(DISTINCT ...) only when the join can repeat the key.
Reason
COUNT(*) counts rows and COUNT(expr) counts rows where the expression is not NULL, so only the latter ignores null-extended rows.
Boundary
Outer joins that feed counts, rates and "number of X per Y" tables.
Symptom or risk
The unmatched flight survives as one row with NULLs on the right, and COUNT(*) counts that row, so "no sales" is reported as one sale.
Exceptions
When the number of output rows is the quantity of interest, for example flights with or without a match.
Trade-offs
COUNT(DISTINCT) is slower on large inputs.
Pitfalls
Taking COUNT(s.some_nullable_column) where the column itself can legitimately be NULL on a match.
Lessons and concepts
Claims and their sources
In PostgreSQL count(*) counts all input rows while count(expression) counts only rows where the expression is not null.