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

Question

Aryan Kumar · Answer

The NULLIF() function in SQL 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

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

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

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

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

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.

forum

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

Can you answer this question?

1 Answers