MySQL ifnull() function; Through this tutorial, i am going to show you how to use MySQL IFNULL function with the help of examples.
MySQL IFNULL() function
The IFNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression
Syntax of MySQL IFNULL() function
The syntax of the IFNULL function is:
IFNULL(expression_1,expression_2);
If Expression_1 is not NULL then it will return from Expression_1, otherwise it gives Expression 2.
Example 1 – MySQL IFNULL() function
Let’s see example of IFNULL function.
CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, businessphone VARCHAR(15), homephone VARCHAR(15) );
Next, we need to insert data into the users table using the following query :
INSERT INTO users(name,homephone,businessphone) VALUES('Tommy Hill','(541) 754-3009',NULL), ('John Smith',NULL,'(541) 754-3110'), ('Mark Greenspan','(541) 754-3010','(541) 754-3011'), ('Kelvin Row',NULL,'(541) 754-3111');
If you want to fetch some users have only home phone or business phone. You can use the following query for that:
SELECT name, businessphone, homephone FROM users;
# Result of the above Query +------------+-----------+----------+-----------+------------------- | id | name | homephone | businessphone | +------------+-----------+----------+-----------+------------------- | 5 | Tommy Hill | NULL | (541) 754-3009 | | 6 | John Smith | (541) 754-3110 | NULL | | 10 | Mark Greenspan | (541) 754-3010 | (541) 754-3111 | | 11 | Kelvin Row | (541) 754-3111 | NULL | +------------+-----------+----------+-----------+-------------------
If we can obtain contact details of users like home and business phone numbers. If there is no business phone in any row, then just be a home phone number. If someone has a home phone number but not a business phone number
In the above situation, you can use the MySQL IFNULL function. IFNULL function returns a home phone if the business phone is NULL.
SELECT name, IFNULL(businessphone, homephone) phone FROM users;
#The above query return this result from users table +------------+-----------+----------+----------- | id | name | phone | +------------+-----------+----------+----------- | 5 | Tommy Hill | (541) 754-3009 | | 6 | John Smith | (541) 754-3110 | | 10 | Mark Greenspan | (541) 754-3111 | | 11 | Kelvin Row | (541) 754-3111 | +------------+-----------+----------+-----------
There are two condition, if you want to fetch the business and home phone number of users table; as follows:
- If the users business phone is not exist in database table, in that case it return home number.
- If the users home phone is not exist in database table, in that case it return business number.
Conclusion
In this tutorial, we have learn how to use MySQL IFNULL function.