Example
SELECT a column_with_null, NVL(a, 'N/A') column_without_null FROM
(SELECT NULL a FROM DUAL);
| COLUMN_WITH_NULL | COLUMN_WITHOUT_NULL |
|---|
| (null) | N/A |
NVL is useful to compare two values which can contain NULLs :
SELECT
CASE WHEN a = b THEN 1 WHEN a <> b THEN 0 else -1 END comparison_without_nvl,
CASE WHEN NVL(a, -1) = NVL(b, -1) THEN 1 WHEN NVL(a, -1) <> NVL(b, -1) THEN 0 else -1 END comparison_with_nvl
FROM
(select null a, 3 b FROM DUAL
UNION ALL
SELECT NULL, NULL FROM DUAL);
| COMPARISON_WITHOUT_NVL | COMPARISON_WITH_NVL |
|---|
| -1 | 0 |
| -1 | 1 |
