Parottasalna

When working with SQL, counting rows in a table is a common task. Two widely used methods for this are COUNT(*) and COUNT(1). While both seem similar, they often spark debates among developers regarding performance, clarity, and best practices.

In this blog, i jot down details on what these functions do, their performance implications, and which one to use.

COUNT(*)

COUNT(*) counts all the rows in a table, regardless of whether the columns contain NULL values or not. It does not focus on any particular column; instead, it considers every row present in the table.

It is straightforward and does exactly what you’d expect—count the total number of rows in the result set.

SELECT COUNT(*) FROM employees;

This query returns the total number of rows in the employees table.

COUNT(1)

COUNT(1) also counts all the rows in a table. However, instead of counting actual rows, it evaluates the constant 1 for each row. The 1 here is a placeholder and doesn’t refer to any specific column.

The database engine treats COUNT(1) similarly to COUNT(*), making it another way to count rows.

SELECT COUNT(1) FROM employees;

This query performs the same function as COUNT(*), counting all rows in the employees table.

Key Differences Between COUNT(*) and COUNT(1)

Scope of Counting

• COUNT(*) includes every row in the table, as it doesn’t rely on specific columns or values.
• COUNT(1) evaluates the constant 1 for each row, effectively counting the rows.
  

Handling NULL Values

Both functions ignore NULL values because they don’t depend on column-specific data. This is particularly useful when working with tables that contain missing or undefined data.

Syntax and Readability

• COUNT(*) is often more immediately understandable since the asterisk (*) clearly signifies “count everything.”
• COUNT(1) can be slightly less intuitive at first glance, as it might make someone wonder why the constant 1 is used.

Readability and Best Practices

Why Readability Matters

Code readability is crucial, especially in collaborative environments where multiple developers work on the same codebase.

COUNT(*)

✅ Clearly conveys the intent to count all rows in a table. ✅ Easy for both beginners and experienced developers to understand at first glance.

COUNT(1)

❌ While it works the same as COUNT(*), it may confuse someone unfamiliar with its behavior. ❌ It doesn’t explicitly signify “count everything,” making it slightly less intuitive.

Feature	COUNT(*)	COUNT(1)
Counts all rows	✅ Yes	✅ Yes
Uses a placeholder	❌ No	✅ Yes
More readable	✅ Yes	❌ Less intuitive
Performance difference	❌ Negligible	❌ Negligible
Recommended	✅ Yes	⚠️ Only if team prefers

In most scenarios, COUNT(*) is the preferred choice because it clearly communicates its purpose. While COUNT(1) is equally valid, its slightly ambiguous syntax may cause confusion. Ultimately, the best option is the one that aligns with your team’s practices and ensures your code is easy to understand.