gotchaModeratepending

PostgreSQL COUNT with LEFT JOIN returns wrong numbers

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

COUNTLEFT JOINNULLGROUP BYwrong countaggregation

postgresqlmysqlsqlite

Error Messages

wrong count

count includes nulls

Problem

Using COUNT(*) with LEFT JOIN counts too many rows because NULL rows from the right table are counted. Or COUNT returns 1 instead of 0 for items with no matches.

Solution

Use COUNT(right_table.id) instead of COUNT(*). COUNT(column) only counts non-NULL values, so unmatched LEFT JOIN rows (which are NULL) are correctly excluded. Also be careful with GROUP BY -- missing columns can merge groups unexpectedly.

Why

COUNT(*) counts rows regardless of NULL values. LEFT JOIN produces rows with NULLs for unmatched records. COUNT(specific_column) skips NULLs.

Code Snippets

COUNT with LEFT JOIN fix

-- WRONG: counts NULL rows from LEFT JOIN
SELECT u.name, COUNT(*) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;
-- Users with 0 orders show count = 1!

-- RIGHT: count specific column
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;
-- Users with 0 orders show count = 0

Revisions (0)

No revisions yet.