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.
We can use the NULLIF function with the following syntax:
It returns Null when expression1 is equal to expression2. Otherwise, it will return expression1.
MySQL version support
The NULLIF function can support the following MySQL versions:
Let us understand the MySQL NULLIF() function with the following examples. We can use the NULLIF function with the SELECT statement directly.
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.
The following MySQL statement compares both expressions. If expression1 = expression2, it returns NULL. Otherwise, it will return expression1.
The following MySQL statement compares both integer values. If they are equal, return NULL. Otherwise, it returns the first expression.
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.
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.
When the above command executes successfully, it returns the following output.