MySQL NULLIF()

This section helps you to learn about the MySQL NULLIF() function. The NULLIF function is a part of the MySQL control flow function that used for comparison in two expressions. It also helps in preventing the division by zero error in a SQL statement.

The NULLIF function accepts two expressions, and if the first expression is equal to the second expression, it returns the NULL. Otherwise, it returns the first expression.

Syntax

We can use the NULLIF function with the following syntax:

It returns Null when expression1 is equal to expression2. Otherwise, it will return expression1.

Parameter

MySQL version support

The NULLIF function can support the following MySQL versions:

• MySQL 8.0
• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0

Let us understand the MySQL NULLIF() function with the following examples. We can use the NULLIF function with the SELECT statement directly.

Example 1

In the above function, the MySQL statement checks the first expression is equal to the second expression or not. If both expressions are the same, it returns NULL. Otherwise, it will return the first expression.

Output:

NULL

Example 2

The following MySQL statement compares both expressions. If expression1 = expression2, it returns NULL. Otherwise, it will return expression1.

Output:

Hello

Example 3

The following MySQL statement compares both integer values. If they are equal, return NULL. Otherwise, it returns the first expression.

Output:

9

Example 4

In this example, we are going to understand how NULLIF() function prevents division by zero error. If we run the query "SELECT 1/0", then we get an error output. So, in that case, we will use NULLIF function as below syntax.

Output:

NULL

Example 5

Let us create a customer table for performing the NULLIF function. The following statement creates a customer table in your database.

Now, you need to insert data into a table. To insert values into the table, run the following command.

After inserting the values into the table, execute the following query.

It will give the following table:

Now, we are going to use the NULLIF function to check the qualification column value against the Btech. It means if the customer occupation is Btech, it returns NULL. Otherwise, it returns the column value.

Output:

When the above command executes successfully, it returns the following output.