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:

NULLIF (Expression1, Expression2)

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

SELECT NULLIF("rookienerd", "rookienerd");

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:

Example 2

SELECT NULLIF("Hello", "404");

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

Output:

Example 3

SELECT NULLIF(9,5);

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

Output:

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.

SELECT 1/NULLIF(0,0);

Output:

Example 5

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

CREATE TABLE 'customer' (
  'customer_id' INT UNSIGNED NOT NULL AUTO_INCREMENT,
  'cust_name' VARCHAR(45) NOT NULL,
  'occupation' VARCHAR(45) NOT NULL,
  'income' VARCHAR(15) NOT NULL,
  'qualification' VARCHAR(45) NOT NULL
);

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

INSERT INTO 'myproductdb'.'customer' ('cust_name', 'occupation', 'income', 'qualification') VALUES ('John Miller', 'Developer', '20000', 'Btech');
INSERT INTO 'myproductdb'.'customer' ('cust_name', 'occupation', 'income', 'qualification') VALUES ('Mark Robert', 'Enginneer', '40000', 'Btech');
INSERT INTO 'myproductdb'.'customer' ('cust_name', 'occupation', 'income', 'qualification') VALUES ('Reyan Watson', 'Scientists', '60000', 'MSc');
INSERT INTO 'myproductdb'.'customer' ('cust_name', 'occupation', 'income', 'qualification') VALUES ('Shane Trump', 'Businessman', '10000', 'MBA');
INSERT INTO 'myproductdb'.'customer' ('cust_name', 'occupation', 'income', 'qualification') VALUES ('Adam Obama', 'Manager', '80000', 'MBA');
INSERT INTO 'myproductdb'.'customer' ('cust_name', 'occupation', 'income', 'qualification') VALUES ('Rincky Ponting', 'Cricketer', '200000', 'Btech');

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

SELECT * FROM customer;

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.

SELECT cust_name, occupation, qualification, 
NULLIF (qualification,"Btech") result 
FROM myproductdb.customer;

Output:

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