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);Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)Even if the number is a floating-point number equivalent to the integer we use, the NULLIF() Function returns NULL.
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);Code language: SQL (Structured Query Language) (sql)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");Code language: SQL (Structured Query Language) (sql)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");Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)Our argument is (True, False), these are boolean values where True corresponds to 1 and False corresponds to 0. These values are not equal and we receive the first expression as our output.
SELECT NULLIF(False, True);Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)
INSERT INTO NameAge(Name, Age) VALUES('DoeJohn', 28);Code language: SQL (Structured Query Language) (sql)
INSERT INTO NameAge(Name, Age) VALUES('Person', 25);Code language: SQL (Structured Query Language) (sql)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;Code language: SQL (Structured Query Language) (sql)The above query makes it clear that NULLIF() Function can be very important to find many things from the data quickly.
Conclusion
The MySQL NULLIF() Function is very simple and can be crucial when trying to gather insights from a dataset. You can also read about the other Flow Control Functions such as IF() and IFNULL().