What are the different ways to replace null values ​​using a SELECT statement in MySQL?

There are many options for replacing NULL values ​​using select statements. You can use CASE statement or IFNULL() or COALESCE()

Case 1 - Use IFNULL()

The syntax of IFNULL() is as follows-

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

Case 2 - Use COALESCE()

The syntax of COALESCE() is as follows -

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

Case 3 - Use CASE statement

The syntax of the 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 the 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)

Use the insert command to insert some records in the table. 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)

Use the select statement to display all records in the table. 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)

Now let us 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 showing 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 showing NULL replaced with 0-

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

Case 3 - Use 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 showing NULL replaced with 0 -

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

The above is the detailed content of What are the different ways to replace null values ​​using a SELECT statement in MySQL?. For more information, please follow other related articles on the PHP Chinese website!