Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
797 Views
