---
title: "What is the purpose of the NULLIF() function in SQL and when would you use it?"  
description: "What is the purpose of the NULLIF() function in SQL and when would you use it?"  
author: "Revati S Misra"  
published: 2023-06-30  
updated: 2023-07-01  
canonical: https://www.mindstick.com/forum/158914/what-is-the-purpose-of-the-nullif-function-in-sql-and-when-would-you-use-it  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 2 minutes  

---

# What is the purpose of the NULLIF() function in SQL and when would you use it?

What is the [purpose](https://yourviews.mindstick.com/view/247/no-fail-policy-failing-its-purpose) of the NULLIF() [function in SQL](https://www.mindstick.com/forum/156839/what-is-function-in-sql-server) and when would you use it?

## Replies

### Reply by Aryan Kumar

The NULLIF() [function](https://www.mindstick.com/articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server) in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) is used to compare two expressions and return NULL if they are equal. Otherwise, the first expression is returned. The syntax for the NULLIF() function is:

SQL

```plaintext
NULLIF(expression1, expression2);
```

- `expression1` and `expression2` are the expressions that you want to compare.

If `expression1` and `expression2` are equal, then NULL will be returned. If `expression1` and `expression2` are not equal, then `expression1` will be returned.

For example, the following query will return NULL if the `first_name` and `last_name` columns are equal for a given row in the `users` table:

SQL

```plaintext
SELECT NULLIF(first_name, last_name)
FROM users;
```

If the `first_name` and `last_name` columns are equal for a given row, then NULL will be returned. If the `first_name` and `last_name` columns are not equal for a given row, then the value of the `first_name` column will be returned.

The NULLIF() function can be used to handle NULL values in a variety of ways. For example, you can use it to:

- Avoid comparing NULL values to other values.
- Filter results based on whether or not two values are equal.
- Create a new column that contains NULL values if two other columns are equal.

The NULLIF() function is a versatile tool that can be used to simplify your queries and improve the performance of your database.

Here are some other examples of how the NULLIF() function can be used:

- To return NULL if the `date_of_birth` and `hire_date` columns are equal for a given row in the `employees` table:

SQL

```plaintext
SELECT NULLIF(date_of_birth, hire_date)
FROM employees;
```

- To filter the results of a query to only rows where the `first_name` and `last_name` columns are not equal:

SQL

```plaintext
SELECT *
FROM users
WHERE NULLIF(first_name, last_name) IS NOT NULL;
```

- To create a new column that contains NULL values if the `first_name` and `last_name` columns are equal:

SQL

```plaintext
ALTER TABLE users
ADD COLUMN name_is_unique NULLIF(first_name, last_name);
```

The NULLIF() function is a powerful tool that can be used in a variety of ways. If you are working with NULL values in your SQL queries, then the NULLIF() function is a tool that you should definitely know about.


---

Original Source: https://www.mindstick.com/forum/158914/what-is-the-purpose-of-the-nullif-function-in-sql-and-when-would-you-use-it

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.