La función SQL Coalesce: Manejo de valores nulos

Puede que ya sepas cómo devolver valores nulos en SQL. Ahora, vamos a aprender a hacer lo contrario. Aunque la función COALESCE de SQL puede parecer compleja, en realidad es muy sencilla y útil. Veamos varios ejemplos de cómo la función COALESCE de SQL puede utilizarse para trabajar con valores NULL en SQL.

La necesidad de Coalesce en SQL

Antes de que nos sumerjamos en la función SQL COALESCE en detalle, debes entender cómo se comportan los valores NULL en las expresiones. En pocas palabras, un valor NULL indica que actualmente no hay ningún valor para una entrada particular en una columna de la tabla.

Cualquier expresión que incluya números, fechas, cadenas o booleanos y un valor NULL devolverá NULL. Esto se debe a que NULL representa un valor desconocido. Dado que cualquier expresión que incluya un valor desconocido no puede determinarse completamente, naturalmente debe devolver NULL.

Vea las siguientes expresiones de ejemplo:

	2  +  NULL 			returns		 NULL

	2  *  NULL 			returns		 NULL

	'hello'  ||  NULL 		returns		 NULL

	NULL  +  interval '1 day' 	returns		 NULL

	TRUE and NULL	                returns		 NULL

Trabajando con Multas de Tránsito

Para explicar cómo funciona la función SQL COALESCE, utilizaremos el ejemplo guiado de las multas de tránsito. La tabla siguiente almacena el ID del conductor, la multa que recibió, la fecha y la hora en que se impuso la multa, el nivel/grado de la infracción que cometió el conductor y la cantidad de multas anteriores no pagadas que ha acumulado el conductor.

Tenga en cuenta que la columna offense_level puede tener valores de "Banda A", "Banda B" o "Banda C". Un nivel de infracción de Banda B indica que el conductor tendrá que pagar 1,5 veces el importe de la multa si no paga la multa original en 30 días. La banda C duplica el importe de la multa después de 30 días. Un nivel de infracción de Banda A no afecta al importe de la multa si no se paga en 30 días.

El misterio de la desaparición de la multa

En primer lugar, consideraremos las multas dentro del periodo de pago de 30 días. En un ejemplo posterior estudiaremos las multas adicionales que pueden cobrarse al conductor una vez transcurrido el periodo de 30 días.

En teoría, la siguiente consulta debería devolver el valor de cada multa y el valor total de las multas que el conductor debe pagar (incluyendo cualquier multa anterior no pagada).

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

Sin embargo, si echamos un vistazo a la salida de la consulta en la siguiente tabla, observaremos que el primer registro no tiene ningún valor para la columna total_due. ¿Qué ha fallado?

La razón por la que esto ocurre es bastante sencilla. Dado que el conductor con ID 16877165 no tenía multas impagadas, el valor almacenado en la columna unpaid_fines para este conductor fue, por defecto, tratado como NULL.

Como hemos mencionado antes, cualquier expresión que incluya un valor NULL devolverá NULL. Así, la adición de fine_value a unpaid_fines produjo inadvertidamente NULL, lo que se tradujo en una celda vacía en la tabla de resultados.

SQL Coalesce al rescate

Para solucionar este problema, utilizaremos la función SQL COALESCE, que devuelve el primer valor no NULL de una lista de argumentos que le damos. La función puede tomar tantos argumentos como sea necesario. En este caso, sin embargo, pasaremos sólo dos argumentos a la llamada de la función: unpaid_fines y un valor de 0. Aquí está la consulta:

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

Ahora vamos a explicar cómo y por qué funciona esto.

Cuando se evalúa COALESCE(unpaid_fines, 0), si unpaid_fines resulta ser NULL para un registro concreto de la tabla, SQL omitirá ese valor y pasará al siguiente argumento que hayamos pasado a la función. Recordemos que en este contexto, un valor NULL indica que un controlador no tiene multas pendientes.

En este caso, el siguiente valor que proporcionamos después de unpaid_fines es 0. Como éste es el primer valor no NULL que encontramos al desplazarnos de izquierda a derecha para el primer conductor, la llamada a la función simplemente devolverá 0. Esto es precisamente lo que queremos: si un conductor no tiene ninguna multa impagada, entonces simplemente tenemos que tratar sus multas impagadas como cero.

Sin embargo, si un conductor tiene alguna multa impagada, entonces el valor almacenado en unpaid_fines para ese registro en particular no será NULL. En ese caso, la llamada a COALESCE(unpaid_fines, 0) devolverá el valor almacenado en unpaid_fines, lo que nos permitirá sumar un importe impagado no nulo a la multa actual del conductor para obtener el importe total adeudado.

Fracaso de los plazos de pago: Se incurre en un cargo extra

Ahora, recuerde que dijimos que si la multa no se paga dentro de los 30 días, entonces la cantidad que debe pagar se multiplicará por la tasa de infracción. En la próxima consulta utilizando la función SQL COALESCE, tendremos en cuenta la offense_rate en nuestros cálculos. Eche un vistazo:

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;

Esta vez, tenemos un valor vacío en la cuarta fila bajo la columna total_due_after_30_days. Con suerte, podrás determinar lo que tenemos que añadir a nuestra consulta para que funcione correctamente.

Al igual que antes, necesitamos hacer una llamada a COALESCE. Esta vez, necesitamos usar SQL COALESCE al calcular el multiplicador fino. Esto tendrá en cuenta la posibilidad de que no se haya especificado la infracción del conductor, en cuyo caso la multa deberá tratarse como si la infracción hubiera sido clasificada como Banda A (sin sanción).

Naturalmente, pasaremos un valor de 1 como segundo argumento. De este modo, si el valor de offense_level resulta ser NULL para un conductor concreto, el valor de la multa seguirá siendo el mismo (se multiplicará por uno).

Esta es la consulta actualizada:

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 nuestro último ejemplo, haremos una concatenación de cadenas para observar qué sucede cuando una de las columnas con las que trabajamos contiene un valor NULL. Esta es la consulta:

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

Si echamos un vistazo al cuarto registro, observaremos de nuevo un valor vacío, esta vez en la columna reason_and_severity. Una vez más, esto se debe a que hemos intentado evaluar una expresión que incluye un valor NULL.

Si en lugar de ello utilizamos la función SQL COALESCE, podemos especificar un valor al que recurrir si nos encontramos con NULL. Para nuestros propósitos, pasaremos la cadena 'No Band' como valor por defecto. Esta es la consulta actualizada, que se ejecuta como se esperaba:

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

Reflexiones finales sobre la función COALESCE de SQL

No todos los gestores de bases de datos tienen acceso a la función SQL COALESCE, pero todas las bases de datos ofrecen funciones similares para trabajar con valores NULL. Funciones como IFNULL(), NVL(), y ISNULL(), entre otras, le permiten detectar, reemplazar o transformar valores NULL.

Si quieres aprender más sobre las funciones de SQL, sigue nuestro curso LearnSQL.es Funciones estándar de SQL , donde cubrimos muchas operaciones y funciones esenciales de SQL.