Oracle Handling NULL values
Operations containing NULL are NULL, except concatenation
SELECT 3 * NULL + 5, 'Hello ' || NULL || 'world' FROM DUAL;3*NULL+5 'HELLO'||NULL||'WORLD'
(null) Hello world
NVL2 to get a dierent result if a value is null or not
If the first parameter is NOT NULL, NVL2 will return the second parameter. Otherwise it will return the third one.
SELECT NVL2(NULL, 'Foo', 'Bar'), NVL2(5, 'Foo', 'Bar') FROM DUAL;NVL2(NULL,'FOO','BAR') NVL2(5,'FOO','BAR')
Bar Foo
COALESCE to return the first non-NULL value
SELECT COALESCE(a, b, c, d, 5) FROM
(SELECT NULL A, NULL b, NULL c, 4 d FROM DUAL);COALESCE(A,B,C,D,5)
4
In some case, using COALESCE with two parameters can be faster than using NVL when the second parameter is not a constant. NVL will always evaluate both parameters. COALESCE will stop at the first non-NULL value it encounters. It means that if the first value is non-NULL, COALESCE will be faster.