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.

MySQL NULLIF() Syntax

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.

Leave a Reply

Your email address will not be published. Required fields are marked *