Funções NVL e COALESCE

Retorna o valor da primeira expressão não nula em uma série de expressões. Quando um valor não nulo é encontrado, as demais expressões na lista não são avaliadas.

NVL é idêntica a COALESCE. São funções sinônimas. Este tópico explica a sintaxe e apresenta exemplos de ambas.

Sintaxe

NVL( expression, expression, ... )

A sintaxe de COALESCE é a mesma:

COALESCE( expression, expression, ... )

Se todas as expressões forem nulas, o resultado será nulo.

Essas funções são úteis para retornar um valor secundário quando um valor primário está ausente ou é nulo. Por exemplo, uma consulta pode retornar o primeiro dos três números de telefone disponíveis: celular, residencial ou profissional. A ordem das expressões na função determina a ordem de avaliação.

Argumentos

expressão

Uma expressão, tal como um nome de coluna, a ser avaliada quanto ao status nulo.

Tipo de retorno

O Amazon Redshift determina o tipo de dado do valor retornado com base nas expressões de entrada. Se os tipos de dados das expressões de entrada não tiverem um tipo comum, um erro será retornado.

Exemplos

Se a lista contiver expressões do tipo inteiro, a função retornará um inteiro.

SELECT COALESCE(NULL, 12, NULL);

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

Esse exemplo, que é igual ao exemplo anterior, exceto pelo fato de usar NVL, retorna o mesmo resultado.

SELECT NVL(NULL, 12, NULL);

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

O exemplo a seguir retorna um tipo string.

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

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

O exemplo a seguir resulta em um erro porque os tipos de dados variam na lista de expressões. Nesse caso, há uma string e um número na lista.

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

Para esse exemplo, crie uma tabela com as colunas START_DATE e END_DATE, insira linhas que incluam valores nulos e aplique uma expressão NVL nas duas colunas.

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

O nome de coluna padrão para uma expressão NVL é COALESCE. A consulta a seguir retorna os mesmos resultados:

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

Para os exemplos de consulta a seguir, crie uma tabela com exemplos de informações de reservas em hotéis e insira várias linhas. Alguns registros contêm valores nulos.

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

Insira os seguintes dados de exemplo. Alguns registros não têm uma data de check_out nem um valor de 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);

A consulta a seguir retorna uma lista de datas. Se a data de check_out não estiver disponível, ela listará a data de check_in.

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

Os resultados são mostrados a seguir. Observe que os dois últimos registros mostram a data de check_in.

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

Se você espera que uma consulta retorne valores nulos para determinadas funções ou colunas, você pode usar uma expressão NVL para substituir os nulos por outro valor qualquer. Por exemplo, as funções agregadas, tal como SUM, retornam valores nulos em vez de zeros quando não há linhas para avaliar. É possível usar uma expressão NVL para substituir esses valores nulos por 700.0. Em vez de 485, o resultado da soma de funds_collected é 1885 porque duas linhas que têm nulo são substituídas por 700.

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