Tutorial Gateway

MySQL NULLIF Operator

by

The MySQL NULLIF operator is a control flow function, which accepts two arguments. If argument 1 is equal to argument 2, then NULL  returned. Otherwise, argument 1 returned.

The syntax behind this MySQL NULLIF is as follows:

SELECT NULLIF(exp1, exp2)

This function returns NULL if exp1 = exp2. Otherwise, it returns the expression 1 value.

MySQL NULLIF Operator Example 1

In this example, we show you the simple NULLIF operator examples. Here, the first statement returns NULL because 10 = 10 is True. The second statement returns 10 because 10 != 100. Within the last statement, we used NULL as an argument value. Here, Null != 10 means the first argument value returned.

SELECT NULLIF(10, 10);

SELECT NULLIF(10, 100);

SELECT NULLIF(NULL, 100), NULLIF(10, NULL);

OUTPUT

MySQL NULLIF Operator 1

MySQL NULLIF Example 2

We are using this Customers table for the NULLIF operator demonstration. The below screenshot shows you the data inside this MySQL table

MySQL NULLIF Operator 2

In this example, we used the MySQL NULLIF operator to check the Occupation column value against the Management. It means, if the employee occupation is Management, it returns NULL. Otherwise, the Occupation column value returned.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       NULLIF(Occupation, 'Management') AS Result,
       Income,
       Sales,
       HireDate
 FROM `MySQL Tutorial`.customer;

OUTPUT

MySQL NULLIF Operator 3

This time, we are using the MYSQL NULLIF Operator on the Occupation column and Income. If the occupation is clerical, NULL returned. Next, if the Income is 80000, it returns NULL as the income value.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Occupation,
       NULLIF(Occupation, 'Clerical') AS OResult,
       Income,
       NULLIF(Income, 80000) AS IResult,
       Sales,
       HireDate
 FROM `MySQL Tutorial`.customer;

OUTPUT

MySQL NULLIF Operator 4