MySQL ISNULL

Syntax of MySQL ISNULL() Function

The following is the syntax of MySQL ISNULL function:

Parameters of MySQL ISNULL() Function

The MySQL ISNULL function takes one parameter:

• expression: The MySQL ISNULL function takes an expression as a parameter. This is a mandatory parameter. This expression can be a numeric value, a string, or any variable value, etc.

Return Value of MySQL ISNULL() Function

• boolean value.

The MySQL ISNULL function returns either true or false based on whether the expression is null or not.

How does the MySQL ISNULL() Function Work?

The MySQL ISNULL function is used to check whether the passed expression is a null value or not. It takes an expression and returns 1 if the expression is null otherwise it returns 0. This expression could be a column, a string, or even an integer value.

What is null in MySQL?

Null in MySQL refers to empty or no value. In the context of a table in MySQL, null values are fields that have been left blank.

Examples

In this section, we will be going through some examples to have a better understanding of MySQL's ISNULL function. In this section, we will refer to the following Employees table for reference:

Example 1: Using MySQL ISNULL Function With the WHERE Clause

In this example, we will display all the tuples in our Employees table where the salary is null.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to display the data of all the employees whose salary was null. Thus to filter the data we needed to use the Where clause. Thus we have passed the ISNULL(salary) which will filter the columns where salary is null (using ISNULL(salary) = 1) and we will have the desired table.

Example 2: Using MySQL ISNULL Function with the WHERE Clause

In this example, we will display all the tuples in our Employees table where the city is not null.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to display the data of all the employees whose city was not null. Thus to filter the data we needed to use the Where clause. Therefore we have passed the ISNULL(salary) which will filter the columns where the city is not null (using ISNULL(city) = 0) and we will have the desired table.

Example 3: Using MySQL ISNULL Function With Multiple Columns to Display Null Values.

In this example, we will display all the tuples in our Employees table where either the salary or the designation is null.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to display the data of all the employees whose either salary or designation was null. Thus to filter the data we needed to use the Where clause. Therefore we have passed the ISNULL(designation, salary) which will filter the columns where either the salary or the designation is null (using ISNULL(designation, salary) = 1) and we will have the desired table.

Example 4: Using MySQL ISNULL Function with Multiple Columns to Display Non-Null Values.

In this example, we will display all the tuples in our Employees table where both the salary and the designation is not null.

Code:

Output:

Explanation of the example:

In the above example, we were supposed to display the data of all the employees whose both salary and designation were not null. Thus to filter the data we needed to use the Where clause. Therefore we have passed the ISNULL(designation, salary) which will filter the columns where both the salary or the designation is not null (using ISNULL(designation, salary) = 0) and we will have the desired table.

See Also

• IFNULL() function in MySQL