The MySQL NULLIF operator is a control flow function, which accepts two arguments. If argument 1 is equal to argument 2 then NULL will be returned otherwise, argument 1 is returned.
In this article, we will show you, How to use the NULLIF with example
MySQL NULLIF Syntax
The basic 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 will 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 first argument value will be returned.
SELECT NULLIF(10, 10);
SELECT NULLIF(10, 100);
SELECT NULLIF(NULL, 100), NULLIF(10, NULL);
OUTPUT
MySQL NULLIF Example 2
We are using this Customers table for the NULLIF operator demonstration. The below screenshot shows you the data inside this table
In this example, we used NULLIF operator to check the Occupation column value against the Management. It means, if the employee occupation is Management, NULL will be returned otherwise, Occupation column value will be returned.
SELECT EmpID,
`First Name`,
`Last Name`,
Qualification,
Occupation,
NULLIF(Occupation, 'Management') AS Result,
Income,
Sales,
HireDate
FROM `MySQL Tutorial`.customer;
OUTPUT
This time, we are using this NULLIF on Occupation column and Income. If occupation is clerical, NULL is returned. Next, if the Income is 80000, null will be returned 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
Thank You for Visiting Our Blog