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:

snippet
NULLIF (Expression1, Expression2)

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

Parameter

Parameter Requirement Descriptions
Expression 1 Required It specify the first expression for comparison.
Expression 2 Required It specify the second expression for comparison.

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

snippet
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:

Output
NULL

Example 2

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

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

Output:

Output
Hello

Example 3

snippet
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:

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.

snippet
SELECT 1/NULLIF(0,0);

Output:

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.

snippet
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.

snippet
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.

snippet
SELECT * FROM customer;

It will give the following table:

MySQL NULLIF

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.

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

Output:

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

MySQL NULLIF
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents +