Uma consulta é executada sem erros e retorna um número aparentemente sensato, mas faltam linhas e a média está errada. Valores SQL NULL são muitas vezes a causa. NULL não se comporta como nenhum valor normal e isso surpreende tanto iniciantes quanto desenvolvedores experientes.

Este guia mostra exatamente como o NULL se comporta. Executamos todas as consultas no SQLite 3.50 e os resultados mostrados vêm dessa execução. A maior parte desse comportamento é SQL padrão e observamos onde os bancos de dados diferem, como a ordem de classificação NULL e o tratamento de strings vazias pelo Oracle.

O que é NULO?

NULL é um marcador que significa que um valor está faltando ou é desconhecido. Não é um valor no sentido usual, então são coisas diferentes:

A diferença é importante especialmente para os valores. Um bônus de 0 significa que a pessoa não recebeu nada. Um bônus NULL significa que ninguém sabe ainda.

A tabela de amostra

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 possui uma string vazia para o telefone, enquanto Arjun e Ravi possuem NULL. Os dois são tratados de forma muito diferente.

Testando para NULL

Aqui está o erro que quase todo mundo comete no início:

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

A primeira consulta é SQL válida e não apresenta erros. Simplesmente não retorna nada, porque comparar qualquer coisa com NULL nunca é verdade. Sara aparece em IS NOT NULL, pois uma string vazia é um valor real. No Oracle, porém, uma string vazia é tratada como NULL, o que é uma exceção notável.

Lógica de três valores

Na maioria das linguagens de programação, uma condição é verdadeira ou falsa. SQL adiciona um terceiro resultado: desconhecido. Se você não conhece um valor, não pode dizer se ele é igual a 5. E dois valores desconhecidos não são iguais, e é por isso que NULL = NULL não é verdadeiro. Em nosso teste, todos retornaram NULL:

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

-- NULL | NULL | NULL | NULL

A combinação de condições segue regras cuidadosas. Aqui estão os resultados da nossa execução, onde 1 é verdadeiro e 0 é falso:

Linhas que desaparecem de WHERE

WHERE mantém uma linha somente se sua condição for verdadeira. As linhas onde o resultado é desconhecido são eliminadas, juntamente com aquelas que são falsas. Isso leva a resultados surpreendentes:

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

-- Sara

Você pode esperar que Arjun e Ravi apareçam, já que o telefone deles certamente não é esse número. Mas o telefone deles é desconhecido, então o resultado é desconhecido e eles desaparecem. Para incluí-los, diga explicitamente: WHERE phone <> '555-0101' OR phone IS NULL. A negação também não ajuda:

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

-- Arjun

Sara e Ravi não têm bônus, então não aparecem nem em bonus > 400 nem no oposto. Isto é algo importante a lembrar: uma condição e sua negação nem sempre cobrem todas as linhas.

COALESCE: fornece um substituto

COALESCE retorna o primeiro valor não NULL em sua lista e funciona em todos os principais bancos de dados:

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 ainda aparece em branco porque uma string vazia não é NULL. Existem funções semelhantes, como IFNULL no MySQL e SQLite, ISNULL no SQL Server e NVL no Oracle. COALESCE é a escolha portátil. Nosso ponto anterior sobre cálculos também se aplica aqui. bonus + 100 dá NULL para qualquer pessoa sem bônus, então escreva COALESCE(bonus, 0) + 100 se tratar um bônus faltante como zero for correto para o seu negócio.

NULLIF: transforma um valor em NULL

NULLIF(a, b) retorna NULL se os dois valores forem iguais e, caso contrário, retorna a. Dois usos são especialmente úteis. Ele pode limpar valores de espaço reservado:

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

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

Essa segunda consulta é uma maneira prática de encontrar valores “ausentes ou em branco”. Também pode proteger contra a divisão por zero, o que gera um erro em muitos bancos de dados:

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

Quando quantity é 0, isso fornece NULL em vez de falhar. Em nosso teste, SELECT 100 / NULLIF(0, 0) retornou NULL.

Agregados ignoram NULL

É aqui que NULL causa mais danos aos relatórios:

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

-- 4 | 2 | 800 | 400.0 | 200.0

Então, qual é o bônus médio, 400 ou 200? Depende do que NULL significa nos seus dados. Se NULL significa “nenhum bônus foi pago”, então 200 está certo. Se significar “ainda não decidido”, então 400 pode estar certo. O banco de dados não pode tomar essa decisão por você, então a pergunta deve ir para alguém que conheça o negócio.

Uma armadilha relacionada: SUM sem valores é NULL em vez de 0. Quando somamos apenas as linhas sem bônus, obtemos NULL, enquanto COUNT retornou 0. O código do aplicativo que espera um número pode falhar nisso, então envolva-o: COALESCE(SUM(bonus), 0).

GROUP BY, classificação e DISTINCT

Embora NULLs “não sejam iguais” nas comparações, essas operações os tratam como um 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 e HAVING para obter mais informações sobre agrupamento. A ordem de classificação é onde os bancos de dados realmente diferem. SQLite e MySQL tratam NULL como o menor valor, enquanto PostgreSQL e Oracle o tratam como o maior. Então o mesmo ORDER BY bonus DESC pode colocar NULLs por último em um e primeiro em outro. PostgreSQL, Oracle e SQLite moderno permitem afirmar isso diretamente, usando NULLS FIRST ou NULLS LAST.

Junta-se

Uma condição de junção usando = nunca corresponde a NULL, então linhas com uma chave NULL são eliminadas de uma junção 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 não tem gerente, portanto, uma junção interna elimina silenciosamente essa linha. Um LEFT JOIN o mantém e também cria seus próprios NULLs para o lado ausente. Leia mais em Explicação sobre junções SQL.

A armadilha NÃO ESTÁ

Um único NULL em uma lista NOT IN elimina todos os 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)

A segunda forma significa manager_id <> 1 AND manager_id <> NULL, e a última parte nunca pode ser verdadeira. Isto é particularmente perigoso com subconsultas, onde um NULL pode chegar despercebido. Use NOT EXISTS, conforme explicado em nosso tutorial de subconsultas SQL. Meera também está faltando no primeiro resultado, já que manager_id dessa linha é NULL.

Projetando com NULL

• Use NOT NULL por padrão. Permita NULL somente quando um valor realmente estiver faltando.
• Adicione padrões razoáveis. Por exemplo, quantity INT NOT NULL DEFAULT 0.
• Não use valores mágicos. Uma data de 1900-01-01 ou uma idade de -1 substituindo “desconhecido” corromperá seus cálculos.
• Conheça o comportamento UNIQUE. A maioria dos bancos de dados permite vários NULLs em uma coluna UNIQUE, e nosso teste inseriu dois sem erro. O SQL Server é uma exceção, pois normalmente permite.
• Lembre-se de que uma chave primária não pode ser NULA. Consulte chave primária versus chave estrangeira.
• Teste com NULLs. Dados de teste sem NULLs ocultam esses bugs. Inclua-os propositalmente, como faria com outros casos extremos.

Considerações finais

Trate NULL como “desconhecido” e a maior parte de seu comportamento começa a fazer sentido: desconhecido mais um é desconhecido e desconhecido é igual a desconhecido não é conhecido como verdadeiro. Audite um de seus próprios relatórios esta semana. Verifique as colunas anuláveis ​​usando COUNT(*) em relação a COUNT(column), procure qualquer NOT IN e decida o que NULL deve significar em cada média.

Perguntas frequentes

Continue aprendendo