Counting seems simple in SQL… until joins enter the picture.

One of the most common bugs in reporting queries comes from misunderstanding:

COUNT(*) vs COUNT(column)

1. The Core Rule

✅ COUNT(*) -> Counts rows

✅ COUNT(column) -> Counts non-NULL values in that column

That’s it.
But joins create NULLs — and that’s where things change.

2. Example setup

Customer

Orders

a. INNER JOIN

SELECT c.id, COUNT(*), COUNT(o.id)
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

Why?

INNER JOIN removes non-matching rows.
No NULL rows are produced.

So:

COUNT(*) == COUNT(o.id)

No surprises here.

b. LEFT JOIN (Where Most Bugs Happen)

SELECT c.id, COUNT(*), COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

Why is Carol different?

LEFT JOIN keeps all customers.
For Carol: A row exists + o.id is NULL

So:

COUNT(*) counts the row → 1
COUNT(o.id) ignores NULL → 0

3. Usage

When you have to count multiple joined tables with different expressions, mind to use count + join properly and wisely

4. Final thoughts

Most production reporting bugs involving SQL counts come from misunderstanding how NULL behaves in outer joins.

Once you internalize:

COUNT(*) counts rows
COUNT(column) counts values

You’ll stop shipping incorrect dashboards.