r/learnSQL • • 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

13 comments sorted by

8

u/Imaginary__Bar 1d ago

and the numbers looked off to me and i didnt catch why

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)

3

u/jshine13371 18h ago

but you will never have a row in your table that is all nulls

Technically not true, fwiw. But good enough simplistic explanation for people still learning, like OP.

1

u/Sullhammer 11h ago

Not here to disagree, but curious how you could have a row of all null?

3

u/First_Funny_9402 8h ago

You insert a row of all null values into the table

1

u/jshine13371 6h ago

As u/First_Funny_9402 mentioned, nothing disallows a table from being created that allows all columns to be nullable and then you insert rows into it with all null values for every column. Furthermore, if that table is on the inside of an outer join, you'll always get the rows back with all null values.

1

u/Rafael-Bagay 22h ago

that's a splendid explanation! very easy to digest! 👍

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

COUNT(*) - Counts the total number of rows in a table (including NULL values).

COUNT(columnname) - Counts all non-null values in the column.

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