Lição 2.4 · Tempo de leitura: ~6 min

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, em que return_date é NULL para os filmes que ainda não foram devolvidos.

SQL IS NULL e IS NOT NULL

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 COUNT(*) AS not_returned FROM rental WHERE return_date = NULL;

Resultado: 0, 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 COUNT(*) AS not_returned FROM rental WHERE return_date IS NULL;

Resultado: 183.

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE return_date IS NULL
ORDER BY rental_date
LIMIT 3;

Resultado: aluguéis 14098 (cliente 554), 13421 (cliente 366) e 15542 (cliente 111) — os filmes mais antigos que ainda não voltaram.

IS NOT NULL

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

SELECT COUNT(*) AS returned FROM rental WHERE return_date IS NOT NULL;

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

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:

SELECT
    (SELECT COUNT(*) FROM rental WHERE return_date >= '2005-08-01') AS after_august,
    (SELECT COUNT(*) FROM rental WHERE NOT (return_date >= '2005-08-01')) AS before_august,
    (SELECT COUNT(*) FROM rental) AS total;

Resultado: 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 COUNT(*) AS late_or_unknown
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Resultado: 8390 = 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 COUNT(*) AS films FROM film WHERE language_id NOT IN (2, 3);

Resultado: 1000.

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, NULL);

Resultado: 0 — language_id <> NULL é UNKNOWN para todos os filmes.

Ninguém escreve NULL numa lista à mão, mas a lista muitas vezes vem 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') AS returned_at
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21'
ORDER BY rental_date;

Resultado: 4 aluguéis; o primeiro, 14098, mostra "not returned".

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.
• 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.

COUNT(column) conta valores NULL?

Não. COUNT(*) conta linhas, COUNT(column) conta apenas os valores da coluna que não são NULL. As funções de agregação têm o seu próprio módulo.

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.