A função Coalesce SQL: Manuseio de Valores Nulos

Você já deve saber como retornar valores nulos em SQL. Agora, vamos aprender a fazer o oposto. Embora a função SQL COALESCE possa parecer complexa, ela é na verdade muito simples e útil. Vejamos vários exemplos de como a função SQL COALESCE pode ser usada para trabalhar com valores NULL em SQL.

A necessidade de Coalescer em SQL

Antes de mergulharmos na função SQL COALESCE em detalhes, você deve entender como os valores NULL se comportam nas expressões. Simplificando, um valor NULL indica que não há atualmente nenhum valor para uma determinada entrada em uma coluna de tabela.

Qualquer expressão envolvendo números, datas, strings ou booleans e um valor de NULL retornará NULL por si só. Isto porque o NULL representa um valor desconhecido. Como qualquer expressão que envolva um valor desconhecido não pode ser totalmente determinada, ela deve naturalmente retornar NULL!

Dê uma olhada nos exemplos de expressões a seguir:

	2  +  NULL 			returns		 NULL

	2  *  NULL 			returns		 NULL

	'hello'  ||  NULL 		returns		 NULL

	NULL  +  interval '1 day' 	returns		 NULL

	TRUE and NULL	                returns		 NULL

Trabalhando com Multas de Trânsito

Para explicar como funciona a função SQL COALESCE, usaremos o exemplo guiado de multas de trânsito. A tabela abaixo armazena a identificação do motorista, a multa recebida, a data e hora em que a multa foi imposta, o nível/grau da infração cometida pelo motorista e o valor das multas anteriores não pagas que o motorista acumulou.

Observe que a coluna offense_level pode ter valores de 'Banda A', 'Banda B', ou 'Banda C'. Um nível de ofensa da Banda B indica que o motorista terá que pagar 1,5 vezes o valor da multa se não pagar a multa original dentro de 30 dias. A Banda C dobra o valor da multa após 30 dias. Um nível de infração da Banda A não afeta o valor da multa se ela não for paga dentro de 30 dias.

O Mistério do Desaparecimento da Multa

Primeiro, consideraremos multas dentro do período de pagamento de 30 dias. Analisaremos as taxas extras que podem ser cobradas ao motorista após o período de 30 dias passar em um exemplo posterior.

Em teoria, a seguinte consulta deve retornar o valor de cada multa e o valor total de quaisquer multas que o motorista deve pagar (incluindo quaisquer multas anteriores não pagas).

SELECT 	driver_id,
		fine_value 			as "fine_amount", 
		fine_value + unpaid_fines 	as "total_due" 
FROM 	fines;

Entretanto, se olharmos a saída da consulta na tabela seguinte, notaremos que o primeiro registro não tem nenhum valor para a coluna total_due! O que deu errado?

A razão pela qual isto acontece é bastante simples. Como o motorista com ID 16877165 não tinha multas não pagas, o valor armazenado sob a coluna unpaid_fines para este motorista foi, por padrão, tratado como NULL.

Como mencionamos anteriormente, qualquer expressão envolvendo um valor de NULL retornará NULL. Assim, a adição de fine_value às multas não pagas_fine inadvertidamente produziu NULL, que se traduziu em uma célula vazia na tabela de resultados.

SQL Coalesce para o Rescue

Para resolver este problema, usaremos a função SQL COALESCE, que retorna o primeiro valor não-NULL a partir de uma lista de argumentos que lhe damos. A função pode aceitar tantos argumentos quantos forem necessários. Neste caso, porém, vamos passar em apenas dois argumentos para a chamada da função: unpaid_fines e um valor de 0. Aqui está a consulta:

SELECT 	driver_id,
		fine_value 						as "fine_amount", 
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM 	fines;

Vamos agora explicar como e por que isto funciona.

Quando COALESCE(unpaid_fines, 0) for avaliado, se unpaid_fines for NULL para um determinado registro na tabela, SQL irá pular esse valor e passar para o próximo argumento que passamos para a função. Lembre-se que, neste contexto, um valor de NULL indica que um motorista não tem multas não pagas.

Neste caso, o próximo valor que fornecemos após unpaid_fines é 0. Como este é o primeiro valor não-NULL que encontramos ao passar da esquerda para a direita para o primeiro motorista, a chamada de função simplesmente retornará 0. Isto é exatamente o que queremos - se um motorista não tiver nenhuma multa não paga, então simplesmente precisamos tratar suas multas não pagas como zero!

No entanto, se um motorista teve algumas multas não pagas, então o valor armazenado sob unpaid_fines para aquele registro em particular não será NULL. Nesse caso, a chamada para COALESCE(unpaid_fines, 0) retornará o valor armazenado sob unpaid_fines, permitindo-nos adicionar um valor não zero não pago à multa atual do motorista para obter o valor total devido.

Prazos de pagamento perdidos: Taxa extra incorrida

Agora, lembre-se que dissemos que se a multa não for paga dentro de 30 dias, então o valor a ser pago será multiplicado pela taxa de infração. Na próxima consulta usando a função SQL COALESCE, levaremos em conta o offense_rate em nossos cálculos. Dê uma olhada:

SELECT driver_id,
	     fine_value as "fine_amount", 
				COALESCE(unpaid_fines,0) as "unpaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * ofense_rate + COALESCE(unpaid_fines,0)	as "total due_after_30_days" 
FROM  fines;

Desta vez, obtivemos um valor vazio na quarta linha sob a coluna total_due_after_30_days. Esperamos que você seja capaz de determinar o que precisamos adicionar à nossa consulta para que ela funcione corretamente.

Assim como antes, precisamos fazer uma chamada para COALESCE. Desta vez, precisamos usar o SQL COALESCE ao calcular o multiplicador fino. Isto levará em consideração a possibilidade de que o delito do condutor não tenha sido especificado, caso em que a multa deve ser tratada como se o delito tivesse sido classificado como Banda A (sem penalidade).

Naturalmente, passaremos em um valor de 1 como o segundo argumento. Assim, se o valor sob offense_level for NULL para um determinado motorista, o valor da multa simplesmente permanecerá o mesmo (será multiplicado por um).

Aqui está a consulta atualizada:

SELECT driver_id,
	     fine_value 	as "fine_amount", 
                COALESCE(unpaid_fines,0)	as "upaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * COALESCE(offense_level, 1) + COALESCE(unpaid_fines,0)	as "total_due_after_30_days" 
FROM  fines;

Para nosso último exemplo, faremos alguma concatenação de cordas para observar o que acontece quando uma das colunas com as quais estamos trabalhando contém um valor NULL. Aqui está a consulta:

SELECT	driver_id,
		reason ||' - '|| offense_level  			as “reason_and_severity”,
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM		fines

Se dermos uma olhada no quarto registro, notaremos novamente um valor vazio, desta vez sob a coluna reason_and_severity. Mais uma vez, isto é porque tentamos avaliar uma expressão envolvendo um valor NULL.

Se em vez disso usarmos a função SQL COALESCE, podemos especificar um valor a ser utilizado se encontrarmos um valor NULL. Para nossos propósitos, passaremos na string 'No Band' como esse padrão. Aqui está a consulta atualizada, que funciona como esperado:

SELECT	driver_id,
		reason ||' - '|| COALESCE(offense_level, 'No Band')  	as "reason_and_severity",
		fine_value + COALESCE(unpaid_fines,0)		as "total_due" 
FROM		fines

Reflexões finais sobre a função SQL COALESCE

Nem todos os gerentes de banco de dados têm acesso à função SQL COALESCE, mas todos os bancos de dados oferecem funções similares para trabalhar com valores NULL. Funções como IFNULL(), NVL(), e ISNULL(), entre outras, permitem detectar, substituir ou transformar os valores NULL.

Se você gostaria de aprender mais sobre as funções SQL, vá em frente e confira nosso curso LearnSQL.com.br Funções Comuns em SQL , onde cobrimos muitas operações e funções SQL essenciais.