Cómo abordar los NULLs de SQL: Función COALESCE

Es inevitable que algunos datos de la base de datos no tengan valor, lo que en SQL se representa con la palabra clave NULL. " Ningún valor" aquí es diferente de cero, falso, o una cadena vacía (¡pero con excepciones! En Oracle NULL es lo mismo que una cadena de longitud cero). Al jugar con la base de datos, el tratamiento de NULLes cada vez más problemático, por lo que MySQL proporciona ayuda con algunas de las funciones, como COALESCE.

Introduzcamos la función COALESCE() función:

Definición

COALESCE devuelve el primer argumento no NULL de la lista de argumentos pasada. Su sintaxis es la siguiente:

COALESCE(x, y, … , n)

Lo que adicionalmente es importante, que:

• Toma al menos dos argumentos.
• Las expresiones de la función COALESCE deben evaluarse al mismo tipo de datos (Por ejemplo la sentencia SQL SELECT COALESCE (1, 'aa', NULL, 23); producirá un error).
• Si todos los argumentos listados se evalúan a NULL, la función también devuelve NULL.

COALESCE es esencialmente una declaración abreviada de CASE que se presenta a continuación:

CASE
WHEN x IS NOT NULL THEN x
WHEN y IS NOT NULL THEN y
WHEN ... IS NOT NULL THEN …
ELSE n
END

COALESCE - ejemplos de uso

Ahora, eche un vistazo al prototipo presentado a continuación del modelo de base de datos universitaria. Mostraré algunos ejemplos que requieren la función COALESCE que hace referencia a las tablas de este modelo.

Uso de COALESCE con concatenación de valores

Echa un vistazo a la tabla student del modelo de base de datos universitario presentado:

Puede ocurrir que una persona en particular no tenga el segundo nombre, por lo que algunos registros de la columna middle_name pueden ser NULLs como muestran los datos del ejemplo de abajo.

Intentemos concatenar el nombre, el segundo nombre y el apellido del estudiante. Esto funciona en PostgreSQL, Oracle.

SELECT
  first_name || ' ' || middle_name || ' ' || last_name AS full_name
FROM student;

La tabla de resultados contiene una columna full_name con las cadenas concatenadas.

Observe que sólo un registro tiene un nombre completo correcto. El resto son NULLs, porque el segundo nombre era también NULL (al concatenar cadenas con valor NULL, el resultado es también NULL).

Este resultado no se ve muy bien. El resultado no muestra el nombre completo de los estudiantes sin segundo nombre. Aquí la función COALESCE() viene al rescate. Con ella podemos sustituir los campos de NULL por, por ejemplo, una cadena vacía. Ahora, la nueva consulta tiene el siguiente aspecto:

SELECT
  first_name || COALESCE(' ' || middle_name || ' ', ' ') || last_name AS full_name
FROM student;

El resultado ya está completo 🙂 .

MySQL, MS SQL Server utiliza para la concatenación la función CONCAT() o el operador '+'.

Del mismo modo, la consulta en MySQL o MS SQL Server tendrá el siguiente aspecto:

SELECT
  CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM student;

... y los registros que tengan NULLs también tendrán :

Con la función COALESCE, los NULLs que aparezcan en la columna middle_name serán reemplazados.

SELECT
  CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)
FROM student;

Uso de PostgreSQL COALESCE con funciones de agregación

Bien, NULLs en efecto puede parecer muy problemático. Imaginemos una situación diferente. Quiero saber cuál es la nota media del curso. Parece muy fácil, ¿verdad? Pero, espera, espera... aunque se trate de una consulta sencilla podemos encontrarnos con algunos inconvenientes. El problema tiene que ver con algunas de las funciones agregadas de SQL como SUM(), MAX() o MIN().

EJEMPLO: Uso de COALESCE con la función AVG

Probemos un ejemplo con la tabla student_courses.

Como se ve a continuación, los estudiantes que están inscritos en el curso de id 1 no tienen todavía una calificación.

La consulta que devuelve la nota media del curso tiene el siguiente aspecto:

SELECT
  course_instance_id,
  AVG(grade) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Para la función AVG(), se suman los campos que no son deNULL y la suma se divide por el número de campos que no son deNULL. Así, si todos los registros tienen NULL en la columna de calificación, la nota media será también NULL.

La tabla de resultados es la siguiente:

En este caso, queremos insertar otro valor, como el 0 (la nota puede ser un valor de 2 a 5, por lo que el promedio 0 puede indicarnos que los estudiantes no tienen notas)

COALESCE() la función viene con la ayuda de la sustitución de un valor. La misma consulta con COALESCE será:

SELECT
  course_instance_id,
  COALESCE(AVG(grade), 0) AS average_grade_in_course
FROM student_courses
GROUP BY course_instance_id;

Y ahora podemos obtener la tabla de resultados como:

EJEMPLO: Usando COALESCE con la función SUM()

Este es un ejemplo similar. Supongamos una situación diferente. Queremos contar la cantidad total de las horas de ausencia de cada estudiante. Echa un vistazo a la parte seleccionada del modelo:

Los datos de la tabla student_course_attendance tienen el siguiente aspecto.

Mientras que los datos student_courses son:

Observe detenidamente los datos de esas tablas. Como ves, dos estudiantes están inscritos en el curso concreto, mientras que sólo uno faltaba a las clases.

La consulta, que calculará la suma del número total de las horas de ausencia para un course_id = 1 será para cada estudiante es:

SELECT
  student_id,
  SUM(absence_hours) AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

... y se evalúa en una tabla de resultados:

El total de horas de ausencia se evaluó en NULL, porque efectivamente, este estudiante no faltó a ninguna clase en esta instancia del curso. No había ningún registro para este estudiante en la tabla student_course_attendance, y la función SUM() devolvía NULL. Podemos asegurarnos de esta situación por supuesto usando COALESCE la función

La nueva consulta es la siguiente:

SELECT
  student_id,
  COALESCE(SUM(absence_hours), 0)  AS total_absence_hours
FROM student_courses sc
LEFT JOIN student_course_attendance sca
  ON sc.id = sca.student_courses_id
WHERE course_instance_id = 1
GROUP BY 1;

Y el resultado es ahora:

Abandonemos ahora el modelo universitario y veamos el ejemplo diferente.

Uso de COALESCE en la creación de tablas PIVOT

Este caso de uso se presentará para la base de datos PostgreSQL. Tenga en cuenta que otras bases de datos como Oracle y MS SQL Server tienen sus funciones equivalentes para crear tablas pivotantes.

Observe la siguiente tabla. Es el resultado de una consulta que muestra el total de ventas de cada marca en un mes determinado.

Quiero transponer las filas con columnas (crear una tabla dinámica). La tabla de resultados debe tener las columnas: brand_id, jan, feb, ..., dec. Observe que no en todos los meses se vendieron productos de la marca en particular. Veamos el ejemplo de Postgres.

SELECT brand_id, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

FROM CROSSTAB (
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Después de ejecutar dicha consulta, recibimos:

Desafortunadamente, la venta se registra sólo en algunos de los meses y sólo en esos campos se pega el número de ventas totales. Como probablemente esperaba, el resto de los campos contiene NULLs.

Para deshacernos de los indeseados NULLs podemos utilizar la función COALESCE, que cuando sea necesario inserta un 0 en lugar de NULL. La consulta reparada tiene el siguiente aspecto:

SELECT 
  brand_id,
  COALESCE(jan, 0),
  COALESCE(feb, 0),
  COALESCE(mar, 0),
  COALESCE(apr, 0),
  COALESCE(may, 0),
  COALESCE(jun, 0),
  COALESCE(jul, 0),
  COALESCE(aug, 0),
  COALESCE(sep, 0),
  COALESCE(oct, 0),
  COALESCE(nov, 0),
  COALESCE(dec, 0)
FROM CROSSTAB(
  'SELECT year, month, qty FROM sales ORDER BY 1',
  'SELECT m FROM generate_series(1,12) m'
) AS (
   year int,
   "jan" int,
   "feb" int,
   "mar" int,
   "apr" int,
   "may" int,
   "jun" int,
   "jul" int,
   "aug" int,
   "sep" int,
   "oct" int,
   "nov" int,
   "dec" int
);

Resumen

NULLs pueden hacer que la vida sea problemática. Si aún no has experimentado el lado malo de los valores perdidos, seguro que te lo encontrarás. Es cuestión de tiempo. Por ahora, recuerde la funciónCOALESCE , que le ayudará a hacer frente a los indeseados NULLs.