The NULLIF() function allows us to replace a given value with null if it matches a specific criterion.
The following illustrates the syntax of the NULLIF function:
From the syntax above, we learn that the 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.
If the first argument matches the second argument, the NULLIF() function returns NULL. Otherwise, it returns the first argument as-is.
In this case, the NULLIF function is used to compare the values 4 and 4.
The result will be NULL since the two values being compared are equal (4 = 4).
In this example, we want to use the NULLIF function to manage different values.
The result will be 9 because the second value in the NULLIF function is 0 (The two values are not equal).
In this case, the NULLIF function compares the strings 'L' and 'O'.
The result will be L because the two strings being compared ('L' and 'O') are not equal. Therefore, the function returns the first string.
Suppose we have an employees 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:
Display the records of the table:
This query retrieves employee names and their adjusted salaries, where a salary of 0 is replaced with NULL:
The NULLIF function checks if the salary value is 0. If it is, the function returns NULL - otherwise, it returns the original salary value.
Suppose we have a fractions table with columns, a numerator and a denominator.
Display the table using the SELECT statement:
Here, the NULLIF function is applied to the denominator column. If the denominator is 0, the function returns NULL, avoiding division by zero.
The result is shown in the result column.