The MySQL NULLIF() Function With 5 Easy Examples

NULLIF Banner

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

MySQL NULLIF syntax
NULLIF Syntax

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()

Expr Numbers Equal
Expression – Equal Numbers
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:

Expr Numbers NotEqual
Expression – Numbers NotEqual
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.

Expression FloatingPoint Integer
Expression – FloatingPoint Integer
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:

Expr String Numbers Equal 1
Expression – String Numbers Equal
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()

Expr String Numbers
Expression – Strings which are Numbers
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.

Expr Strings
Expression – Strings
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:

Expr True False
Expression – True False
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.

Expr False True
Expression – False True
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 NameAge Table
Create NameAge Table
CREATE TABLE NameAge(Name VARCHAR(20), Age INT);
Insert Data1
Insert Data Row 1
INSERT INTO NameAge(Name, Age) VALUES('JohnDoe', 25);
Code language: SQL (Structured Query Language) (sql)
Insert Data2 1
Insert Data Row 2
INSERT INTO NameAge(Name, Age) VALUES('DoeJohn', 28);
Code language: SQL (Structured Query Language) (sql)
Insert Data3
Insert Data Row 3
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.

NameAge NULLIF
Running NULLIF() on NameAge Table
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().

References

  • To read more about how and why the type is converted when we took ("2", 2) as our argument, you can read here.
  • Also, you can refer to the official MySQL Documentation for the NULLIF() Function here.