NULLIF()
function allows us to replace a given value with null if it matches a specific criterion.
NULLIF
function:
NULLIF
function takes two arguments:
NULLIF()
function returns NULL. Otherwise, it returns the first argument as-is.NULLIF
function is used to compare the values 4 and 4.
NULL
since the two values being compared are equal (4 = 4).
NULLIF
function to manage different values.
9
because the second value in the NULLIF
function is 0 (The two values are not equal).
NULLIF
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.
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:
NULLIF
function checks if the salary
value is 0. If it is, the function returns NULL - otherwise, it returns the original salary
value.
fractions
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.