SQL: COUNT(*) counts rows, COUNT(col) skips NULLs

They differ by exactly the number of NULLs in that column - which after a LEFT JOIN is the number of unmatched rows.

Code

SELECT COUNT(*)        AS rows,
       COUNT(o.id)     AS with_orders,
       COUNT(DISTINCT c.id) AS customers
  FROM customer c
  LEFT JOIN orders o ON o.customer_id = c.id;

Output

rows | with_orders | customers
9876 |        1204 |      9876

-- COUNT(1) is identical to COUNT(*) in every mainstream engine.
-- The folklore that one is faster has been false for twenty years.

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