Learn all about the MySQL IFNULL Function including syntax and practical application:
In most programming languages, a lot of time it’s required to evaluate NULL conditions or vice versa. It’s not very uncommon to see if-else conditions based on a null check or a given expression evaluating NULL
MySQL provides IFNULL() function for returning a default/preset value if the expression evaluates to NULL else returns the evaluated value.
=> Check all MySQL Tutorials Here
Table of Contents:
MySQL IFNULL Function
Syntax and Description
IF NULL function in MySQL returns the specified value if the expression is NULL else returns the value evaluated by the expression.
Syntax
IFNULL(expression, alternate_value)
Here both the arguments – expression and alternate-value can be literal values or an expression.
Examples:
Let’s understand the above syntax with the help of some basic examples:
SELECT IFNULL('Hello!','EXPRESSION EVALUATED TO NULL');
//Output
Hello!
Here, if you see the expression to be evaluated is “Hello!” – since this is not NULL, the result would be “Hello!”
Let’s replace the value of the expression with NULL – in that case, you would see the default value getting printed.
SELECT IFNULL(NULL,'EXPRESSION EVALUATED TO NULL');
//Output
EXPRESSION EVALUATED TO NULL
Here, since the expression is evaluated to NULL, the default value would be returned as output.
Applications of MySQL IFNULL Function
The IFNULL function is generally used along with SELECT queries or fetching/assigning values of derived columns.
Let’s understand this with the help of an example.
Test Data
Table – Employee
Fields – Id (R), Name (R), OfficePhone (O), MobileNumber(R)
Here R and O indicates – Required and Optional fields
Let’s create this table and insert some sample data (In the INSERT script – you can see that for few records – we are inserting officePhone as NULL)
-- table creation
CREATE TABLE employee (id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), officePhone VARCHAR(15),
mobilePhone VARCHAR(15) NOT NULL);
-- insert sample data
INSERT INTO employee(name, officePhone, mobilePhone) VALUES("anand", "2568795","+919876543210"), ("maya", "2568785","+919867711111"), ("diana", "2344567","+919890543211"), ("abhishek", "3412345","+917865412333"),("arpit", NULL,"+919776412223"),("alexander", NULL,"+52562345678");
Suppose, we need to SELECT all the data from the table and list down a contact_number where the employee can be reached, the contact number should be officePhone – if officePhone is not available (or NULL), then we should have their mobileNumber.
Let’s see how we can make use of the IFNULL function.
SELECT name, IFNULL(officePhone, mobilePhone) AS contact_number FROM employee;
You can see in the above query that we are using the IFNULL function and the specified column names to be evaluated for NULL values. Here, if the officePhone value for a row is NULL, then the mobilePhone would be returned else the value of officePhone.
//Output
Using Queries as an expression within IFNULL() statements. You can see in the output above for employee names – “arpit” and “alexander” since the officePhone value was NULL, the mobilePhone values were returned.
The arguments inside the IFNULL function can also be expressions like the SELECT queries themselves.
For Example: Suppose the result of the SELECT query is NULL, then the default value can be returned, else the evaluated value of the expression can be returned.
For Example: Let’s use a SELECT query inside the IFNULL() function for evaluation.
SELECT IFNULL((SELECT officePhone FROM employee WHERE id=5), 'NOT AVAILABLE') AS phone_number
//Output
NOT AVAILABLE
As the result of the query SELECT officePhone FROM employee WHERE id=5 is NULL, we will get the default value as the output.
Similar to the expression being used for evaluation, we can also use the expression for the default value to be returned.
SELECT IFNULL((SELECT officePhone FROM employee WHERE id=5), (SELECT mobilePhone FROM employee WHERE id=5)) AS phone_number
//Output
+919776412223
Here, we can see that the expression – SELECT officePhone FROM employee WHERE id=5 is evaluated to NULL, hence the expression for default value is evaluated and the value is returned.
Frequently Asked Questions
Q #1) What is IFNULL in MySQL?
Answer: IFNULL is a pre-defined function supported by MySQL, that helps to evaluate an expression or literal strings and returns the default specified value if the evaluated expression is NULL.
Q #2) How do I use IFNULL in MySQL with expressions and values?
Answer: The syntax of IFNULL() function is
IFNULL(expression, default_value/expression)
Let’s see some examples:
SELECT IFNULL(NULL,"DEFAULT VALUE");
// Output – DEFAULT VALUE
SELECT IFNULL(1,"DEFAULT VALUE");
// Output – 1
Q #3) Which versions of MySQL support IFNULL function?
Answer: The MySQL IFNULL() function is supported in MySQL 4.0 and above.
Conclusion
In this tutorial, we learned about the MySQL IFNULL function. It’s a useful function that evaluates the given expression or value and returns a default specified value or expression if the original expression evaluates to NULL
This is heavily used in SELECT queries and derived queries where we want to evaluate an existing column and return some other value if the column/expression to be evaluated is NULL.
=> Visit Here To Read Through All MySQL Tutorials