SQL IS NULL e IS NOT NULL

Lição 2.4 · Tempo de leitura: ~6 min · Tarefas para praticar ↓

IS NULL e IS NOT NULL são a única forma confiável de filtrar valores ausentes em SQL. Nesta lição você verá por que WHERE column = NULL não encontra nada, como encontrar linhas com e sem valor e como o NULL tira linhas silenciosamente de condições com NOT, <> e NOT IN. Os exemplos usam a tabela rental do banco Sakila: ela tem 16044 linhas, e return_date é NULL para os filmes que ainda não foram devolvidos.

Por que = NULL não encontra nada

NULL significa "o valor é desconhecido". Qualquer comparação com um valor desconhecido também é desconhecida: return_date = NULL não é verdadeiro nem falso, é UNKNOWN. O WHERE mantém apenas as linhas em que a condição é TRUE, então a consulta não retorna nada:

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date = NULL;

Resultado: nenhuma linha, embora 183 aluguéis não tenham data de devolução.

O mesmo acontece com <>, >, < e qualquer outro operador de comparação.

IS NULL

IS NULL é uma verificação especial que é TRUE quando o valor está ausente:

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NULL;

Resultado: 183 linhas - os aluguéis cujos filmes ainda não voltaram.

IS NULL se combina com outras condições como qualquer comparação:

SELECT rental_id, rental_date, return_date
FROM rental
WHERE customer_id = 554 AND return_date IS NULL;

Resultado: uma linha, o aluguel 14098 de 2005-08-21: o cliente 554 ainda está com esse filme.

IS NOT NULL

IS NOT NULL é o contrário: TRUE quando a coluna tem um valor.

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NOT NULL;

Resultado: 15861 linhas. Junto com os 183 aluguéis sem data de devolução, são todas as 16044 linhas da tabela.

O NULL fica fora da condição e do seu NOT

Parece natural que uma condição e o seu NOT dividam a tabela em duas partes. Com NULL, não dividem. Compare duas consultas:

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01';

Resultado: 8207 linhas.

SELECT rental_id, return_date
FROM rental
WHERE NOT (return_date >= '2005-08-01');

Resultado: 7654 linhas. 8207 + 7654 = 15861, não 16044: para as 183 linhas sem data de devolução as duas condições são UNKNOWN, e NOT UNKNOWN continua sendo UNKNOWN.

Para manter essas linhas, peça por elas explicitamente com OR ... IS NULL:

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Resultado: 8390 linhas = 8207 + 183.

A armadilha do NOT IN

NOT IN é uma sequência de comparações <> unidas por AND. Se a lista contém NULL, uma delas é sempre UNKNOWN, e nenhuma linha passa:

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, 3);

Resultado: 1000 linhas - todos os filmes.

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, NULL);

Resultado: nenhuma linha - language_id <> NULL é UNKNOWN para todos os filmes.

Ninguém escreve NULL numa lista à mão, mas mais adiante no curso a lista virá de uma coluna, e um único NULL ali basta para esvaziar o resultado. Antes de usar NOT IN, garanta que os valores não podem ser NULL.

Mostrando um valor no lugar do NULL

COALESCE retorna o primeiro dos seus argumentos que não é NULL. É útil para mostrar um valor legível no resultado:

SELECT rental_id, rental_date, COALESCE(return_date, 'not returned')
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21';

Resultado: 4 aluguéis; o aluguel 14098 mostra "not returned". Sem um nome, a coluna recebe como título a própria expressão.

Isto é MySQL. No PostgreSQL e no SQL Server os dois argumentos precisam ser do mesmo tipo, então lá a data é convertida em texto antes. COALESCE e outras funções são vistas no módulo de funções.

Principais conclusões:

• Qualquer comparação com NULL (=, <>, >, <) resulta em UNKNOWN, e o WHERE descarta essas linhas.
• Use IS NULL para encontrar linhas sem valor e IS NOT NULL para encontrar linhas com valor; as duas se combinam com AND e OR.
• Uma condição e o seu NOT juntos não cobrem as linhas em que a coluna é NULL; adicione OR column IS NULL para mantê-las.
• NOT IN não retorna nenhuma linha se a lista contém NULL.
• COALESCE(column, value) mostra um valor substituto no lugar do NULL.

Perguntas frequentes

Por que WHERE column = NULL não retorna linhas?

Uma comparação com NULL é UNKNOWN, não TRUE, e o WHERE mantém apenas as linhas TRUE. Use WHERE column IS NULL.

IS NOT NULL é o mesmo que NOT (column = NULL)?

Não. column = NULL é UNKNOWN, e NOT UNKNOWN também é UNKNOWN, então NOT (column = NULL) também não encontra nada. IS NOT NULL é TRUE para toda linha que tem valor.

Dá para combinar IS NULL com AND e OR?

Sim, como qualquer outra condição: WHERE customer_id = 554 AND return_date IS NULL encontra os filmes que ainda estão com um cliente, e OR return_date IS NULL acrescenta a outra condição as linhas sem valor.

Perguntas de entrevista

Por que as consultas SQL usam IS NULL em vez de = NULL?

NULL significa um valor desconhecido, e comparar qualquer coisa com um valor desconhecido resulta em UNKNOWN, não em TRUE ou FALSE. O WHERE mantém apenas as linhas em que a condição é TRUE, então = NULL nunca encontra nada. IS NULL é um teste separado que retorna TRUE ou FALSE.

Por que NOT IN pode retornar um resultado vazio?

x NOT IN (a, b) significa x <> a AND x <> b. Se a lista contém NULL, x <> NULL é UNKNOWN para todas as linhas, o AND inteiro não pode ser TRUE e nenhuma linha é retornada. Isso acontece quando a lista vem de uma subconsulta cuja coluna tem valores NULL.

Como manter as linhas com NULL num filtro?

Adicione o caso do NULL explicitamente: WHERE return_date >= '2005-08-01' OR return_date IS NULL. Caso contrário, as linhas com NULL são descartadas tanto pela condição quanto pela sua negação.