The MySQL NULLIF Function is very useful when it comes to checking if two values are equal and getting a value in return. It is categorized as a Flow Control Function.
A Flow Control Function means you can control the flow of the program based on the return value given by a function. The NULLIF() Function is simple to execute and can be a crucial decision-maker for our MySQL program.
Syntax of MySQL NULLIF
As we can see above, the MySQL NULLIF() Function requires 2 arguments. Both the arguments are required for the function to work. Even if one parameter is missing it will give an error. Once the NULLIF() Function is populated with two equal expressions it will return
NULL, else if both the expressions are not equal then it will return the first expression as the output.
How To Use MySQL NULLIF()?
Now let’s look at some examples to better our understanding of this function:
1. Comparing Numeric Values Using MySQL NULLIF()
SELECT NULLIF(3, 3);
In the above example, we have two numbers i.e. (3, 3) as our argument. Both the numbers are equal thus it gives us
NULL as output.
Now let’s see what happens when the numbers are not equal:
SELECT NULLIF(8, 2);
Our argument above is
(8, 2) where
8 is greater than
2 it is not equal to
2 thus it gives us the first expression which is
8 as the output.
SELECT NULLIF(3.0, 3);
Even if the number is a floating-point number equivalent to the integer we use, the
NULLIF() Function returns
2. Comparing A String And A Number Using NULLIF()
Now we’ll see what is the output when one argument is a string and another is a number:
SELECT NULLIF("2", 2);
In MySQL when we evaluate a number which is a string with a number it is implicitly converted to a number. The same occurs above and we get
NULL as our output.
3. Comparing Strings Using NULLIF()
SELECT NULLIF("2", "8");
The above example is similar to the one we saw earlier. Here both the expressions are numbers expressed as strings and
8 is greater than 2 thus we receive the first expression as our output.
SELECT NULLIF("MySQLCode", "mysqlcode");
Here we have two strings that are differentiated by case. The output is
NULL because the case is ignored here.
4. Comparing Boolean Values Using NULLIF()
Let us now compare boolean values using the NULIF() Function:
SELECT NULLIF(True, False);
Our argument is (True, False), these are boolean values where
True corresponds to
False corresponds to
0. These values are not equal and we receive the first expression as our output.
SELECT NULLIF(False, True);
Now if we interchange the expression from (True, False) to (False, True) we will get
0 as our output which is the first expression because the expressions are not equal.
5. Using MySQL NULLIF() with a table
Now we will create a table from which our understanding of the function can become crisp and clear.
CREATE TABLE NameAge(Name VARCHAR(20), Age INT);
INSERT INTO NameAge(Name, Age) VALUES('JohnDoe', 25);
INSERT INTO NameAge(Name, Age) VALUES('DoeJohn', 28);
INSERT INTO NameAge(Name, Age) VALUES('Person', 25);
Once the table is created we will run NULLIF() Function to return
NULL where the Age is qual to 28.
SELECT Name, NULLIF(AGE, 28) AS "AGE = 28" FROM NameAge;
The above query makes it clear that NULLIF() Function can be very important to find many things from the data quickly.