The NULLIF function returns NULL if the two expressions specified as the parameters are identical, and returns the first parameter value otherwise.
NULLIF(expr1, expr2)
result :
expr1 | NULL
NULLIF(a, b) is the same of the CASE statement.
CASE
WHEN a = b THEN NULL
ELSE a
END
SELECT * FROM case_tbl;
a
=============
1
2
3
NULL
--returning NULL value when a is 1
SELECT a, NULLIF(a, 1) FROM case_tbl;
a nullif(a, 1)
===========================
1 NULL
2 2
3 3
NULL NULL
--returning NULL value when arguments are same
SELECT NULLIF (1, 1.000) FROM db_root;
nullif(1, 1.000)
======================
NULL
--returning the first value when arguments are not same
SELECT NULLIF ('A', 'a') FROM db_root;
nullif('A', 'a')
======================
'A'