NVL

Syntax

NVL(expr1, expr2)

Overview

The NVL function returns the value of the first of the two arguments if it is not NULL, and the value of the second argument if it is NULL.

Called only for arguments of the same type, but performs an implicit cast if they are of different types. The return type follows the type of the first argument.

Parameters

Parameter	Description
expr1	The first value to evaluate. If this value is not NULL, it is returned as is.
expr2	The value to be returned if the first argument is NULL; if the two arguments are of different types, an appropriate cast may be attempted (e.g., from text to number or date).

Example

# test data
create table employees (first_name varchar, last_name varchar, salary integer, hire_date timestamptz);

INSERT INTO employees (first_name, last_name, salary, hire_date) VALUES
('John', 'Doe', NULL, '2020-03-15 09:00:00'),
(NULL, 'Smith', 62000, '2019-07-22 10:30:00'),
('Michael', 'Johnson', 72000, '2018-11-10 08:45:00'),
('Emily', 'Davis', 48000, '2021-05-01 12:00:00'),
('David', 'Wilson', 53000, '2017-09-17 14:20:00'),
(NULL, 'Brown', NULL, '2016-12-05 09:15:00'),
('James', 'Taylor', NULL, '2015-06-30 16:45:00'),
('Jessica', 'Martinez', 68000, '2022-01-25 11:10:00'),
(NULL, 'Anderson', 58000, '2020-10-05 13:35:00'),
('Laura', 'Thomas', 49500, '2023-08-12 08:00:00');


# test 1
# return 0 if salary value is NULL; return last_name if first_name value is NULL;
select oracle.NVL(salary,0), oracle.NVL(first_name, last_name) from employees ;

  nvl  |   nvl   
-------+---------
 55000 | John
 62000 | Jane
 72000 | Michael
 48000 | Emily
 53000 | David
 60000 | Sarah
 75000 | James
 68000 | Jessica
 58000 | Daniel
 49500 | Laura
(10 rows)