Una consulta se ejecuta sin errores y devuelve un número que parece razonable, pero faltan filas y el promedio es incorrecto. Los valores SQL NULL suelen ser la causa. NULL no se comporta como ningún valor normal y eso sorprende tanto a principiantes como a desarrolladores experimentados.

Esta guía muestra exactamente cómo se comporta NULL. Ejecutamos todas las consultas en SQLite 3.50 y los resultados mostrados provienen de esa ejecución. La mayor parte de este comportamiento es SQL estándar, y observamos dónde difieren las bases de datos, como el orden de clasificación NULL y el tratamiento de cadenas vacías por parte de Oracle.

¿Qué es NULO?

NULL es un marcador que significa que falta un valor o se desconoce. No es un valor en el sentido habitual, por lo que son cosas diferentes:

La diferencia es especialmente importante en el caso de las cantidades. Una bonificación de 0 significa que la persona no recibió nada. Una bonificación NULL significa que nadie lo sabe todavía.

La tabla de muestra

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Observe que Sara tiene una cadena vacía para el teléfono, mientras que Arjun y Ravi tienen NULL. Los dos reciben un trato muy diferente.

Prueba de NULL

Este es el error que casi todo el mundo comete al principio:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

La primera consulta es SQL válida y no da ningún error. Simplemente no devuelve nada, porque comparar algo con NULL nunca es cierto. Sara aparece debajo de IS NOT NULL, ya que una cadena vacía es un valor real. Sin embargo, en Oracle una cadena vacía se trata como NULL, lo cual es una excepción notable.

Lógica de tres valores

En la mayoría de los lenguajes de programación, una condición es verdadera o falsa. SQL agrega un tercer resultado: desconocido. Si no conoce un valor, no puede decir si es igual a 5. Y no se sabe que dos valores desconocidos sean iguales, razón por la cual NULL = NULL no es cierto. En nuestra prueba, todos estos arrojaron NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

La combinación de condiciones sigue reglas cuidadosas. Estos son los resultados de nuestra ejecución, donde 1 es verdadero y 0 es falso:

Filas que desaparecen de DONDE

WHERE mantiene una fila solo si su condición es verdadera. Las filas en las que se desconoce el resultado se eliminan, junto con las que son falsas. Esto lleva a resultados sorprendentes:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Es de esperar que aparezcan Arjun y Ravi, ya que su teléfono ciertamente no es ese número. Pero se desconoce su teléfono, por lo que se desconoce el resultado y desaparecen. Para incluirlos, dígalo explícitamente: WHERE phone <> '555-0101' OR phone IS NULL. La negación tampoco ayuda:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara y Ravi no tienen bonificación, por lo que no aparecen ni en bonus > 400 ni en su opuesto. Es fundamental recordar esto: una condición y su negación no siempre cubren todas las filas.

COALESCER: proporcionar un respaldo

COALESCE devuelve el primer valor no NULL de su lista y funciona en todas las bases de datos principales:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara todavía aparece en blanco porque una cadena vacía no es NULL. Existen funciones similares, como IFNULL en MySQL y SQLite, ISNULL en SQL Server y NVL en Oracle. COALESCE es la opción portátil. Nuestro punto anterior sobre los cálculos también se aplica aquí. bonus + 100 da NULL para cualquier persona sin bonificación, así que escriba COALESCE(bonus, 0) + 100 si tratar una bonificación faltante como cero es correcto para su negocio.

NULLIF: convierte un valor en NULL

NULLIF(a, b) devuelve NULL si los dos valores son iguales y, en caso contrario, devuelve a. Dos usos son especialmente útiles. Puede limpiar los valores de los marcadores de posición:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Esa segunda consulta es una forma práctica de encontrar valores que “faltan o están en blanco”. También puede proteger contra la división por cero, lo que genera un error en muchas bases de datos:

SELECT total / NULLIF(quantity, 0) FROM sales;

Cuando quantity es 0, esto da NULL en lugar de fallar. En nuestra prueba, SELECT 100 / NULLIF(0, 0) devolvió NULL.

Los agregados omiten NULL

Aquí es donde NULL causa más daño a los informes:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Entonces, ¿cuál es el bono promedio, 400 o 200? Depende de lo que significa NULL en sus datos. Si NULL significa “no se pagó ningún bono”, entonces 200 es correcto. Si significa “aún no decidido”, entonces 400 puede tener razón. La base de datos no puede tomar esta decisión por usted, por lo que la pregunta debe dirigirse a alguien que conozca el negocio.

Una trampa relacionada: SUM sin valores es NULL en lugar de 0. Cuando sumamos solo las filas sin bonificación, obtuvimos NULL, mientras que COUNT devolvió 0. El código de la aplicación que espera un número puede fallar en esto, así que envuélvalo: COALESCE(SUM(bonus), 0).

GRUPO POR, clasificación y DISTINTO

Aunque los NULL “no son iguales” en las comparaciones, estas operaciones los tratan como un grupo:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Consulte SQL GROUP BY y HAVING para obtener más información sobre la agrupación. El orden de clasificación es donde realmente difieren las bases de datos. SQLite y MySQL tratan a NULL como el valor más pequeño, mientras que PostgreSQL y Oracle lo tratan como el más grande. Entonces el mismo ORDER BY bonus DESC puede poner los NULL al final en uno y al principio en otro. PostgreSQL, Oracle y SQLite moderno le permiten indicar esto directamente, usando NULLS FIRST o NULLS LAST.

Se une

Una condición de unión que utiliza = nunca coincide con NULL, por lo que las filas con una clave NULL salen de una unión interna:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera no tiene administrador, por lo que una combinación interna elimina esa fila silenciosamente. Un LEFT JOIN lo mantiene y también crea sus propios NULL para el lado que falta. Lea más en Explicación de uniones SQL.

La trampa NO ENTRAR

Un solo NULL en una lista NOT IN borra todos los resultados:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

La segunda forma significa manager_id <> 1 AND manager_id <> NULL, y esa última parte nunca puede ser cierta. Esto es particularmente peligroso con las subconsultas, donde un NULL puede pasar desapercibido. Utilice NOT EXISTS en su lugar, como se explica en nuestro tutorial de subconsultas SQL. Meera también falta en el primer resultado, ya que el manager_id de esa fila es NULL.

Diseñando con NULL

• Utilice NOT NULL de forma predeterminada. Permita NULL solo cuando realmente pueda faltar un valor.
• Agregue valores predeterminados sensatos. Por ejemplo, quantity INT NOT NULL DEFAULT 0.
• No utilice valores mágicos. Una fecha de 1900-01-01 o una edad de -1 en lugar de “desconocido” corromperá sus cálculos.
• Conozca el comportamiento ÚNICO. La mayoría de las bases de datos permiten varios NULL en una columna UNIQUE y nuestra prueba insertó dos sin error. SQL Server es una excepción, ya que normalmente lo permite.
• Recuerde que una clave principal no puede ser NULL. Consulte clave principal versus clave externa.
• Prueba con NULL. Los datos de prueba sin NULL ocultan estos errores. Inclúyalos a propósito, como lo haría con otros casos extremos.

Conclusión

Trate a NULL como “desconocido” y la mayor parte de su comportamiento comenzará a tener sentido: desconocido más uno es desconocido, y desconocido es igual a desconocido no se sabe que sea verdadero. Audite uno de sus propios informes esta semana. Verifique las columnas que aceptan valores NULL usando COUNT(*) contra COUNT(column), busque cualquier NOT IN y decida qué debería significar NULL en cada promedio.

Preguntas frecuentes

sigue aprendiendo