SQL NULLIF

Syntax

The following is the syntax of the NULLIF function in SQL:

SELECT NULLIF(first_expression, second_expression)

In the above syntax, the NULLIF function compares the input expressions. If the "first_expression" is not equal to the "second_expression," then the output will be "first_expression". Otherwise, the outcome will be NULL. Also, the function is case-sensitive, so 'dog' and 'Dog' are also different.
 

Let's understand the working of the SQL NULLIF function with the help of CASE statements. The above expression can be explained with CASE statement as follows:

CASE
	WHEN first_expression = second_expressioin THEN NULL
	ELSE first_expression
END

Must Read SQL Clauses

Examples

As we have discussed the syntax of the NULLIF() function above, let's now understand this syntax and write some queries for better understanding.

Example 1 (Numeric expressions)

In the following example, let's use the SQL NULLIF function to compare two numeric expressions.

Code

SELECT NULLIF(2,3) As Output1;
SELECT NULLIF(2,2) As Output2;

Output
 

As in the first query, the first expression, i.e., 2, is not equal to the second, i.e., 3. Therefore the output stored in Output1 was 2, i.e., the first expression. On the other hand, in the second query, the inputs were equal; therefore, NULL was stored in Output2.

Example 2 (String Expressions)

We will compare two string expressions using the SQL NULLIF function in this example.

Code:

SELECT NULLIF("CodingNinjas", "codingninjas");
SELECT NULLIF("CodingNinjas", "CodingNinjas");

Output:

As in the first query, "CodingNinjas" is not equal to "codingninjas." Therefore the output was "CodingNinjas," i.e., the first expression. On the other hand, in the second query, the expressions were equal; therefore, NULL was the output. This example also shows that it is case-sensitive.

Example 3 (NULLIF function with table)

In this example, let's use the SQL NULLIF function to compare data in a table. Let's first create a table to perform the comparison.

Let's create a table named "Products," which consists of the product's name, its brand name, and the number of stocks left.

CREATE TABLE Products (
	SNo INT PRIMARY KEY NOT NULL,
	Brand VARCHAR(50),
	Name VARCHAR(50),
	STOCK_Left INT
)

Now, let's add some data to the table "Products" using the insert command.

INSERT INTO Products   
VALUES (1, 'Apple', 'iPhone 14', 0),
(2, 'Samsung', ' Galaxy Z Fold4', 20),
(3, 'Redmi', 'Note 9 pro max', 10),
(4, 'Apple', 'iPhone 14 plus', 0),
(5, 'Oneplus', 'OnePlus 11 5G', 0),
(6, 'Oneplus', 'OnePlus 10 Pro', 15),
(7, 'Motorola', 'Moto G stylus', 12),
(8, 'Redmi', 'Note 11 mini', 0),
(9, 'Apple', 'iPhone 13', 19),
(10, 'Samsung', 'Galaxy S23', 4),
(11, 'Apple', 'iPhone 10', NULL);

After executing the above command, the below pic shows how the table "Products" is stored in the database.

In the table, we can see in the "STOCK_Left" column that some products have 0 stock left. Therefore using the SQL NULLIF statement, we can get the information about the products left with 0 stock. We can run the following query for desired output.

SELECT * FROM Products WHERE NULLIF(STOCK_Left,0) IS NULL;

The result displayed after the above command is executed is shown below.

Note: The SQL NULLIF query we used above also retrieves the row where the STOCK_Left is NULL/undefined.
 

The above expression can be written in CASE statement in the following way:

SELECT
	SNo,
	Brand,
	Name,
	CASE
		WHEN STOCK_Left = 0 THEN 'Out of Stock'
		ELSE 'In Stock'
	END AS Status
FROM Products
WHERE Status = 'Out of Stock';

The below picture shows the output for the above code.

The CASE statement creates a new column, 'Status.' If the 'Stock_Left' is equal to 0, then the 'Status' is 'Out of Stock,' or else it is 'In Stock.' Then with WHERE we display the rows where the status is 'Out of Stock.'

Also see,  Tcl Commands in SQL

Frequently Asked Questions

What is the SQL NULLIF() function?

The NULLIF function returns a NULL value when the two input expressions are the same, and else it returns the first expression. It is used in different ways. For example, if an owner wants to check if any product is out of stock, he can compare the stock left with 0.

What is the difference between Coalesce() and NULLIF() functions?

The SQL NULLIF() function takes two expressions as input and returns the first expression if they are not equal; else returns NULL. The coalesce() takes a list of values as input, returns the first non-NULL value, and returns NULL if all values are null.

What data types can be used with NULLIF()?

The SQL NULLIF() function can be used with many data types, including numeric, string, date, time, etc. It is generally used with numeric data types to avoid division by zero errors. 

Conclusion

We expect this article was insightful and that you learned something new today. In this blog, we learned about a SQL function named NULLIF(), which returns NULL when the two input expressions are the same, else returns the first expression. We discussed its syntax and rewrote it with a CASE statement for better understanding. The blog also explains some examples which help you clarify SQL NULLIF() function.

If you want to learn more about SQL functions, do visit 

• SQL Introduction.
• MS SQL Server String Functions.
• Understanding Coalesce() function in SQL.
• Functions in SQL.
• SQL Expressions.
• LIMIT in SQL
   

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninja!