Overview
TheNULLIF()
function allows us to replace a given value with null if it matches a specific criterion.
Syntax
The following illustrates the syntax of theNULLIF
function:
NULLIF
function takes two arguments:
- The first argument is the value we want to evaluate
- The second argument is the value we want to treat as null if the first argument matches it
The Output:
If the first argument matches the second argument, the
If the first argument matches the second argument, the
NULLIF()
function returns NULL. Otherwise, it returns the first argument as-is.Examples
Case #1: Handling Equal Values
In this case, theNULLIF
function is used to compare the values 4 and 4.
NULL
since the two values being compared are equal (4 = 4).
Case #2: Handing Different Values
In this example, we want to use theNULLIF
function to manage different values.
9
because the second value in the NULLIF
function is 0 (The two values are not equal).
Case #3: String Comparison
In this case, theNULLIF
function compares the strings ‘L’ and ‘O’.
L
because the two strings being compared (‘L’ and ‘O’) are not equal. Therefore, the function returns the first string.
Case #4: Handling Default Values
Suppose we have anemployees
table with columns for name
and salary
. This query retrieves employee names and their adjusted salaries, where a salary of 0 is replaced with NULL:
NULLIF
function checks if the salary
value is 0. If it is, the function returns NULL - otherwise, it returns the original salary
value.
Case #5: Avoiding Division by Zero
Suppose we have afractions
table with columns, a numerator
and a denominator
.
SELECT
statement:
NULLIF
function is applied to the denominator
column. If the denominator
is 0, the function returns NULL, avoiding division by zero.