Difference between count(*) and count(columnName) in MySQL?

The count(*) returns all rows whether column contains null value or not while count(columnName) returns the number of rows except null rows.

Let us first create a table.

Following is the query

mysql> create table ifNotNullDemo
   -> (
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.54 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into ifNotNullDemo values('Chris');
Query OK, 1 row affected (0.15 sec)

mysql> insert into ifNotNullDemo values('');
Query OK, 1 row affected (0.13 sec)

mysql> insert into ifNotNullDemo values('Robert');
Query OK, 1 row affected (0.24 sec)

mysql> insert into ifNotNullDemo values(null);
Query OK, 1 row affected (0.30 sec)

mysql> insert into ifNotNullDemo values(0);
Query OK, 1 row affected (0.16 sec)

Following is the query to display all records from the table using select statement:

mysql> select *from ifNotNullDemo;

This will produce the following output

+--------+
| Name   |
+--------+
| Chris  |
|        |
| Robert |
| NULL   |
| 0      |
+--------+
5 rows in set (0.00 sec)

Case 1: Following is the demo of count(*) that includes null as well in the count:

mysql> select count(*) from ifNotNullDemo;

This will produce the following output

+----------+
| count(*) |
+----------+
| 5        |
+----------+
1 row in set (0.02 sec)

Case 2: Following is the query for count(columnName).

mysql> select count(Name) from ifNotNullDemo;

This will produce the following output

+-------------+
| count(Name) |
+-------------+
| 4           |
+-------------+
1 row in set (0.00 sec)

George John

Published on 17-Apr-2019 11:08:12

• Related Questions & Answers
• Difference between count() and find().count() in MongoDB?
• MySQL SELECT DISTINCT and count?
• MySQL- GROUP and COUNT by date?
• Count from two tables and give combined count of string in MySQL?
• Get distinct values and count them in MySQL
• Difference between MySQL and MongoDB
• Difference Between MySQL and PostgreSQL
• Count values greater and less than a specific number and display count in separate MySQL columns?
• How to count rows – count (*) and Java
• Can I use MySQL COUNT() and DISTINCT together?
• Using DISTINCT and COUNT together in a MySQL Query?
• Difference between MySQL and SQL Server
• MySQL select count by value?
• MySQL count(*) from multiple tables?
• Difference between Schema and Database in MySQL?