COUNT (*) vs COUNT(1) In SQL

In SQL, COUNT(*) and COUNT(1) are often used interchangeably to count the number of rows in a table. While they achieve the same result, there are subtle differences in their usage and performance. Here’s a detailed comparison:-

COUNT(*)

- Usage: Counts all rows in the table, including rows with NULL values.

- Syntax: 

  SELECT COUNT(*) FROM table_name;

- Performance: Modern SQL databases optimize COUNT(*) queries efficiently. The * does not mean that all columns are selected; it is simply a standard notation to count all rows.

COUNT(1)

- Usage: Counts all rows in the table. The 1 is a constant expression and does not refer to any column.

- Syntax: 

  SELECT COUNT(1) FROM table_name;

 

- Performance: Similar to COUNT(*), modern SQL databases optimize COUNT(1) effectively. Since 1 is a constant, it can be slightly more efficient in certain scenarios because it doesn’t need to reference any columns.

Differences:-

1. Conceptual Difference:- 

- COUNT(*) counts all rows, considering all columns.

- COUNT(1) counts all rows, but 1 is just a placeholder and doesn’t reference any column.

2. Performance:-

- In most modern SQL databases, there is no significant performance difference between COUNT(*) and COUNT(1) due to query optimization techniques.

 - Older versions of some databases might show slight differences, but these differences are generally negligible.

3. Usage in Joins:

- When using joins, COUNT(*) can be useful to count rows from a specific table even if some columns are joined with NULL values.

- COUNT(1) remains unaffected by the actual columns being joined, as it uses a constant.

Example:-

Given a table employees:

| id | name     | department |

|----|----------|------------|

| 1  | John Doe | Sales      |

| 2  | Jane Doe | Marketing  |

| 3  | Alice    | NULL       |

Using COUNT(*):-

SELECT COUNT(*) FROM employees;

Result: 3

Using COUNT(1):

SELECT COUNT(1) FROM employees;

Result: 3

In both cases, the result is the same: 3, indicating that there are three rows in the employees table

For most practical purposes, COUNT(*) and COUNT(1) are interchangeable and will produce the same results with similar performance. The choice between them is often a matter of personal or organizational coding standards rather than a strict performance or functionality requirement.