What are the difference ways to replace nulls values in MySQL using SELECT statement?

There are lots of options available to replace NULL values using select statement. You can use CASE statement or IFNULL() or COALESCE()

Case 1 − Using IFNULL()

The syntax of IFNULL() is as follows −

SELECT IFNULL(yourColumnName,’yourValue’) AS anyVariableName from yourTableName;

Case 2 − Using COALESCE()

The syntax of COALESCE() is as follows −

SELECT COALESCE(yourColumnName,’yourValue’) AS anyVariableName from yourTableName;

Case 3 − Using CASE statement

The syntax of CASE statement.

SELECT CASE
WHEN yourColumnName IS NULL THEN ‘yourValue’
ELSE yourColumnName END AS anyVariableName FROM yourTableName

To understand what we discussed above, let us create a table. The query to create a table is as follows −

mysql> create table ReplaceNULLDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10),
   -> Marks int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into ReplaceNULLDemo(Name,Marks) values('Larry',90);
Query OK, 1 row affected (0.16 sec)

mysql> insert into ReplaceNULLDemo(Name,Marks) values('Carol',NULL);
Query OK, 1 row affected (0.17 sec)

mysql> insert into ReplaceNULLDemo(Name,Marks) values('David',NULL);
Query OK, 1 row affected (0.14 sec)

mysql> insert into ReplaceNULLDemo(Name,Marks) values('Bob',67);
Query OK, 1 row affected (0.17 sec)

mysql> insert into ReplaceNULLDemo(Name,Marks) values('Sam',78);
Query OK, 1 row affected (0.19 sec)

mysql> insert into ReplaceNULLDemo(Name,Marks) values('Mike',NULL);
Query OK, 1 row affected (0.19 sec)

mysql> insert into ReplaceNULLDemo(Name,Marks) values('John',98);
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ReplaceNULLDemo;

The following is the output −

+----+-------+-------+
| Id | Name  | Marks |
+----+-------+-------+
|  1 | Larry |    90 |
|  2 | Carol |  NULL |
|  3 | David |  NULL |
|  4 | Bob   |    67 |
|  5 | Sam   |    78 |
|  6 | Mike  |  NULL |
|  7 | John  |    98 |
+----+-------+-------+
7 rows in set (0.00 sec)

Let us now convert the NULL value to 0.

Case 1 − Using IFNULL()

The query is as follows −

mysql> select ifnull(Marks,0) as ReplacementOfNULLWith0 from ReplaceNULLDemo;

The following is the output displaying NULL replaced with 0 −

+------------------------+
| ReplacementOfNULLWith0 |
+------------------------+
|                     90 |
|                      0 |
|                      0 |
|                     67 |
|                     78 |
|                      0 |
|                     98 |
+------------------------+
7 rows in set (0.00 sec)

Case 2 − Using COALESCE()

The query is as follows −

mysql> select coalesce(Marks,0) as ReplacementOfNULLWith0 from ReplaceNULLDemo;

The following is the output displaying NULL replaced with 0 −

+------------------------+
| ReplacementOfNULLWith0 |
+------------------------+
| 90 |
| 0 |
| 0 |
| 67 |
| 78 |
| 0 |
| 98 |
+------------------------+
7 rows in set (0.00 sec)

Case 3 − Using CASE statement.

The query is as follows −

mysql> select case
   -> when Marks is null then 0
   -> else Marks end as ReplacementOfNULLWith0
   -> from ReplaceNULLDemo;

The following is the output displaying NULL replaced with 0 −

+------------------------+
| ReplacementOfNULLWith0 |
+------------------------+
| 90                     |
| 0                      |
| 0                      |
| 67                     |
| 78                     |
| 0                      |
| 98                     |
+------------------------+
7 rows in set (0.00 sec)