What does "select count(1) from table_name" on any database tables mean?

sql

count-function

sql-performance

database-optimization

byAnton Shumikhin·Oct 10, 2024

The SQL command SELECT COUNT(1) FROM table_name; is a swift way to tally all rows in a table_name. Much like its sibling command, COUNT(*), it is often mistakenly considered faster by some DBMS due to 1 being a simple placeholder, ensuring speedy row counting.

SELECT COUNT(1) FROM table_name;  //-- "Every row is a superstar to me!" 😉

The COUNT(1) and COUNT(*) equivalence

At its core, the use of COUNT(1) in your SQL command will tally all entries in a table, much the same way that COUNT(*) does. However, it's key to note that:

COUNT(1) equals '"bread and butter" number count. It's an evaluated expression, where 1 is always a non-NULL number, serving as a placeholder for each row.
COUNT(*) equals 'the whole crew' count. It's an all-inclusive row count, with no column data examination.

Remember, DBMS treats COUNT(1) and COUNT(*) equally. So fret not, as there won't be a duel at dawn between these two!

Unflappable stance with NULL values

Banal as it appears, COUNT(1) and COUNT(*), are surprisingly invulnerable to NULL value attacks.

The expression '1' in COUNT(1) is non-NULL and hence, ensures every row gets counted, irrespective of the content.
Likewise, COUNT(*), without any discrimination, counts all rows.

So, in this 'NULL game of thrones', both our contenders don't even break a sweat!

Modifiers: ALL and DISTINCT in play

In the interesting game of SQL, COUNT() dons two distinct hats: ALL and DISTINCT:

ALL: By default, COUNT() is like an inclusive party host, counting everyone on the guest list, duplicates included.
DISTINCT: With this, COUNT() turns into an exclusive club bouncer, only letting unique entries into the count.

COUNT function and performance considerations

Performance is key, even for SQL commands:

Neither faster nor slower: Regardless of your choice, COUNT(1) or COUNT(*), modern SQL platforms ensure optimal performance.
Smart Execution: Database query planners smartly interpret COUNT(1) and COUNT(*), tallying rows without data evaluation.

Aim to improve data structures, indexes, and WHERE clause usage for real performance gains.

When and how to use COUNT for maximum utility

Final strategic points for stellar SQL COUNT() use:

Expression preference: COUNT(*) is good for usual counts; for specific conventions, COUNT(1) is your buddy.
COUNT with WHERE: Use COUNT() with WHERE to count rows meeting specific criteria. It's your personalized row counter!
Joins Impact: In a left join, using COUNT(column_name) where column_name is from a joined table can give different results from COUNT(*).