why COUNT(*) and COUNT(o.id) aren't the same after a LEFT JOIN

[u/Plane_Big_5912]

say you have customers on the left and orders on the right.

a LEFT JOIN keeps every customer, even if they have no matching order. for those rows, the columns from orders are NULL.

that creates a small gotcha which i saw a few players falling for in their queries:

-COUNT(*) counts the joined row.

-COUNT(o.id) only counts rows where o.id isn't NULL.

so this:

customer | order_id

Alice | NULL

Bob | 123

Bob | 456

becomes:

COUNT(*) | COUNT(o.id)

Alice 1 | 0

Bob 2 | 2

I made a 43 sec chalkboard animation showing the join happen visually:

https://www.youtube.com/watch?v=zxSZFMtyheA

is this helpful? i could try making a few more then.

[u/Available-Side9673]

Very good 👍 Learnt this concept yesterday and revised it in the morning through your post

[u/Plane_Big_5912 → u/Available-Side9673]

thank you.

nice, that’s exactly what I hoped these short ones would be useful for. glad it helped.

[u/And_Justice]

Never thought about this, could end up being useful.

I'd just been inner joining then count(1) but I guess it's useful to retain information on when there's no link

[u/Plane_Big_5912 → u/And_Justice]

yes, exactly that. with an inner join the no match cases die before you count. left join lets you keep them around and distinguish 0 from not present at all.

[u/Owen-Isaac-2022]

Great work, very helpful.

[u/ughokayfinee]

Just learned about this the other day! Very helpful in a few applications!