SQL - NULLIF() Function

---

---

The SQL NULLIF() function is, used to verify whether the two expressions are equal or not.

It accepts two parameters expr1 and expr2 and returns NULL, if both the expression are equal; else returns the first expression.

Note − It always returns the first expression if both expressions are not equal, no matter whether the first expression is greater, or the second expression.

Syntax

Following is the syntax of the SQL NULLIF() function −

NULLIF(expr1, expr2); 

Parameters

• expr1 − It is the first expression.

• expr2 − It is the second expression.

Return value

This function returns NULL if both expressions are equal.

Example

If the both expressions are equal, the NULLIF() function returns a NULL value.

The SQL NULLIF() function is used in the following program to determine whether the first expression 100 is equal to the second expression 100 or not.

SELECT NULLIF(100, 100) As Result; 

Output

Following is the output of the above query −

+--------+
| Result |
+--------+
| NULL   |
+--------+

Example

If both expressions are different, this function returns the first expression.

In this example, we are using the SQL NULLIF() function to verify whether the first expression '50' is equal to the second expression '30' or not.

SELECT NULLIF(50, 30) As Result; 

Output

On executing the above statement, it produces the following output −

+--------+
| Result |
+--------+
| 50     |
+--------+

Example

Following is another example of the SQL NULLIF() function, here, we are using this function to compare two strings, whether they are equal or not.

DECLARE @EXPR1 VARCHAR(10) = 'HELLO', @EXPR2 VARCHAR(10) = 'HELLOWORLD';
SELECT NULLIF(@EXPR1, @EXPR2) AS RESULT;

Output

The above SQL statement produces the following output −

+--------+
| Result |
+--------+
| HELLO  |
+--------+

Example

If we pass the first expression value as NULL to the function, this function throws an error.

In this program, we are passing the first expression value as NULL to the NULLIF() function to compare it with the second expression 20.

SELECT NULLIF(NULL, 20) As Result; 

Output

After executing the above query, it produces the following output −

The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

Example

You can also pass the table column as an argument to the SQL NULLIF() function to check whether the content of the two columns is equal or not. Assume we have created a table with the name Customer using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
   ID INT NOT NULL,    
   NAME VARCHAR (20) NOT NULL,    
   AGE INT NOT NULL,    
   ADDRESS CHAR (25) ,    
   SALARY DECIMAL (18, 2));

Now, let's insert some records in to the Customers table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Ganesh', 32, 'Punjab', 2200.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Rohan', 32, 'Delhi', 2250.00 );

The following SQL query checks whether the customer Age(first expression) is equal to the specified second expression or not, if equal, it returns NULL; else returns the first expression(customer age) in the Customers table −

SELECT ID, NAME, AGE, NULLIF(AGE, 32) AS RESULT FROM CUSTOMERS;

Output

The above SQL query generate the following output −

+----+----------+-------+---------+
| ID | NAME     | AGE   | RESULT  |
+----+----------+-------+---------+
|  1 | Ramesh   | 32    | NULL    |
|  2 | Khilan   | 25    | 25      |
|  3 | kaushik  | 23    | 23      |
|  4 | Chaitali | 25    | 25      |
|  5 | Ganesh   | 32    | NULL    |
|  6 | Rohan    | 32    | NULL    |
+----+----------+-------+---------+