MySQL NULLIF Function with Examples

How to Use the MySQL NULLIFF() Function

In this tutorial, you'll learn how to use the MySQL NULLIF() function. NULLIF is a function to compare two expressions and return NULL if they are equal. Otherwise, it returns the first expression.


The syntax of the NULLIF() function is as follow:

NULLIF (expression_1, expression_2)

NULLIF Examples

Example 1

The following statement compares two integers and returns as Message:

SELECT NULLIF (1, 2) AS Message;

Since integer 1 is not equal to 2, NULLIF() returns 1, which is the first expression.

Example 2

The following statement returns NULL because both expressions in the NULLIF() function are equal.

SELECT NULLIF ("Apple", "Apple") AS Message;

Example 3

The following statement returns NULL, because both expressions are equal.

SELECT NULLIF ("The tutorial of NULLIF function.", "THE TUTORIAL of NULLIF Function.") AS Message;

As you can see, both expressions are not the same if you consider the lowercase and uppercase characters are different. Since NULLIF is case-insensitive, the lowercase and uppercase characters treated the same.

Example 4

The following illustrates an example of using SUM() function inside NULLIF():

SELECT NULLIF(SUM(1+2), 2) AS Message;

The statement returns 3. The integer 3 is the returned value of SUM(1+2), greater than 2.

In this tutorial, you've learned how to use the MySQL NULLIF function. NULLIF is used to return the NULL value if both expressions are equal. Otherwise, it returns the first expression.

See also:
MySQL LIKE Operator with Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function with Examples

Leave a Comment