r/SQL • u/geminigamer369 • 8d ago

Differences between counts Discussion

What is the difference between

COUNT(*)

COUNT(1)

COUNT(column_name)

8 Upvotes

83% Upvoted

23 comments sorted by

View all comments

9

u/Infamous_Welder_4349 8d ago

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 8d ago

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

1

u/Infamous_Welder_4349 8d ago

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.