Cuidado con not in y los valores nulos

Cuando usamos el operador not in sobre el resultado de una subconsulta es necesario ser precavido ante la posible presencia de valores nulos en ese resultado.

Ya deberías saber que, si el conjunto de valores sobre el que se aplica not in contiene algún valor null, la condición que estemos evaluando no se verificará nunca

Para ilustrarlo, consideremos este ejemplo (te recomiendo que lo pruebes usando los enlaces a DB Fiddle que se proporcionan). Disponemos de una tabla de clientes de un comercio con la siguiente información:

Cliente
+----+-------------+--------+---------+
| id | nombre      | genero | ciudad  |
+----+-------------+--------+---------+
|  1 | Ana Abad    | M      | Logroño |
|  2 | Bea Bez     | M      | Logroño |
|  3 | Carlos Cruz | H      | Logroño |
|  4 | Diego Díaz  | H      | NULL    |
|  5 | Eva Ena     | M      | Madrid  |
+----+-------------+--------+---------+

Queremos obtener un listado de clientas (mujeres) que viven en ciudades en las que no vive ningún cliente varón. Para ello planteamos una consulta que, a priori, parece correcta y que debería mostrar la fila de la cliente número 5.

select *
from Cliente
where genero='M'
      and ciudad not in (select ciudad
                         from Cliente
                         where genero='H');

Pruébalo en DB Fiddle

Pero el resultado es que no hay resultados.

La explicación de este fenómeno está en que el resultado de la subconsulta son estos dos valores ('Logroño', null). Debido al null ahí contenido, ninguna de las comprobaciones ciudad not in ('Logroño', null) de las tuplas de clientas mujeres es evaluada a true. En el caso de las que viven el Logroño, es obvio que la condición es falsa; pero incluso en el caso de la clienta de Madrid, debido al valor null no se puede asegurar que la condición sea cierta.

¿Qué se puede hacer entonces? Lo que siempre aconsejamos ante una consulta que requiera usar not in es hacer alguna de estas dos cosas:

• Asegurarse de que los valores devueltos por la subconsulta son de alguna clave. Eso impedirá que aparezcan valores nulos
• Evitar la presencia de nulos incluyendo alguna cláusula is not null
• No usar not in, siempre que se te ocurra otra solución que sea sencilla.

En nuestro ejemplo, podríamos haber salido del paso con esta consulta

select *
from Cliente
where genero='M'
      and ciudad not in (select ciudad
                         from Cliente
                         where genero='H' and ciudad is not null);

Pruébalo en DB Fiddle

Rizando el rizo

Un purista podría decir que el resultado de la consulta anterior debería ser una tabla vacía. Y, la verdad, no le faltaría razón, ya que como no sabemos donde vive el cliente 'Diego Díaz' no podríamos afirmar con seguridad que las mujeres no vivan en su ciudad (sea la que sea esta). Por eso, hubiese sido mejor enunciar la consulta de esta manera: Listado de clientas (mujeres) que viven en ciudades en las que se sepa que no vive ningún cliente varón.