COUNT() vs COUNT(1) vs COUNT(col_name) in SQL

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

Table created: STUDENTS

ID   | MARKS
-----+------
1    | 10
2    | 15
null | 20
3    | 25
null | 18
4    | 22

■ COUNT(*) :

Let’s check count(*) operation on the above table: STUDENTS.

SELECT count(*) from STUDENTS;

Output:

6

count(*) output = Total number of records in the table including null values.

---

■ COUNT(1) :

Let’s check count(1) operation on the above table.

SELECT count(1) from STUDENTS;

Output:

6

count(1) output = Total number of records in the table including null values.

NOTE :

▪ The output of count(*) and count(1) is same but the difference is in the time taken to execute the query.

▪ count(1) is faster/optimized than count(*) because:

• count(*) has to iterate through all the columns,
  But count(1) iterates through only one column.
  count(*) 遍历所有列
  count(1)只遍历一个列

• Check the time difference between count(*) and count(1) on big data-set.

▪ Always try to use count(1) instead of count(*). Since, count(1) performs better and saves computation effort & time.

COMMON CONFUSION :

▪ Many people think that the number “1” in thecount(1)indicates the first column of the table.
But it is not correct.

▪ 1 is just a hardcoded value. You can use any other hardcoded number or string instead of 1.
The output will be the same.

count(0), count(2), count(-1), count("A"), count("APDaga"), etc

---

■ COUNT(col_name) :

Let’s check count(col_name) operation on the above table.

On first column : ID

SELECT count(ID) from STUDENTS;

Output:

4

count(ID) output = Total number of entries in the column “roll_no” excluding null values.

On the second column : MARKS

SELECT count(MARKS) from STUDENTS;

Output:

6  

count(MARKS) output = Total number of entries in the column “Marks” excluding null values.

---

总结:

1. count(*) :
   output = total number of records in the table including null values.

   输出所有数据（包括空值）的行数

2. count(1) :
   output = total number of records in the table including null values.
   [ Faster than count(*) ]

   输出所有数据（包括空值）的行数
   比count(*)快

3. count(col_name) :
   output = total number of entries in the column “col_name” excluding null values.

   输出相应列名的数据（不包括空值）的行数