MySQL NULLIF Operator

The MySQL NULLIF operator is a control flow function that accepts two arguments. If argument 1 is equal to argument 2, then NULL is returned. Otherwise, argument 1 returned. The syntax behind this NULLIF is as follows:

SELECT NULLIF(exp1, exp2)

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

MySQL NULLIF Operator Example

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);
NULLIF Operator Example 1

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

Customer Table

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 is returned.

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

This time, we are using the NULLIF Operator on the Occupation column and Income. If the occupation is clerical, NULL ill return. 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 customer;
NULLIF Operator 4