Funciones NVL y COALESCE

Devuelve el valor de la primera expresión que no es nula en una serie de expresiones. Cuando se encuentra un valor que no es nulo, las expresiones restantes de la lista no se evalúan.

NVL es idéntica a COALESCE. Son sinónimos. En este tema se explica la sintaxis y se incluyen ejemplos de ambas funciones.

Sintaxis

NVL( expression, expression, ... )

La sintaxis de COALESCE es la misma:

COALESCE( expression, expression, ... )

Si todas las expresiones son nulas, el resultado es nulo.

Estas funciones son útiles cuando se desea devolver un valor secundario si falta un valor primario o es nulo. Por ejemplo, una consulta puede devolver el primero de los tres números de teléfono disponibles: móvil, fijo o trabajo. El orden de las expresiones de la función determina el orden de evaluación.

Argumentos

expresión

Una expresión, como un nombre de columna, que evalúa estados nulos.

Tipo de retorno

Amazon Redshift determina el tipo de datos del valor devuelto según las expresiones de entrada. Si los tipos de datos de las expresiones de entrada no tienen un tipo común, se devuelve un error.

Ejemplos

Si la lista contiene expresiones de enteros, la función devuelve un entero.

SELECT COALESCE(NULL, 12, NULL);

coalesce
--------------
12

Este ejemplo, que es igual al anterior, excepto que usa NVL, devuelve el mismo resultado.

SELECT NVL(NULL, 12, NULL);

coalesce
--------------
12

En el siguiente ejemplo, se devuelve un tipo de cadena.

SELECT COALESCE(NULL, 'Amazon Redshift', NULL);

coalesce
--------------
Amazon Redshift

En el siguiente ejemplo, se produce un error porque los tipos de datos varían en la lista de expresiones. En este caso, hay un tipo de cadena y un tipo de número en la lista.

SELECT COALESCE(NULL, 'Amazon Redshift', 12);
ERROR: invalid input syntax for integer: "Amazon Redshift"

Para este ejemplo, se crea una tabla con las columnas START_DATE y END_DATE, se insertan filas que incluyan valores nulos y, después, se aplica una expresión NVL a las dos columnas.

create table datetable (start_date date, end_date date);           
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values (null,'2008-12-31');
insert into datetable values ('2008-12-31',null);

select nvl(start_date, end_date)
from datetable
order by 1;
               
coalesce
------------
2008-06-01
2008-12-31
2008-12-31

El nombre predeterminado de la columna para una expresión NVL es COALESCE. La siguiente consulta devuelve los mismos resultados:

select coalesce(start_date, end_date)
from datetable
order by 1;

En las siguientes consultas de ejemplo, se crea una tabla con información de ejemplo sobre reservas de hotel y se insertan varias filas. Algunos registros contienen valores nulos.

create table booking_info (booking_id int, booking_code character(8), check_in date, check_out date, funds_collected numeric(12,2));

Inserte los siguientes datos de ejemplo. Algunos registros no tienen fecha check_out ni cantidad funds_collected.

insert into booking_info values (1, 'OCEAN_WV', '2023-02-01','2023-02-03',100.00);
insert into booking_info values (2, 'OCEAN_WV', '2023-04-22','2023-04-26',120.00);
insert into booking_info values (3, 'DSRT_SUN', '2023-03-13','2023-03-16',125.00);
insert into booking_info values (4, 'DSRT_SUN', '2023-06-01','2023-06-03',140.00);
insert into booking_info values (5, 'DSRT_SUN', '2023-07-10',null,null);
insert into booking_info values (6, 'OCEAN_WV', '2023-08-15',null,null);

La siguiente consulta devuelve una lista de fechas. Si la fecha de check_out no está disponible, se muestra la fecha de check_in.

select coalesce(check_out, check_in)
from booking_info
order by booking_id;

Los resultados son los siguientes. Tenga en cuenta que los dos últimos registros muestran la fecha de check_in.

coalesce
------------
2023-02-03
2023-04-26	
2023-03-16	
2023-06-03	
2023-07-10	
2023-08-15	

Si espera que una consulta devuelva valores nulos para determinadas funciones o columnas, puede usar una expresión NVL para reemplazar los valores nulos por algún otro valor. Por ejemplo, funciones de agregación, como SUM, devolverían valores nulos en lugar de ceros cuando no tengan filas para evaluar. Puede usar una expresión NVL para reemplazar esos valores nulos por 700.0. En lugar de 485, el resultado de sumar funds_collected es 1885 porque dos filas que tienen un valor nulo se sustituyen por 700.

select sum(nvl(funds_collected, 700.0)) as sumresult from booking_info;
               
sumresult
------
 1885