Cómo utilizar los operadores de comparación con NULL en SQL

El valor NULL de SQL tiene un propósito especial. También viene con comportamientos contraintuitivos que pueden hacer tropezar a los principiantes de SQL o incluso a los programadores experimentados. Aprende a evitar estos problemas cuando uses NULL con operadores de comparación.

Este artículo le ayudará a dominar las mejores prácticas para elaborar consultas SQL que funcionen con valores NULL y utilicen operadores de comparación ( =, <>, <, > ) - lo cual, si ha escrito alguna consulta SQL antes, sabe que son casi todas las consultas que escribirá. Este es un conocimiento esencial y dominarlo hará que SQL sea más fácil para ti.

Vamos a hacer un rápido recorrido por los NULLs de SQL, por qué existen y cómo su rareza afecta a los resultados de las consultas. Esto será seguido por algunas consultas SQL que demuestran la rareza. Luego discutiremos las técnicas estándar para escribir consultas que manejen correctamente los NULLs y los operadores de comparación. Finalmente, habrá un resumen rápido de todos los operadores de comparación de SQL y cómo cada uno interactúa con NULL.

No te preocupes. No es tan difícil como parece. Comencemos revisando los valores NULL en SQL.

NULL en SQL - Lo desconocido

Las bases de datos están pensadas para ser una única fuente de verdad. Lo que se registra en los campos de cada fila representa lo que se conoce.

Mira la tabla de abajo, que ha sido adaptada de What Is a NULL in SQL (un gran recurso para una inmersión profunda en los NULL de SQL). Esto podría ser parte de una base de datos creada por un superfan de los Simpsons.

NULL está representando dos cosas diferentes aquí. Se sabe que Waylon Smithers es soltero, por lo que NULL en esta fila de la columna cónyuge representa un valor inexistente. Pero sabemos tan poco sobre el Dr. Nick que los valores NULL en sus columnas spouse y status representan un valor desconocido.

Para mantener la integridad de una base de datos, ambas interpretaciones de NULL son necesarias. Y para ayudar a los programadores a mantener esa integridad a pesar de los valores perdidos y desconocidos, SQL incorpora los NULL en su lógica ternaria.

¿Qué es la lógica ternaria y cómo funciona en SQL?

La lógica binaria utiliza dos valores: Verdadero y Falso, 0 y 1, etc. La lógica ternaria utiliza tres valores. En SQL, esos tres valores son Verdadero, Falso y Desconocido. En la lógica ternaria de SQL, NULL equivale al valor de desconocido.

Así es como los tres valores de la lógica ternaria operan con los operadores lógicos de SQL NOT, OR y AND:

Lo que hay que tener en cuenta: Cualquier operación lógica que implique un NULL da como resultado un valor desconocido , excepto TRUE OR NULL y FALSE AND NULL. Interesante, pero no es algo que debas memorizar. Como verás, SQL nos ayuda a sortear esta complicada lógica. Lo cual es útil, porque las operaciones de comparación de SQL utilizan la misma lógica ternaria.

Operaciones de comparación SQL y valores NULL

Esta es una consulta que utiliza operadores lógicos y de comparación. Adivina cuántas filas devuelve:

SELECT spouse
  FROM simpsons
 WHERE      spouse = NULL
    OR NOT (spouse = NULL) 

Empecemos con la primera operación de comparación:

WHERE      spouse = NULL

Cualquiera que sea el contenido de la columna de comparación -sueldos, nombres de mascotas, etc.- si comprobamos que es igual a NULL, el resultado es desconocido. Esto es cierto incluso si el valor de la columna es NULL. Esto es lo que confunde a los programadores con experiencia en otros lenguajes. Por ejemplo, en Python el valor None parece similar al NULL de SQL y puede ser comparado contra sí mismo:

>>> None == None
True

Pero la primera línea de la cláusula WHERE va a devolver desconocido. Así que cuando nuestra consulta se evalúe, se verá así:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR NOT (spouse = NULL) 

Veamos la segunda línea de la condición WHERE:

   OR NOT (spouse = NULL)
	 

Esta comparación también va a devolver desconocido. Podemos ver en la tabla de verdad anterior que NOT unknown va a devolver unknown. Así que ahora nuestra consulta se ha convertido en:

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR unknown 
	 

La tabla de verdad OR anterior nos dice que el resultado de esto será desconocido.

Una cláusula WHERE requiere condiciones verdaderas. Que el resultado no sea falso no es suficiente. Así que a pesar de que la consulta parece que devolverá todas las filas, la lógica ternaria de SQL y la naturaleza de SQL NULL hace que se devuelvan cero filas.

Otros Operadores de Comparación de SQL

Para estos ejemplos, vamos a utilizar una tabla diferente, pet_owners:

NULL y el operador <

Usando la tablapet_owners , veamos quién tiene menos de 5 mascotas.

El resultado:

¿Por qué? Alice, que no ha completado su formulario de encuesta sobre mascotas, tiene NULL para su pet_count. El valor de cualquier NULL es desconocido. ¿Es NULL < 5? Es desconocido, por lo que Alice no puede ser incluida en los resultados.

NULL y el operador >

Ahora veremos quién tiene más de 3 mascotas.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count > 3
	 

El resultado:

¿Por qué? De nuevo es el valor desconocido de NULL. Al igual que se desconoce si NULL 3 < 5, it is unknown if NULL >. Alice está excluida de los resultados.

NULL y el operador <=

En un sutil cambio, ahora obtendremos una lista de todos los que no tengan más de 5 mascotas.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count <= 5
	 

El resultado:

Al cambiar nuestra primera consulta de usar < a usar <= se añade a Bob al conjunto de resultados, pero no a Alicia. En la primera consulta, el número de mascotas de Bob (5) no es inferior a 5. Pero es menor o igual que 5, por lo que ahora está incluido en el resultado de la consulta. Alice sigue sin aparecer.

Al examinar la fila de Alicia, podemos pensar que <= es la abreviatura de "NULL < 5 O NULL = 5". Sabemos por lo anterior que "NULL < CUALQUIER COSA" devolverá un resultado desconocido; por lo anterior, sabemos que "NULL = CUALQUIER COSA" también devolverá un resultado desconocido. Una vez más, Alicia queda excluida de los resultados.

NULL y el operador >=

Ahora veamos quién tiene al menos 3 mascotas.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count >= 3
	 

El resultado:

Al igual que <=, podemos pensar en >= como una combinación lógica de dos operaciones de comparación. Así, para Alicia, la comparación es equivalente a "NULL > 3 O NULL = 3". Ahora deberías tener claro que esto sólo puede dar como resultado un valor desconocido.

Operadores de comparación SQL que trabajan con NULLs

Para manejar correctamente los NULL, SQL proporciona dos operadores de comparación especiales: IS NULL y IS NOT NULL. Sólo devuelven verdadero o falso y son la mejor práctica para incorporar valores NULL en las consultas.

Para obtener el resultado que esperábamos, podemos reescribir la consulta de Los Simpson de la siguiente manera:

SELECT spouse
  FROM simpsons
 WHERE spouse IS NULL
    OR spouse IS NOT NULL
	 

Ahora la consulta devolverá todas las filas, como esperábamos.

SQL NULL y Outer Joins

Este es un tema más avanzado. Si eres nuevo en Tipos de JOIN en SQL, probablemente deberías leer primero 7 ejemplos de JOIN SQL con explicaciones detalladas.

Es común usar una cláusula WHERE junto con el operador IS NOT NULL para deshacerse de las filas con valores NULL. Pero esto a veces puede ser una manera ineficiente de lograr un resultado. A continuación se explica el motivo.

Las uniones externas - un LEFT, RIGHT, o FULL JOIN - pueden considerarse como un INNER JOIN (que devuelve filas coincidentes) más filas no coincidentes con columnas rellenadas con NULLs.

Un LEFT JOIN devuelve todas las filas de la tabla izquierda de la unión con las filas coincidentes de la tabla derecha (o valores NULL cuando no hay coincidencias). Un RIGHT JOIN devuelve todas las filas de la tabla derecha con filas coincidentes (o NULLs) de la tabla izquierda. Un FULL JOIN es como un INNER JOIN que también devuelve todas las filas de la tabla izquierda y derecha que no coinciden, ampliadas por NULLs.

Si la cláusula WHERE de su consulta está filtrando las filas que han sido extendidas por NULLs, básicamente está cancelando su unión externa. Debería reescribir su consulta utilizando una cláusula INNER JOIN.

En otros casos, los NULLs interactuarán con su cláusula WHERE y causarán resultados incorrectos. Veamos un ejemplo que demuestra esto. Puede ejecutarlo usted mismo usando este SQL Fiddle. (Aquí hay una guía de más sitios para usar en la práctica de SQL).

Para este ejemplo, vamos a usar dos tablas, una para customers y otra para orders.

Tabla customers

Tabla orders

Queremos ver el importe gastado por todos nuestros clientes registrados desde el 4 de febrero de 2021 ("2021-02-04"). Comenzamos con un INNER JOIN. Hay algunas funciones SQL adicionales en esta consulta que podrían ser nuevas para ti, pero LearnSQL te tiene cubierto. Tenemos artículos sobre COALESCE, funciones agregadas como SUM y la cláusula GROUP BY. No necesitas preocuparte por lo que hacen estas funciones ahora mismo. Sólo enfócate en lo que devuelve cada consulta, empezando por ésta:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
INNER JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

La consulta produce este resultado:

Se ve bien, pero tenemos 3 clientes. Si queremos ver todos los clientes, tenemos que usar un LEFT OUTER JOIN (también conocido como LEFT JOIN para abreviar). Incluirá todas las filas de la tabla izquierda de la sentencia FROM aunque no haya datos que coincidan en la tabla derecha. Esto nos da nuestra siguiente consulta:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Los resultados podrían sorprenderle:

¿Por qué ocurre esto? ¿Por qué sigue sin aparecer Alice? Una consulta más sencilla nos dará una pista:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

El resultado:

Como ya hemos dicho, LEFT JOIN incluye todas las filas de la tabla izquierda. Cuando no hay ninguna fila que coincida en la tabla derecha, las columnas se rellenan con NULLs.

La cláusula WHERE realiza su filtrado después de JOIN, por lo que se eliminarán todas las filas de Alicia, ya que al comparar cualquier cosa con NULL, como un order_date inexistente, se obtiene un resultado desconocido .WHERE sólo devuelve una fila cuando las condiciones se evalúan como TRUE.

La forma de arreglar esto es mover la expresión condicional, aquí o.order_date > '2021-02-04', dentro de JOIN incluyéndola en la cláusula ON:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Como estamos utilizando un LEFT JOIN, Alicia permanece en el conjunto de resultados a pesar de la condición de fecha adicional. Su NULL se convierte en un "0" más limpio gracias a la función COALESCE(). El resultado de la consulta es ahora el que esperábamos:

Más información sobre los NULL de SQL

Ahora debería entender cómo SQL trata los valores NULL y las mejores prácticas para trabajar con ellos. Sabes sobre los operadores IS NULL y IS NOT NULL y cómo la lógica ternaria de SQL siempre devuelve desconocido cuando cualquier cosa es comparada con un NULL excepto en dos casos especiales. También has visto cómo reescribir las consultas para no tener que filtrar esos molestos NULL.

Pero hay más cosas que saber sobre cómo trabajar con NULLs en SQL. Te sugiero que sigas aprendiendo con los artículos Cómo funcionan juntos ORDER BY y NULL en SQL y Valores NULL y la cláusula GROUP BY.

Y si quieres dominar de verdad el SQL, te recomiendo el Ejercicios prácticos de SQL o el curso Ejercicio de SQL . Ofrecen una experiencia de aprendizaje exhaustiva y fácil de seguir que le ayudará a perfeccionar su arte.