El NULL de SQL es raro

• En SQL, los valores NULL se manejan de forma particular. Una columna con restricción UNIQUE puede tener varios valores NULL.

  • Esto se debe a que cada valor NULL se considera un valor independiente y distinto de cualquier otro NULL.
  • SQLite, Postgres y MySQL se comportan igual.

• Estableciendo la referencia

  select '' = '';    -- Returns 1 (true) las cadenas vacías son iguales   
  select 1 = 1;      -- Returns 1 (true) los números son iguales   
  select 1 = 0;      -- Returns 0 (false) los números son distintos   
  select null = null; -- Returns NULL (null) ¿eh?  
  

  • Esto se debe a que NULL es un marcador de posición para un "valor desconocido", así que no se considera que dos valores desconocidos sean iguales.
  • Si usas el operador IS, puedes verificar la identidad de NULL. Por ejemplo, null is null devuelve TRUE.

• Sobre la unicidad

  • Si una columna con restricción UNIQUE contiene valores NULL, esos NULL se consideran distintos entre sí y no violan la restricción de unicidad.
  • Por ejemplo, ('[e-mail]', NULL) y ('[e-mail]', NULL) se consideran filas distintas.

• Por qué NULL se maneja así

  • SQLite y otras bases de datos compatibles con SQL lo implementan de esta manera para mantener consistencia con otras bases de datos. La documentación del estándar SQL sugiere que NULL debería ser único en todas partes, pero en la práctica la mayoría de los motores SQL no tratan a NULL como único en SELECT DISTINCT o UNION.

• Cómo garantizar la unicidad

  • Usar columnas generadas

    • Puedes mitigar el problema creando una columna que siempre tenga un valor determinista que no sea NULL. Por ejemplo, puedes usar COALESCE(deleted_at, '1970-01-01') para reemplazar valores NULL.
    • Este método puede ocupar espacio al agregar un campo adicional a la tabla.

  • Usar índices parciales

    • Puedes garantizar la unicidad creando un índice parcial sobre email solo cuando deleted_at sea NULL.
    • Los índices parciales consumen menos espacio sin ensanchar la tabla, y no generan errores al eliminar repetidamente el mismo par de registros.

• Actualización

  • Oracle trata las cadenas vacías como NULL.

• Conclusión

  • Cuando usas un ORM no se nota, pero la forma particular en que SQL maneja NULL puede causar confusión. La documentación del estándar SQL no se ofrece públicamente y solo puede obtenerse pagando.

[iolothebard]

Todos los null son raros.
Por eso, el null perfectamente normal de SQL termina pareciendo el raro…
En el país de los tuertos, el de dos ojos es el anormal…

[GN⁺]

Comentarios de Hacker News

• El NULL de SQL se basa en la lógica TRUE-FALSE-UNKNOWN de Kleene. Si se lee NULL como UNKNOWN, muchas operaciones se entienden de forma más intuitiva

  • TRUE OR UNKNOWN = TRUE, TRUE AND UNKNOWN = UNKNOWN, UNKNOWN XOR UNKNOWN = UNKNOWN, etc.
  • NULL es un marcador de posición que representa UNKNOWN, y no se puede decir que dos NULL sean iguales
  • Desde PostgreSQL 15, se pueden crear índices únicos usando NULLS NOT DISTINCT

• Cuando se introdujo el concepto de NULL en la década de 1970, se pensó que provocaría mucha confusión en el futuro. Han pasado 45 años y todavía sigue en discusión

• Una forma intuitiva de entender NULL: el valor NULL en una celda específica de una tabla es una manera de indicar “sin valor”. Cuando se quiere un valor único, los casos sin valor no deberían tomarse en cuenta

• Escepticismo sobre el uso de ORM: los ORM son convenientes, pero han creado una generación que no aprendió cómo funcionan realmente las bases de datos relacionales. El comportamiento de NULL en SQL coincide con el álgebra relacional básica; el problema es el NULL al estilo de C

• Recuerda el humor de las comparaciones con NULL en un diálogo de un episodio de Blackadder

• Parece extraño que en Oracle NULL sea lo mismo que una cadena vacía

• En un contexto orientado a objetos, "null" es útil para indicar que cierta propiedad no tiene valor. En JavaScript existen null y undefined; se puede asumir que undefined significa que no se conoce el valor y null que no hay valor

• NULL no es extraño en el sentido de que no haya duplicados. Como los NULL no son iguales entre sí, no pueden considerarse duplicados. Si no te gusta la semántica de NULL, puedes usar un valor centinela

• El NULL de SQL no es extraño si se considera cómo se quiere que funcione la lógica relacional en registros donde hay valores inexistentes