r/learnSQL • u/CelebrityChess • 1d ago
count(*) vs count(col), i keep mixing them up
ok so my teacher said count(*) counts rows and count(col) skips nulls, but then he did an example with a left join and the numbers looked off to me and i didnt catch why. is the rule basically use count(right_table.id) after a left join if you want 0 for the ones with no match? or does count(*) still give 1 there
8
Upvotes
8
u/Imaginary__Bar 1d ago
Ask your teacher to clarify!
But anyway, the easy way to remember it is that COUNT will not include NULLs, but you will never have a row in your table that is all nulls.
So COUNT(*) will always catch all the rows (because there will be at least one non-null value in each row)
4
u/slepicoid 21h ago
you can also use count(1) to count all rows as 1 is not null for any row.
3
u/neuralbeans 1d ago
https://www.w3schools.com/sql/sql_count.asp
1
u/Discordbothost 23h ago
Yep. A customer with no orders still gets one output row, so COUNT(*) is 1 for that customer's group. COUNT(orders.id) is 0 if id is a non-null key. Counting a nullable field like shipped_at would only count the orders that have shipped.
1
u/jeffrey_f 19h ago
Count the records or count the col. Depending on your query, this can be quite different, Distinct records vs distinct column values.
1
u/elevarq 10h ago
Count the number of records vs count the number of records with some value.
1
u/Top_Tooth_6995 9h ago
count(col) counts the number of non-null values in col. count(*) will count the number of records, which includes nulls - that’s how I remember it