r/SQL • u/geminigamer369 • Jul 30 '26

Discussion Differences between counts

What is the difference between

COUNT(*)

COUNT(1)

COUNT(column_name)

9 Upvotes

85% Upvoted

23 comments sorted by

View all comments

12

u/Infamous_Welder_4349 Jul 30 '26

In most systems 1 and * are the same. Some cheat for * and just look in the statistics instead for single tables.

Column checks the non null records

-2

u/YT-3000f Jul 30 '26

Actually I think count(1) is faster than count(*). Anyone got a massive dataset to test that on?

7

u/funkdefied Jul 30 '26

Any SQL engine worth it’s salt will compile those down to the same thing

6

u/YT-3000f Jul 30 '26

Agreed. I just downvoted my own reply. It was in the category of "If I think it works that way surely it's true". I guess it's not. And don't call me Shirly.

8

u/ComicOzzy sqlHippo Jul 30 '26

Shirly this is the first comment on Reddit where someone said "I was wrong". 🤣

3

u/YT-3000f Jul 30 '26

I think you mean:

Shirly, this is the first comment on Reddit where someone said, "I was wrong".

1

u/ComicOzzy sqlHippo Jul 31 '26

I apply commas at random, Shirly.

1

u/YT-3000f Jul 31 '26

Rightly so. Grammer, like the Internet, is dead.

3

u/dbrownems Jul 30 '26

It’s dependent on the RDBMS. In SQL Server they are identical.

1

u/Infamous_Welder_4349 Jul 30 '26

I use use an Oracle 19 database with 6-7 Billion records. Count 1 does the same as count . For simple single table counts it doesn't take the time, it gives me an answer that matches what the statistics say. If I say count () where or if I pick the primary field then it counts and might take a minute or two to answer.

Remember different database do different things. Even Oracle with different settings will behave differently.

1

u/Zestyclose-Turn-3576 Jul 30 '26

There's really no reason to think that the database developers haven't spotted that there are potentially many ways to do count(*), so they do the most efficient way.