Understanding COUNT(*) vs COUNT(1) in SQL

In SQL, counting rows is a common task, and you’ve probably encountered COUNT(*) and COUNT(1) in various queries. At first glance, they seem identical — and in practice, they often produce the same result. But what really happens behind the scenes? Does one perform better than the other? Let’s dive into the nuances and explore the differences between COUNT(*) and COUNT(1), debunk some common myths, and offer practical guidance on when to use each.

1. COUNT(*) vs. COUNT(1): The Basics

• COUNT(*): This function counts all rows in a table, regardless of whether the columns contain NULL values. It operates at the row level, ignoring specific column values.
• COUNT(1): Similarly, this function also counts all rows. The 1 here is simply a constant expression that is evaluated for each row. Since 1 is always non-NULL, it effectively counts every row as well.

At face value, both approaches give the same result — but there are key differences in how SQL engines handle them.

2. Internal Mechanics: How Do They Work?

• COUNT(*): When you use COUNT(*), the SQL engine doesn’t evaluate individual column values. It’s designed to quickly scan through each row and count them, even if the row contains NULL values. The engine isn’t concerned with what’s inside each row — just that the row exists.
• COUNT(1): With COUNT(1), the SQL engine processes the constant value 1 for each row. Since 1 is a static, non-NULL value, it evaluates every row, essentially performing the same counting operation as COUNT(*).

In essence, both functions rely on counting rows, but the internal processing varies slightly. However, these differences are mostly theoretical in modern SQL engines.

3. Performance Considerations: Old Myths vs. Modern Realities

In the early days of SQL, developers often debated which was faster: COUNT(*) or COUNT(1). There was a belief that COUNT(1) could be more efficient because it evaluates a constant rather than scanning every column in a row.

However, in modern SQL databases, this is no longer the case.

Most SQL engines (such as MySQL, PostgreSQL, SQL Server, and Oracle) have highly optimized both operations. Whether you use COUNT(*) or COUNT(1), the query optimizer processes them similarly. The difference in performance is so negligible that in 99.9% of scenarios, you won’t notice any difference at all.

Key Takeaway: If you’re using a modern SQL engine, there’s no real performance advantage to using COUNT(1) over COUNT(*). They are essentially treated the same.

When to Use COUNT(*), COUNT(1), or COUNT(Column)

• Use COUNT(*) when you want to count all rows: This is the most common and clear way to count the total number of rows in a table. It’s widely used because it makes the query intention obvious: “Count everything, regardless of column values.”
• Use COUNT(1) if you prefer, but don’t expect a performance gain: Some developers still use COUNT(1) out of habit or because they’ve inherited legacy code that uses this pattern. While it works the same way, there’s no tangible benefit in most cases today.
• Use COUNT(column_name) when you want to count non-NULL values in a specific column: Unlike COUNT(*) or COUNT(1), this function ignores NULL values. It’s useful when you want to count only the rows where a particular column contains a value.

For Example:

5. SQL Example for Practical Use

Here’s a simple SQL query using both methods:

Both queries will return the same result: the total number of rows in the Employees table, even if some rows contain NULL values. It’s worth noting that in most real-world scenarios, you’ll likely reach for COUNT(*) simply because it’s more explicit and familiar.

6. Clearing Up Common Misunderstandings

• Myth: COUNT(1) is faster than COUNT(*): As mentioned earlier, this is a leftover belief from older versions of SQL. Modern SQL optimizers treat them the same way, so there’s no significant performance difference.
• Myth: COUNT(*) evaluates all columns: This is another misconception. While COUNT(*) operates at the row level, it doesn’t perform column-level evaluations. It simply counts rows, regardless of what’s inside them.

7. Conclusion: Which One Should You Use?

When it comes down to it, both COUNT(*) and COUNT(1) will give you the same result in most scenarios, and performance differences are negligible in modern SQL databases.

• For clarity and readability, most developers prefer using COUNT(*), as it clearly communicates that the query is counting all rows.
• For legacy code or personal preference, if you’re used to COUNT(1), that’s fine too — just know that it won’t provide a performance edge anymore.

Ultimately, the choice between the two comes down to preference rather than performance, so feel free to use whichever feels more natural for you and your team.

---

Final Summary:

• COUNT(*): Best for clarity and counting all rows.
• COUNT(1): Functionally equivalent to COUNT(*) with no performance advantage in modern databases.
• COUNT(column_name): Ideal when you need to count non-NULL values in a specific column.

Whether you go with COUNT(*) or COUNT(1), you can rest assured that both are reliable, efficient, and widely used in SQL queries today.