SQL NULLIF

What is SQL NULLIF?

The SQL NULLIF function is used to compare two expressions or values and return NULL if they are equal. It provides a way to handle specific cases where you want to treat equality as a special condition by substituting it with a NULL value. NULLIF is a valuable tool for data transformation and simplifying query results, particularly in cases where you need to suppress or hide specific values.

When you would use it

You would use the SQL NULLIF function when you want to replace a value with NULL in your query result if it matches a specified condition. NULLIF is particularly useful when you need to handle equality as a distinct case, such as when you want to hide or suppress certain results in your query.

Syntax

The syntax for the SQL NULLIF function is as follows:

NULLIF(expression1, expression2)

• expression1: The first expression or value you want to compare.
• expression2: The second expression or value you want to compare to expression1. If expression1 is equal to expression2, NULL is returned; otherwise, expression1 is returned.

Parameter values

• expression1: The first expression or value you want to compare.
• expression2: The second expression or value you want to compare to expression1.

Example query

Suppose we have a table named "products" with columns "product_id," "product_name," and "discontinued." We want to retrieve the product names but replace the names of discontinued products with NULL using the NULLIF function:

SELECT product_id, NULLIF(product_name, 'Discontinued') AS adjusted_product_name
FROM products;

In the above query, we use the NULLIF function to replace product names with 'Discontinued' with NULL in the result set.

Example table response

Assuming the "products" table contains the following data:

| product_id | product_name   | discontinued |
|------------|---------------- | ----------- |
| 1          | Widget         | 0           |
| 2          | Gadget         | 1           |
| 3          | Discontinued   | 1           |
| 4          | Gizmo          | 0           |
| 5          | Widget XL      | 0           |

The query mentioned earlier would return the following result:

| product_id | adjusted_product_name |
|------------|----------------------- |
| 1          | Widget                |
| 2          | Gadget                |
| 3          | NULL                  |
| 4          | Gizmo                 |
| 5          | Widget XL             |

This result includes product names, but it replaces 'Discontinued' with NULL using the NULLIF function.

Use cases

• Replacing specific values with NULL in query results.
• Suppressing or hiding data based on specific conditions.
• Handling equality as a distinct case.

SQL languages this is available for

The SQL NULLIF function is a standard SQL feature and is available in most relational database management systems (RDBMS) that support SQL. This includes popular RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax and behavior may vary slightly between database systems, but the fundamental functionality remains the same.