Returning a value even if there is no result in a MySQL query?

You can use IFNULL() function from MySQL to return a value even if there is not result. Let us create a table. Te query to create a table.

mysql> create table IfNullDemo
   −> (
   −> Id int,
   −> Name varchar(100)
   −> );
Query OK, 0 rows affected (0.60 sec)

Insert some records in the table with the help of insert command. The query is as follows −

mysql> insert into IfNullDemo values(1,'John');
Query OK, 1 row affected (0.18 sec)

mysql> insert into IfNullDemo values(200,'Sam');
Query OK, 1 row affected (0.21 sec)

mysql> insert into IfNullDemo values(204,'Carol');
Query OK, 1 row affected (0.14 sec)

mysql> insert into IfNullDemo values(510,'Johnson');
Query OK, 1 row affected (0.18 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from IfNullDemo;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
|   1 | John     |
| 200 | Sam      |
| 204 | Carol    |
| 510 | Johnson  |
+------+---------+
4 rows in set (0.00 sec)

Let us first return a value for TRUE condition −

The query is as follows −

mysql> select ifnull((select Id from IfNullDemo where Id = 200),'No Result Found') As ResultFound;

The following is the output −

+-------------+
| ResultFound |
+-------------+
| 200         |
+-------------+
1 row in set (0.00 sec)

Now, let us return a value if there is no result using the IFNULL method. The query is as follows −

mysql> select ifnull((select Id from IfNullDemo where Id = 400),'No Result Found') As ResultFound;

The following is the output −

+-----------------+
| ResultFound     |
+-----------------+
| No Result Found |
+-----------------+
1 row in set (0.00 sec)

Jennifer Nicholas

e

Updated on: 30-Jul-2019

7K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started