How can I return 0 for NULL in MySQL?

We can return 0 for NULL in MySQL with the help of IFNULL() method. The syntax of IFNULL() is as follows.

IFNULL(YOUREXPRESSION,0);

Let us see an example. First, we will create a table.

mysql> create table NullDemoWithZero
   -> (
   -> id varchar(200)
   -> );
Query OK, 0 rows affected (0.65 sec)

After creating a table, let us insert some records in the table using the INSERT command. The query is as follows −

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

mysql> insert into NullDemoWithZero values('123');
Query OK, 1 row affected (0.14 sec)

mysql>  insert into NullDemoWithZero values('442');
Query OK, 1 row affected (0.10 sec)

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

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

mysql>
mysql> insert into NullDemoWithZero values();
Query OK, 1 row affected (0.17 sec)

Display all records with the help of SELECT statement. The query is as follows.

mysql> select *from NullDemoWithZero;

The following is the output.

+------+
| id   |
+------+
| NULL |
| 123  |
| 442  |
| 333  |
| NULL |
| NULL |
+------+
6 rows in set (0.00 sec)

Look at the sample output above, there are many NULL values. Now, I am going to write a syntax that returns 0, if there is NULL value in the table. The following is the syntax.

select IFNULL(yourColumnName, 0) from yourTableName;

Apply the above syntax to get 0 if any row has NULL value. The query is as follows.

mysql> select IFNULL(id, 0) from NullDemoWithZero;

The following is the output that returns 0 for every NULL.

+---------------+
| IFNULL(id, 0) |
+---------------+
| 0             |
| 123           |
| 442           |
| 333           |
| 0             |
| 0             |
+---------------+
6 rows in set (0.00 sec)