NULLs y Manejo de Datos Perdidos en SQL

Manejar datos que faltan (es decir, NULLs) en SQL puede ser un reto. Los NULLs pueden plantear muchas trampas, especialmente si no entiendes cómo funcionan. En este artículo, hablaremos sobre el manejo de NULL en SQL. También explicaremos cómo evitar errores comunes al trabajar con NULLs.

La falta de datos en una base de datos es una realidad inevitable. Hay muchas razones por las que pueden faltar datos en la base de datos: los datos completos aún no están disponibles, los usuarios proporcionan información incompleta, cambios en el esquema de la base de datos, fallos en la base de datos, errores humanos, etc. Trabajar con datos que faltan en las consultas SQL es todo un reto. En este artículo mostraré los errores más comunes asociados a los datos incompletos en SQL.

Si desea practicar el trabajo con datos incompletos o desconocidos en SQL, le recomiendo nuestro temaEjercicio de SQL . En el momento de escribir esto, contiene 10 cursos para que practiques SQL, ¡y seguimos añadiendo más! Los cursos están divididos en secciones y muchos de ellos tienen una sección dedicada a NULL. Recomiendo específicamente estos cursos para practicar el manejo de NULL en SQL:

• Básico Ejercicio de SQL: A Store
• Ejercicio de SQL: Universitario
• Básico Ejercicio de SQL: ¡Rastrea Consultas!
• Básico Ejercicio de SQL: Blog y Datos de Tráfico

¿Qué es NULL en SQL?

En SQL, NULL representa un valor ausente o indefinido en una base de datos. Se utiliza para denotar el hecho de que el valor en un campo está ausente o es desconocido.

Un problema común de principiante con NULLs es que su base de datos a menudo no mostrará NULLs explícitamente. En la tabla siguiente, el campo like para el ID de puesto 1, el campo location para el ID de puesto 2 y el campo views para el ID de puesto 3 son todos NULL. Sin embargo, una base de datos no muestra NULL explícitamente; en su lugar, muestra un campo vacío. Hay que tener en cuenta que NULL es una posibilidad de adivinar que estos campos son NULL.

id	title	location	views	likes
1	Quick Morning Routines!	London	94,365
2	Eco-Friendly Living Tips		123,891	6,587
3	Healthy Snacks on the Go	Paris		9,457

Sin embargo, es importante tener en cuenta que NULL es diferente de una cadena vacía o de un cero. NULL es la ausencia de valor; significa que el valor es desconocido. Como pronto veremos, NULL en sí no es un valor real. Mucha gente dice o escribe algo como "Hay NULL valores en este campo", pero eso es técnicamente incorrecto.

Al diseñar una tabla en una base de datos, un diseñador de bases de datos puede decidir que NULL no está permitido para un campo concreto. Esto se hace estableciendo una restricción NOT NULL para una columna cuando se crea la tabla. Las claves primarias (columnas que identifican filas en una tabla) también son NOT NULL por defecto.

Se considera una buena práctica evitar NULLs en el diseño de la base de datos. Por lo tanto, el mayor número posible de columnas debería definirse como NOT NULL. Lo mejor es permitir NULLs sólo cuando sea estrictamente necesario. Sin embargo, los datos pueden estar desordenados y a veces NULL es inevitable.

El conjunto de datos

En este artículo utilizaremos los datos de la tabla posts. Imagina que estás extrayendo datos de tu plataforma de redes sociales favorita para analizarlos. Los datos de esa plataforma se almacenan en esta tabla. Aquí están los campos:

• id - ID de la publicación.
• title - El título de la publicación.
• url - La URL (dirección web) de la entrada.
• creator - El nombre del creador de la entrada.
• published - La fecha de publicación de la entrada.
• type - El tipo de entrada.
• location Dónde se publicó la entrada; puede ser NULL si la ubicación es desconocida o irrelevante.
• views - Cuántas visitas tiene cada entrada; puede ser NULL si el creador decide no hacer públicos estos datos.
• likes - El número de "me gusta" que ha recibido la publicación; puede ser NULL si el creador decide no hacer públicos estos datos.
• dislikes - El número de "no me gusta" de la publicación; puede ser NULL porque la plataforma ya no muestra estos datos. Sin embargo, es posible que tengamos los datos de los "no me gusta" de las entradas más antiguas.

Ahora que hemos revisado los datos, usémoslos para entender NULL.

Operadores de comparación con NULL

Incluso algo tan aparentemente simple como el comportamiento de NULL cuando se utiliza con operadores de comparación puede ser contraintuitivo y sorprendente para los principiantes.

Supongamos que queremos averiguar cuántas filas de la tabla posts faltan los campos views. Por ejemplo:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

Resultado: 0 filas

Genial, cero filas tienen vistas que faltan. Asombroso. ¿Cuántas de ellas no faltan?

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

Resultado: 0 filas

¿También cero? Algo debe estar mal.

La cuestión aquí es que debería utilizar los operadores IS NULL and IS NOT NULL para comprobar NULLs:

SELECT COUNT(*)
FROM posts
WHERE views IS NULL;

Resultado: 34 filas

SELECT COUNT(*)
FROM posts
WHERE views IS NOT NULL;

Resultado: 66 filas

¿Por qué estos resultados son tan diferentes de las dos consultas anteriores?

Lógica de Tres Valores en SQL

Los problemas de comparación para NULL provienen del hecho de que NULL no es un valor real. Este es el punto más importante que tienes que entender para trabajar con NULL eficientemente.

SQL utiliza lógica de tres valores. Cada condición lógica en SQL puede tener uno de tres valores: TRUE, FALSE o NULL. NULL aquí significa "no lo sé". Siempre que se utiliza una condición en WHERE, SQL devuelve las filas para las que la condición lógica en WHERE es TRUE. No devuelve las filas para las que la condición es FALSE (como cabría esperar) y para las que la condición es NULL (que no siempre es lo que cabría esperar).

Veamos un ejemplo del funcionamiento de WHERE:

SELECT COUNT(*)
FROM posts
WHERE views < 100;

Esta consulta cuenta las entradas en las que la columna views tiene un valor y ese valor es inferior a 100. Las entradas con vistas desconocidas no se cuentan. Los mensajes con vistas desconocidas no se cuentan. Esto puede ser contraintuitivo: cuando visualizas los datos de los posts, ves el vacío en muchos campos de vistas. Intuitivamente piensas que seguramente este vacío es menor que 100. Pero el vacío significa que el número de visitas es menor que 100. Pero el vacío significa que la base de datos no tiene los datos; como no tiene los datos, no puede decir si es menos de 100 o no. Así que las filas que tienen un campo views vacío no se cuentan.

SELECT COUNT(*)
FROM posts
WHERE views < likes;

Esta consulta devuelve las filas en las que se conocen tanto el número de visitas como el número de "me gusta" y el valor de views es menor que el de likes. No devuelve filas donde el valor views es desconocido o el valor likes es desconocido.

Si desea incluir en el resultado las entradas con campos views vacíos, deberá filtrar explícitamente por NULL:

SELECT COUNT(*)
FROM posts
WHERE views < likes OR views IS NULL;

Volvamos de nuevo a las consultas con las que empezamos:

SELECT COUNT(*)
FROM posts
WHERE views = NULL;

SELECT COUNT(*)
FROM posts
WHERE views <> NULL;

La condición WHERE compara la columna views con NULL. Sin embargo, NULL significa "desconozco el valor". La base de datos no puede decir si views es igual (o no) a un valor desconocido. Puede serlo o no, por lo que la base de datos dice NULL - es decir, "No lo sé" - y esas filas no se devuelven en el resultado.

Recuerde:

• Pruebe NULL con IS NULL y IS NOT NULL
• Los operadores de comparación (como <, <=, >, >=, =, <>, y LIKE) devuelven NULL si uno de los argumentos es NULL. Si desea incluir NULL, compruébelo explícitamente con IS NULL o IS NOT NULL.

Uso de NULL en funciones SQL

NULL es igualmente problemático en operadores y funciones. La mayoría de las funciones y operadores devuelven NULL cuando se les da NULL como argumento.

Ejemplo 1: Imagina que queremos devolver la cabecera de cada entrada. (La cabecera consiste en el título, un guión y la ubicación). Esta es la consulta

SELECT 
  title || ‘ - ‘ || location
FROM posts;

Resultado:

Quick Morning Routines! - London
Healthy Snacks on the Go - Paris

La consulta devuelve NULL si falta title o location. El post con ID 2 tiene NULL como resultado de nuestra consulta, ya que su location es desconocido.

Ejemplo 2: Lo mismo ocurre con los operadores aritméticos. Digamos que quieres calcular la participación de un post como la suma de likes y dislikes:

SELECT 
  title, 
  likes, 
  dislikes, 
  likes + dislikes AS engagement
FROM posts;

Resultado:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10
Healthy Snacks on the Go		34

Si alguno de los campos likes o dislikes es NULL, entonces el valor devuelto en la columna engagement es también NULL.

Ejemplo 3: El mismo comportamiento presentan las funciones regulares, como UPPER():

SELECT 
  title, 
  UPPER(creator)
FROM posts;

title	UPPER(creator)
Quick Morning Routines!	JENNY
Eco-Friendly Living Tips
Healthy Snacks on the Go	RACHEL82

El creador del post "Eco-Friendly Living Tips" es desconocido, por lo que la expresión UPPER(creator) devuelve NULL.

Funciones que trabajan con NULLs

Afortunadamente, existen funciones en SQL que ayudan a mitigar estos problemas con NULL.

COALESCE

COALESCE() toma muchos argumentos y devuelve el primer valor noNULL de sus argumentos. Suele utilizarse para sustituir NULL por un valor significativo en otra función o expresión. Podríamos modificar nuestra consulta de compromiso de la siguiente manera:

SELECT 
  title, 
  likes, 
  dislikes, 
  COALESCE(likes, 0) + COALESCE(dislikes, 0) AS engagement
FROM posts;

Siempre que el valor de likes o dislikes sea NULL, la función COALESCE() lo sustituye por 0. El nuevo valor se utiliza en el cálculo y evitamos los resultados de NULL:

title	likes	dislikes	engagement
Quick Morning Routines!	25	15	30
Eco-Friendly Living Tips	10		10
Healthy Snacks on the Go		34	34

También puede utilizar COALESCE() para dar una etiqueta significativa a NULLs en los resultados. La siguiente consulta sustituye NULL por "Desconocido" en el conjunto de resultados; el campo en sí sigue siendo NULL en la base de datos:

SELECT 
  title, 
  COALESCE(location, ‘Unknown’) AS location
FROM posts;

Este es el resultado:

title	location
Quick Morning Routines!	London
Eco-Friendly Living Tips	Unknown
Healthy Snacks on the Go	Paris

Recuerde: Utilice la función COALESCE():

• Para proporcionar una etiqueta significativa para NULL en los informes.
• Para dar un valor a NULL en los cálculos.

NULLIF

Otra función que trabaja con NULL es NULLIF. Ésta es un poco rara: toma dos argumentos y devuelve NULL si los argumentos son iguales. En la práctica, se utiliza NULLIF para evitar la división por cero:

SELECT 
  title, 
  likes / NULLIF(views, 0)
FROM posts;

Se desea calcular la relación entre likes y views para los puestos. Sin embargo, si el valor de views es 0, podría producirse un error de división por cero. Para evitarlo, utilice la función NULLIF. Si views es igual a cero, entonces NULLIF(views, 0) devuelve NULL.

NULL en la división da como resultado NULL y evita el error de división por cero. Aquí nos aprovechamos de NULL en cascada sobre los resultados de los cómputos.

NULL en funciones GROUP BY y agregadas

Cuando se trabaja con valores perdidos, es bueno saber cómo se comporta NULL en GROUP BY y en funciones agregadas.

NULL y GROUP BY

GROUP BY colocan las filas en grupos basados en valores comunes en una columna dada. A continuación, puede aplicar funciones de agregación a cada grupo y calcular resúmenes para cada grupo. Esta consulta cuenta el número de entradas de cada ubicación:

SELECT 
  location, 
  COUNT(*)
FROM posts
GROUP BY location;

Con GROUP BY, todas las filas con NULL en la columna se colocan en un grupo; se calculan estadísticas para este grupo como para cualquier otro.

En nuestro ejemplo, todos los mensajes con una ubicación desconocida se incluyen en un grupo:

location	COUNT
London	45
Paris	23
	12
…	…

NULL y funciones agregadas

En general, las funciones agregadas también ignoran NULLs. Pero hay algunas variantes importantes en la forma en que algunas funciones agregadas tratan NULLs.

Las funciones SUM(), MIN(), MAX() ignoran NULLs:

SELECT 
  type, 
  SUM(views), 
  MIN(views), 
MAX(views)
FROM posts
GROUP BY type;

type	SUM	MIN	MAX
video	230,485	5,632	100,589
image	159,340	1,289	45,003
text	34,224	256	3,341
infographics

La función SUM() trata NULL como si fuera 0, por lo que NULL no influye en el resultado de SUM. Pero si todos los valores del grupo son NULL, el resultado de SUM() es NULL. En nuestro ejemplo, no tenemos ningún dato de vista para el grupo infografía, por lo que la suma es NULL para este grupo.

Las funciones MIN() y MAX() también ignoran NULL; devuelven los valores mínimo y máximo de los valores conocidos. Sólo si todos los valores del grupo son NULL estas funciones devolverán NULL. Nuestro grupo infográfico no tiene datos, por lo que los valores mínimo y máximo se informan como NULL.

La función COUNT() es un poco más sutil a la hora de tratar NULL. Existen tres variantes de la sintaxis de COUNT: COUNT(*), COUNT(expression), COUNT(DISTINCT). Puede consultarlas en nuestro artículo ¿Cuál es la diferencia entre COUNT(*), COUNT(1), COUNT(column) y COUNT(DISTINCT)?

SELECT 
  COUNT(*), 
  COUNT(location), 
  COUNT(DISTINCT location)
FROM posts;

COUNT	COUNT	COUNT
100	78	52

La expresión COUNT(*) cuenta todas las filas del conjunto de resultados. Hay 100 entradas en nuestra posts por lo que esta expresión devuelve 100.

La expresión COUNT(location) cuenta los valores noNULL de la columna dada. En nuestro ejemplo, contará los mensajes en los que la columna location no sea NULL. Ignorará los mensajes con ubicaciones desconocidas.

Por último, la expresión COUNT(DISTINCT location) cuenta los valores distintos noNULL; en otras palabras, ignora los valores repetidos. Contará cuántas ubicaciones diferentes hay en nuestra tabla posts tabla.

La función AVG() ignora NULL. Por lo general, esto es lo que se espera. Sin embargo, debe tener cuidado al utilizar AVG() con COALESCE(). Todas las variantes siguientes devuelven valores diferentes: AVG(views), AVG(COALESCE(views,0)), COALESCE(AVG(views)).

Recuerde:

• Las filas con NULLs en columnas GROUP BY se ponen en un grupo separado.
• Las funciones agregadas ignoran NULL y sólo utilizan valores conocidos en los cálculos.
• Utilice COALESCE si desea sustituir un valor desconocido por un valor específico.

NULL y JOIN

Tienes que recordar lo de NULL cuando uses JOIN, especialmente con OUTER JOINs como LEFT JOIN o FULL JOIN. Puede haber NULLs en columnas que vienen de la tabla correcta.

Imaginemos que tenemos otra tabla commentsque contiene datos sobre los comentarios de las entradas. Tiene información en las siguientes columnas:

• id - Un identificador único para cada comentario.
• post_id - El identificador del post sobre el que versa el comentario.
• content - El contenido del comentario.
• author - El autor del comentario
• upvotes - El número de upvotes dados a este comentario; puede ser NULL
• downvotes - El número de downvotes dados a este comentario; puede ser NULL

Queremos contar cuántos comentarios hay para cada entrada, pero queremos incluir las entradas sin comentarios en los resultados. Tienes que usar posts LEFT JOIN comments para incluir todos los posts.

Después, tienes que recordar usar COUNT(comments.id) y no COUNT(*) cuando cuentes comentarios. Este último contará filas independientemente de si la fila está relacionada con el comentario. Lo correcto es utilizar COUNT(comments.id). Si no hay comentarios, el id es NULL y no se cuenta.

SELECT 
  posts.title, 
  COUNT(comments.id)
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id;

Otro problema a tener en cuenta es que la condición WHERE a veces puede "anular" la OUTER JOIN. En la consulta siguiente, queremos encontrar comentarios con upvotes superior a 100. Si la entrada tiene algunos comentarios con un número desconocido de upvotes, estos comentarios no se incluirán en el resultado. Si la entrada sólo tiene comentarios con un número desconocido de upvotes, la entrada no se incluirá en absoluto, a pesar de que utilicemos LEFT JOIN. La condición WHERE "cancelará" la condición LEFT JOIN:

SELECT 
  posts.title, 
  comments.content
FROM posts
LEFT JOIN comments
  ON posts.id = comments.post_id
WHERE upvotes > 100;

Recuerda:

• LEFT JOIN RIGHT JOIN o pueden introducir en el resultado. FULL JOIN NULL
• La condición WHERE puede "anular" la OUTER JOIN.

NULL en ORDER BY

Cuando creas un informe, a menudo quieres ordenar los datos en un orden específico, por ejemplo, en orden alfabético, ascendente o descendente. ¿Cómo se comporta NULL en la ordenación?

Cuando ordena por una columna que contiene NULL, las filas con NULL aparecerán primero o último, dependiendo del motor de base de datos que esté utilizando. Por ejemplo, MySQL coloca NULLs en primer lugar para ordenaciones ascendentes, mientras que Oracle las coloca en último lugar para ordenaciones ascendentes. Puedes consultar el comportamiento por defecto de tu base de datos en su documentación.

Si no recuerda el comportamiento predeterminado o no le gusta, puede utilizar los operadores NULLS FIRST o NULLS LAST después de ORDER BY para especificar el comportamiento deseado:

SELECT 
  title, 
  views
FROM posts
ORDER BY views DESC NULLS LAST;

De este modo, las filas que contengan NULL aparecerán en último lugar:

title	views
Quick Morning Routines!	120,365
…	…
Eco-Friendly Living Tips	256
Easy At-Home Workouts for All Levels
Healthy Snacks on the Go

Puede leer en detalle cómo funciona NULL con ORDER BY en How ORDER BY and NULL Work Together in SQL.

Manejo de Datos Perdidos con NULL en SQL

Manejar NULL y datos faltantes en SQL es una habilidad importante para cualquiera que trabaje con datos. Comprender los matices de NULL, su comportamiento en diferentes operaciones y las mejores prácticas para gestionar los datos que faltan garantiza que sus consultas sean precisas y sus análisis fiables.

Para profundizar en sus conocimientos de SQL, considere la posibilidad de adquirir nuestro paqueteTodo, para siempre SQL. Esta oferta de pago único proporciona acceso de por vida a todos los cursos de SQL actuales y futuros. Los cursos abarcan desde consultas básicas hasta SQL avanzado; lo que aprenda le será útil en todos los niveles de su carrera. Además, asegúrese de consultar nuestra pista Ejercicio de SQL con 10 cursos prácticos de SQL y más de 1.000 ejercicios. Mejore sus conocimientos a largo plazo con LearnSQL.es.