What is the difference between count(0), count(1).. and count(*) in mySQL/SQL?

sql

best-practices

performance

sql-optimizers

byAlex Kataev·Oct 31, 2024

COUNT(*) counts all rows, including those with NULL values. COUNT(1) or COUNT(0), they give the same result because they're using non-null constants, considering all the rows in the count.

Be aware that they're handled interchangeably by most SQL optimizers.

A common way to count all rows:

SELECT COUNT(*) FROM table_name;  -- "Hey table, feeling heavy today?"

To star or not to star, that is the question. But for explicitness and clear intent, COUNT(*) shines.

Deep dive: Under the COUNT's cloak

Semantics of counting

Understanding the flavors of COUNT:

COUNT(*) is explicit goodness. It counts every row, NULL or not, laying bare its intention to include all.
COUNT(column) daydreams about a specific column. It counts non-null entries of that column. When NULL matters, this is your ally.
COUNT(1) or COUNT(0) are like budget versions. They count all rows—value doesn't matter. No performance boost, but could smooth SQL dialect transitions.

Your COUNT's speedometer

Wondering if COUNT(1) should zoom past COUNT(*) in performance? Not in today's world of smart SQL optimizers. 'Rumors' of COUNT(1)'s speed are a blast from the SQL past.

For the speed-conscious, running empirical benchmarks in your specific environment could provide interesting insights.

Best practices & portability

While COUNT(*) remains a clear, readable choice, COUNT(0) or COUNT(1) could come to rescue in cross-database scenarios. Yet, readability usually outranks minor possible optimizations.

SQL counting in action

Knowing your COUNT territory

Expect surprises when:

Aggregated queries with JOINs: COUNT(*) might register duplicate rows. Know your data and context.
Performance on large datasets: Consider slimming data with WHERE clauses or indexed columns.
Using COUNT with DISTINCT: To count distinct values in a column, use COUNT(DISTINCT column). This is not the same as COUNT(1) or COUNT(0).

Pitfalls and how to swerve

Miscounting due to NULLs: Be extra careful when picking COUNT(column) versus COUNT(*) when NULL values are involved.
COUNT addiction: Sometimes, EXISTS can be more efficient than COUNT. If you just want to know if rows exist, consider this option.
Locking issues: COUNT choice could affect locking and concurrency. Keep an eye on your isolation levels and locking mechanisms.