O NULL do SQL é estranho

• No SQL, valores NULL são tratados de forma peculiar. Uma coluna com restrição UNIQUE pode ter vários valores NULL.

  • Isso acontece porque cada valor NULL é considerado um valor independente, diferente dos outros NULL
  • SQLite, Postgres e MySQL se comportam assim da mesma forma.

• Definindo a base

  select '' = '';    -- Returns 1 (true) strings vazias são iguais   
  select 1 = 1;      -- Returns 1 (true) números são iguais   
  select 1 = 0;      -- Returns 0 (false) números são diferentes   
  select null = null; -- Returns NULL (null) ué?  
  

  • Isso acontece porque NULL é um marcador que representa um "valor desconhecido", então dois valores desconhecidos não são considerados iguais
  • Usando o operador IS, é possível verificar a identidade de NULL. Por exemplo, null is null retorna TRUE.

• Sobre unicidade

  • Quando uma coluna com restrição UNIQUE contém valores NULL, esses valores NULL são considerados distintos entre si e, portanto, não violam a restrição de unicidade.
  • Por exemplo, ('[e-mail]', NULL) e ('[e-mail]', NULL) são considerados linhas diferentes.

• Por que o NULL é tratado assim

  • O SQLite e outros bancos de dados compatíveis com SQL implementam isso dessa forma para manter consistência com outros bancos. A documentação do padrão SQL sugere que NULL deveria ser único em todos os contextos, mas, na prática, a maioria dos motores SQL não trata NULL como único em SELECT DISTINCT ou UNION.

• Como garantir unicidade

  • Usando colunas geradas

    • É possível mitigar o problema criando uma coluna que sempre tenha um valor determinístico não nulo. Por exemplo, pode-se usar COALESCE(deleted_at, '1970-01-01') para substituir valores NULL.
    • Esse método pode ocupar espaço ao adicionar um campo extra à tabela.

  • Usando índice parcial

    • É possível garantir unicidade criando um índice parcial em email apenas quando deleted_at for NULL.
    • Índices parciais não ampliam a tabela, ocupam menos espaço e não geram erro ao excluir repetidamente o mesmo par de registros.

• Atualização

  • O Oracle trata strings vazias como NULL.

• Conclusão

  • Ao usar ORM isso pode passar despercebido, mas a forma peculiar como o SQL trata NULL pode causar confusão. A documentação do padrão SQL não é disponibilizada publicamente e só pode ser obtida mediante pagamento.

[iolothebard]

Todo null é estranho.
Por isso, o null perfeitamente normal do SQL acaba parecendo o estranho…
Em terra de caolho, quem tem dois olhos é anormal…

[GN⁺]

Comentários do Hacker News

• O NULL do SQL se baseia na lógica TRUE-FALSE-UNKNOWN de Kleene. Se você ler NULL como UNKNOWN, várias operações ficam mais intuitivas de entender

  • TRUE OR UNKNOWN = TRUE, TRUE AND UNKNOWN = UNKNOWN, UNKNOWN XOR UNKNOWN = UNKNOWN etc.
  • NULL é um marcador que representa UNKNOWN, e não se pode dizer que dois NULL são iguais
  • A partir do PostgreSQL 15, é possível criar índices únicos usando NULLS NOT DISTINCT

• Quando o conceito de NULL foi introduzido nos anos 1970, já se pensava que ele causaria muita confusão no futuro. Passados 45 anos, ele ainda continua sendo debatido

• Uma forma intuitiva de entender NULL: o valor NULL em uma célula específica da tabela é uma maneira de representar “sem valor”. Quando você quer valores únicos, casos sem valor não devem ser considerados

• Ceticismo em relação ao uso de ORM: ORMs são convenientes, mas criaram uma geração que não aprendeu como bancos de dados relacionais realmente funcionam. O comportamento do NULL em SQL está de acordo com a álgebra relacional básica; o problema é o NULL no estilo C

• Isso relembra o humor de comparações com NULL no diálogo de um episódio de Blackadder

• Acho estranho que, no Oracle, NULL seja igual a string vazia

• Em um contexto orientado a objetos, null é útil para indicar que uma determinada propriedade não tem valor. Em JavaScript, há null e undefined; pode-se supor que undefined significa que o valor é desconhecido, e null significa que não há valor

• NULL não é estranho no sentido de não haver duplicação. Como NULL não é igual a outro NULL, ele não pode ser considerado duplicado. Se você não gosta da semântica de NULL, pode usar um valor sentinela

• O NULL do SQL não é estranho se você considerar como a lógica relacional deve funcionar em registros que contêm valores inexistentes