This section helps you to learn about the MySQL IFNULL() function. The IFNULL function is a part of the MySQL control flow function used for handling NULL values.
The IFNULL function accepts two expressions, and if the first expression is not null, it returns the first arguments. If the first expression is null, it returns the second argument. This function returns either string or numeric value, depending on the context where it is used.
We can use the IFNULL function with the following syntax:
IFNULL (Expression1, Expression2)
It returns expression1 when the expression1 is not null. Otherwise, it will return expression2.
|Expression 1||Required||This expression is used to check whether it is NULL or not.|
|Expression 2||Required||It will return when the expression 1 is NULL.|
The IFNULL function can support the following MySQL versions:
Let us understand the MySQL IFNULL() function with the following examples. We can use the IFNULL function with the SELECT statement directly.
In the above function, the MySQL statement checks the first expression. If the first expression is not NULL, it will return the first expression, which is zero.
SELECT IFNULL("Hello", "rookienerd");
The above MySQL statement checks the first expression. If the first expression is not NULL, it will return the first expression, which is 'Hello' value.
The following MySQL statement checks the first expression. If the first expression is not NULL, it will return the first expression. Otherwise, it will return the second expression, which is five (5).
Here, we are going to create a table 'student_contacts' and perform the IFNULL() function.
CREATE TABLE `student_contacts` ( `studentid` int unsigned NOT NULL AUTO_INCREMENT, `contactname` varchar(45) NOT NULL, `cellphone` varchar(20) DEFAULT NULL, `homephone` varchar(20) DEFAULT NULL, );
Now, you need to insert data into a table. After inserting the values into the table, execute the following query.
SELECT contactname, cellphone, homephone FROM student_contacts;
It will display the output that contains all rows and columns. Here, we can see that some of the contacts have only a cell phone or home phone number.
In the above output, we will get all contacts name weather cell phone, and home phone number is available or not. So, in that case, the IFNULL() function plays an important role.
Now, run the following MySQL query. This statement returns the home phone number if the cell phone is NULL.
SELECT contactname, IFNULL(cellphone, homephone) phone FROM student_contact;
When the above MySQL statement runs successfully, it will give the following output.