The IFNULL function is working like the NVL function; however, only the NVL function supports set data type as well. The IFNULL function (which has two arguments) returns expr1 if the value of the first expression is not NULL or returns expr2, otherwise.
Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.
For example, if a type of a is INT and b is BIGINT, then IFNULL(a, b) returns a BIGINT type. If a type of a is INTEGER and b is TIMESTAMP, then IFNULL(a, b) returns a VARCHAR type.
IFNULL( expr1, expr2 )
NVL( expr1, expr2 )
result :
expr1 | expr2
IFNULL(a, b) or NVL(a, b) has the same meaning as the CASE statement below.
CASE WHEN a IS NULL THEN b
ELSE a
END
SELECT * FROM case_tbl;
a
=============
1
2
3
NULL
--returning a specific value when a is NULL
SELECT a, NVL(a, 10.0000) FROM case_tbl;
a nvl(a, 10.0000)
===================================
1 1.0000
2 2.0000
3 3.0000
NULL 10.0000
--IFNULL can be used instead of NVL and return values are converted to the string type
SELECT a, IFNULL(a, 'UNKNOWN') FROM case_tbl;
a ifnull(a, 'UNKNOWN')
===================================
1 '1'
2 '2'
3 '3'
NULL 'UNKNOWN'