The NULLIF function in SQL is used to return NULL when two expressions are equal; otherwise, it returns the value of the first expression. The syntax for NULLIF is as follows:
NULLIF(expression1, expression2)
- expression1: The first expression to evaluate.
- expression2: The second expression to compare against expression1.
If expression1 and expression2 evaluate to the same value, NULLIF returns NULL. If they do not, it returns the value of expression1.
Example
Here’s an example to illustrate how to use NULLIF. Let's say you have a table named orders with a column discount. You want to return the discount, but if the discount is 0, you want it to return NULL.
SELECT
order_id,
NULLIF(discount, 0) AS discount_value
FROM
orders;
In this example:
- If discount is 0, the NULLIF function returns NULL.
- If discount has any other value, it returns that value.
Real Example with Condition
You can also use NULLIF in a more complex query or calculation. For instance, if you want to compute the average discount but replace 0 with NULL:
SELECT
AVG(NULLIF(discount, 0)) AS average_discount
FROM
orders;
In this case, any 0 discounts will be treated as NULL in the calculation of the average, potentially giving you a more accurate result.
By using NULLIF, you can effectively handle cases where you want to avoid using specific values in your calculations or results.