MySQL NULL Functions

MySQL IFNULL() and COALESCE() Functions

The MySQL ISNULL() function is used for checking whether an expression is NULL or not. This function returns 1 if the expression passed is NULL, else it returns 0. The ISNULL() function accepts the expression as a parameter and returns an integer a value 0 or 1 depending on the parameter passed.

Look at the following “Products” table:

Suppose that the “UnitsOnOrder” column is optional, and may contain NULL values. Look at the following SELECT statement:SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder) FROM Products; In the example above, if any of the “UnitsOnOrder” values are NULL, the result will be NULL.

What are NULL Values in SQL?

Null values are the placeholders in the database when we have the data missing, or the required data is not available. A null value is not a part of any particular data type, it is a flexible data type and can be put in the column of any data type be it string, int, blob or CLOB datatype. Null values come in handy while the cleaning of data during the exploratory analysis of data Null values help us in removing the ambiguity arising in data. Also, null values are beneficial to maintain uniform datatype across the column.

Example: Imagine if the user by mistake enters the date of birth in the column of mobile number then ambiguity could arise when contact needs to be established.

To overcome this we run a check on data before insertion and update any data which is not of date datatype with the null value.

Why do we need NULL Functions?

Null functions are required to perform operations on the null values stored in our database. We can perform functions on NULL values, which explicitly recognize if a value is null or not. Using this recognizing capacity, one can further perform operations on the null values like the aggregate functions in SQL. Some of the functions are as follows:

MySQL IFNULL() Function

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL. The example below returns 0 if the value is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;

MySQL COALESCE() Function

Or we can use the COALESCE() function, like this:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;

Syntax:

ISNULL(expression)

Parameters Used:

expression – It is used to specify the expression.

Return Value:
The MySQL ISNULL() function returns 1 if the expression passed is a NULL expression, else it returns 0.

More Example of MySQL ISNULL()

Let us take a look at the below examples where we use ISNULL() with the SELECT statement.

And the output is –

• In the first query, we pass NULL to the ISNULL() function and it returns 1 – meaning the value passed to the function is indeed NULL. 
• In the second query, we pass 25 to the ISNULL() function and it returns 0 – meaning the value passed to the function is not NULL.
• In the third query, we pass 25 + NULL to the ISNULL() function and it returns 1. This is because adding NULL to a number results to NULL.
• In the fourth query, we pass 1/0 to the ISNULL() function and it returns 1. This is because dividing any number with 0 will give a NULL value in MySQL.
• In the last query, we pass “Jessica” to the ISNULL() function and it returns 0. 

Working With Tables

Consider the below ‘Persons’ table.

Let us use the MySQL IF() condition and the ISNULL() function to check if an employee has a value specified in the Work column. If yes, display the value; else display ‘N/A’. Also, let us display the ID and the Name of the person. The query is –

And the output is –

Examples of MySQL IFNULL()

Let us take a look at some basic examples of IFNULL() now. Consider the below queries.

And the output is -l

In the first query we check if 500 is a NULL value or not. Since it is not a NULL value, the function returns 500 as the output. In the second query, we check if ‘Jay’ is a NULL value or not. Since it is not a NULL value, the function returns ‘Jay’ as the output.

Let us look at a different example now.

And the output is –

Here, since the value is NULL, the value in the ‘alt_value’ parameter gets returned

Working With Tables

Consider the ‘Persons’ table that we used earlier. We can use IFNULL() to handle NULL values in the result set. Let us use the IFNULL() function to handle the NULL values in the MiddleName, LastName and Mobile columns. If there is a NULL value in any of these columns, simply display an empty string. The query for this is –

And the output is –

Conclusion

Checking for NULL values and handling them while displaying results is a key database operation and MySQL ISNULL() and IFNULL() are very crucial to these operations. I would encourage you to check out the below references.