MySQL ISNULL() and IFNULL() Functions

By /
ISNULL And IFNULL Functions

In this tutorial, we will study the MySQL ISNULL() and IFNULL() functions. Checking for NULL values and handling them while displaying results is a key database operation. While you can always add a WHERE clause to check for NULL in a column of a table, MySQL provides you with additional ways to tackle this problem.

The MySQL ISNULL() function is used to check for any NULL values in the expression passed to it as a parameter. If the expression has/results to NULL, it displays 1. If the expression does not have or result in NULL, the function returns 0.

The MySQL IFNULL() function is used to return a specified value if the expression is NULL. If the expression is not NULL, IFNULL() returns the expression.

Syntax of MySQL ISNULL()

ISNULL(expression)Code language: SQL (Structured Query Language) (sql)

Where ‘expression’ is the value that needs to be checked.

Syntax of MySQL IFNULL()

IFNULL(expression, alt_value)Code language: SQL (Structured Query Language) (sql)

Where ‘expression’ is the value that needs to be checked and,

‘alt_value’ is the value the function returns if ‘expression’ is NULL.

Example of MySQL ISNULL()

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

SELECT ISNULL(NULL); 
SELECT ISNULL(25); 
SELECT ISNULL(25 + NULL);  
SELECT ISNULL(1/0); 
SELECT ISNULL("Jessica");Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL ISNULL Basic Examples
  • 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 in 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.

Persons Table MySQL ISNULL
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 –

SELECT ID, FirstName, IF(ISNULL(Work)=1, 'N/A', Work) AS 'Work Number' FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Isnull Table Example

Examples of MySQL IFNULL()

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

SELECT IFNULL(500, 100); 
SELECT IFNULL('Jay', 'Ruby');Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Ifnull Basic Example

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.

SELECT IFNULL(NULL, 'We have a NULL value');Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Ifnull Basic Example 2

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 –

SELECT ID, FirstName, IFNULL(MiddleName, '') AS 'Middle Name', 
IFNULL(LastName, '') AS 'Last Name', 
IFNULL(Mobile, 'N/A') AS 'Mobile No' 
FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Ifnull Table Example

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.

References