SQL: COUNT(column) is not COUNT(*), and neither is COUNT(DISTINCT)

COUNT(*) counts rows. COUNT(col) counts rows where col is not NULL. After an outer join these differ by exactly the unmatched rows - which is usually the number somebody is about to report to a customer.

• SQL: a stale row estimate is why the planner chose the wrong join
• SQL: READ COMMITTED lets the same query return different rows twice
• SQL: consistent lock ordering is the only cure for deadlocks
• SQL: SELECT DISTINCT is usually a join fan-out you have not fixed
• SQL: OFFSET pagination gets slower the deeper the user goes
• SQL: a WHERE on the right table turns a LEFT JOIN into an INNER JOIN

---

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25

© Java Coding Hub · About · Contact · Privacy · Terms