SQL IFNULL e COALESCE

Três clientes da Loja Aurora não têm email cadastrado: no banco, o email deles é NULL. Quando a Ana imprime a lista de contatos, aparece um buraco no lugar. Ela preferia ler "sem email". Em vez de escrever um CASE inteiro só para isso, o SQL tem funções curtas que fazem exatamente esse trabalho: IFNULL ("se for nulo") e COALESCE ("juntar", "fundir"). As duas devolvem o valor da coluna quando ele existe e um valor reserva quando ele é NULL.

Você vai aprender

IFNULL(valor, reserva): a forma do SQLite e do MySQL.
COALESCE(a, b, c, …): a forma do SQL padrão, que aceita várias reservas.
Por que um NULL "contamina" contas e textos, e como evitar.
O caso clássico: SUM com LEFT JOIN que dá NULL em vez de 0.
NULLIF, o caminho de volta: transformar um valor em NULL.
Os nomes nos outros bancos: ISNULL (SQL Server) e NVL (Oracle), e os erros reais do SQLite.

Se o NULL ainda é novidade, leia antes O Valor NULL e IS NULL e IS NOT NULL. Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: o plano B

A Ana vai mandar um recado para cada cliente. A regra dela é: "mande para o email; se não tiver email, anote 'sem email' para eu ligar depois". É um plano A com um plano B. E às vezes há um plano C: "mande para o email; se não tiver, para o telefone; se não tiver nenhum dos dois, anote 'sem contato'".

Função	Quantos planos	Quer dizer
`IFNULL(a, b)`	2: o valor e uma reserva	"`a`, mas se `a` for `NULL`, `b`"
`COALESCE(a, b, c, …)`	Quantos quiser	"O primeiro da lista que não for `NULL`"

1. IFNULL: o valor ou a reserva

Primeiro, os emails como estão no banco, para cinco clientes:

SELECT nome, email
FROM clientes
WHERE id IN (5, 6, 7, 15, 23);

O Luís, o Roberto e o Henrique têm email NULL. Agora com IFNULL:

SELECT nome, IFNULL(email, 'sem email') AS email
FROM clientes
WHERE id IN (5, 6, 7, 15, 23);

IFNULL( … , … ): Uma função com exatamente dois argumentos, separados por vírgula.
email: O primeiro: o valor que você quer mostrar. Se ele não for NULL, é ele que sai.
'sem email': O segundo: a reserva, usada só quando o primeiro é NULL. Aqui é um texto entre aspas simples; pode ser um número, outra coluna ou uma conta.
AS email: O nome da coluna no resultado. Sem ele, o cabeçalho seria IFNULL(email, 'sem email').

Os emails que existem passaram do jeito que estavam; só os NULL viraram "sem email". O banco não mudou: a troca acontece só na resposta. Para mudar a tabela de verdade, seria um UPDATE.

Lembra da gerente sem chefe, na aula Self Join? O LEFT JOIN deixava o chefe dela NULL. Com IFNULL:

SELECT f.nome AS funcionario,
       IFNULL(c.nome, '(ninguém)') AS chefe
FROM funcionarios AS f
LEFT JOIN funcionarios AS c ON f.chefe_id = c.id;

Missão: na lista dos clientes de São Paulo, um deles está sem email. Troque a coluna email por um IFNULL que mostre 'sem email' no lugar do NULL (mantenha os emails que existem).

SELECT nome, email
FROM clientes
WHERE cidade = 'São Paulo';

SELECT nome, IFNULL(email, 'sem email') AS email
FROM clientes
WHERE cidade = 'São Paulo';

2. COALESCE: o primeiro que não for NULL

O COALESCE faz o mesmo que o IFNULL, mas aceita quantos argumentos você quiser (pelo menos dois). Ele olha um por um, da esquerda para a direita, e devolve o primeiro que não for NULL.

Os fornecedores estrangeiros não têm estado (o estado deles é NULL). Para uma coluna "região" que mostre o estado dos brasileiros e o país dos outros:

SELECT nome, cidade, estado, pais,
       COALESCE(estado, pais) AS regiao
FROM fornecedores;

Para os brasileiros, estado existe e é ele que vem. Para a Lusitana e a Pampa Sur, estado é NULL, então o COALESCE passa para o próximo, pais.

Com três ou mais opções, a lista de planos fica explícita:

SELECT COALESCE(NULL, NULL, 'terceiro', 'quarto') AS resultado;

"terceiro": o primeiro valor não nulo da lista. O "quarto" nem chega a ser examinado. E se todos forem NULL, o resultado também é NULL:

SELECT COALESCE(NULL, NULL) AS resultado;

Mais um uso do dia a dia, com datas: os pedidos ainda não entregues mostram "a caminho" no lugar da data de entrega:

SELECT id, data, COALESCE(entregue_em, 'a caminho') AS entrega
FROM pedidos
WHERE id >= 84;

3. IFNULL × COALESCE: qual usar

Com dois argumentos, os dois são a mesma coisa. A documentação do SQLite diz com todas as letras: ifnull() "é equivalente a coalesce() com dois argumentos". A diferença está em onde cada um funciona:

Função	Argumentos	SQLite	MySQL	PostgreSQL	SQL Server	Oracle
`COALESCE(a, b, …)`	2 ou mais	sim	sim	sim	sim	sim
`IFNULL(a, b)`	exatamente 2	sim	sim	não	não	não
`ISNULL(a, b)`	exatamente 2	não	outra função (veja abaixo)	não	sim	não
`NVL(a, b)`	exatamente 2	não	não	não	não	sim

Regra prática: use COALESCE. Ele é do SQL padrão, funciona em todos esses bancos e aceita mais de uma reserva. O IFNULL é ótimo de ler e você vai vê-lo muito em código de SQLite e MySQL.

E o CASE? Ele faz o mesmo, só que mais comprido. As duas consultas abaixo dão o mesmo resultado:

SELECT nome,
       CASE WHEN email IS NULL THEN 'sem email' ELSE email END AS email
FROM clientes
WHERE id IN (6, 7);

SELECT nome, COALESCE(email, 'sem email') AS email
FROM clientes
WHERE id IN (6, 7);

Para "troque NULL por outro valor", use COALESCE/IFNULL. Para qualquer outra decisão, CASE.

4. O NULL que contamina

Quase toda conta ou junção de textos com um NULL dá NULL. É como uma receita com um ingrediente "desconhecido": o resultado também fica desconhecido. A Ana monta um contato juntando o email e a cidade com o operador || (que cola textos):

SELECT nome, email || ' (' || cidade || ')' AS contato
FROM clientes
WHERE id IN (6, 7);

O Luís sumiu inteiro: como o email dele é NULL, o texto todo virou NULL, e a cidade, que existe, foi junto. Protegendo o pedaço que pode faltar:

SELECT nome, IFNULL(email, 'sem email') || ' (' || cidade || ')' AS contato
FROM clientes
WHERE id IN (6, 7);

O caso clássico: SUM com LEFT JOIN

A Ana quer quanto cada cliente já gastou em produtos, incluindo quem nunca comprou. O LEFT JOIN mantém todos os clientes, mas quem não tem pedido não tem item nenhum para somar:

SELECT c.nome, SUM(i.quantidade * i.preco_unitario) AS total_gasto
FROM clientes AS c
LEFT JOIN pedidos AS pe ON pe.cliente_id = c.id
LEFT JOIN itens_pedido AS i ON i.pedido_id = pe.id
WHERE c.id IN (1, 2, 27, 28)
GROUP BY c.id, c.nome;

O Paulo e a Tainá aparecem com NULL: o SUM de "nada" é NULL, e não 0. Num relatório, a Ana quer ler 0. O COALESCE vai em volta do SUM:

SELECT c.nome, COALESCE(SUM(i.quantidade * i.preco_unitario), 0) AS total_gasto
FROM clientes AS c
LEFT JOIN pedidos AS pe ON pe.cliente_id = c.id
LEFT JOIN itens_pedido AS i ON i.pedido_id = pe.id
WHERE c.id IN (1, 2, 27, 28)
GROUP BY c.id, c.nome;

Por que em volta, e não dentro?

Os valores que faltam aqui não são NULL dentro de uma coluna: é a soma inteira que não tem o que somar. Por isso o COALESCE vai fora: "a soma, ou 0 se a soma for NULL". O COUNT não precisa disso: contar "nada" já dá 0.

5. NULLIF: o caminho de volta

O NULLIF(a, b) faz o contrário: devolve NULL se a for igual a b, e o próprio a se forem diferentes.

SELECT NULLIF(5, 5) AS iguais,
       NULLIF(5, 3) AS diferentes,
       NULLIF('', '') AS texto_vazio;

Para que serve "fabricar" um NULL? Para aproveitar o fato de que as funções agregadas ignoram NULL. Na Loja Aurora, alguns pedidos tiveram frete grátis (frete 0.0). A Ana quer duas médias: a de todos os pedidos e a só dos pedidos que pagaram frete:

SELECT id, frete, NULLIF(frete, 0) AS frete_pago
FROM pedidos
WHERE id BETWEEN 1 AND 5;

O pedido 4, de frete grátis, ficou com frete_pago NULL. Agora, com AVG e COUNT:

SELECT ROUND(AVG(frete), 2) AS media_todos,
       ROUND(AVG(NULLIF(frete, 0)), 2) AS media_pagos,
       COUNT(*) AS pedidos,
       COUNT(NULLIF(frete, 0)) AS com_frete
FROM pedidos;

A média geral é 14.49; a de quem pagou frete, 16.51. Dos 90 pedidos, 79 pagaram frete. Os fretes zerados viraram NULL e saíram das contas.

NULLIF e a divisão por zero

No SQLite, dividir por zero não dá erro: dá NULL.

SELECT 10 / 0 AS divisao;

O MySQL também (o próprio manual dele mostra IFNULL(1/0, 10) devolvendo 10). Mas há bancos em que a divisão por zero para a consulta com erro. Por isso, você vai ver muito valor / NULLIF(divisor, 0) em código SQL: se o divisor for 0, ele vira NULL, e a conta dá NULL em vez de erro, em qualquer banco.

Quando dá errado

Argumentos demais no IFNULL

SELECT nome, IFNULL(email, 'sem', 'email')
FROM clientes;

A mensagem: wrong number of arguments to function IFNULL(), "número errado de argumentos para a função IFNULL()". O IFNULL aceita exatamente dois.

Como consertar: para mais de uma reserva, use COALESCE. Aqui, provavelmente a intenção era um texto só: IFNULL(email, 'sem email').

COALESCE com um argumento só

SELECT COALESCE(email)
FROM clientes;

A mensagem: wrong number of arguments to function COALESCE(). A documentação do SQLite exige pelo menos dois argumentos: sem uma reserva, não há o que fazer.

Como consertar: diga qual é a reserva: COALESCE(email, 'sem email').

Usou o nome de outro banco: ISNULL e NVL

A Ana copiou um exemplo de SQL Server:

SELECT nome, ISNULL(email, 'sem email')
FROM clientes;

A mensagem: near "ISNULL": syntax error. No SQLite, ISNULL não é função: é um operador que vem depois do valor (email ISNULL, o mesmo que email IS NULL), e responde 1 ou 0. Por isso o erro é de sintaxe. E um exemplo de Oracle:

SELECT nome, NVL(email, 'sem email')
FROM clientes;

A mensagem: no such function: NVL, "não existe a função NVL".

Como consertar: nos dois casos, COALESCE(email, 'sem email'), que funciona em todos os bancos.

Reserva de outro tipo

SELECT nome, IFNULL(nascimento, 0) AS nascimento
FROM clientes
WHERE id IN (8, 9);

O que acontece: o SQLite aceita, e a coluna fica com uma data numa linha e o número 0 na outra. Para quem lê, "0" não quer dizer nada; e os outros bancos podem recusar ou converter os valores (o manual do SQL Server, por exemplo, diz que o ISNULL converte a reserva para o tipo do primeiro argumento).

Como consertar: use uma reserva do mesmo tipo e com sentido: IFNULL(nascimento, 'não informado').

Procurou os NULL com IFNULL no lugar de IS NULL

Para achar quem não tem email, a função de troca não é o caminho mais claro:

SELECT COUNT(*) AS sem_email
FROM clientes
WHERE IFNULL(email, '') = '';

O que acontece: funciona (3 clientes), mas mistura duas coisas: quem tem email NULL e quem tivesse um email vazio (''). E o banco tem de calcular o IFNULL em todas as linhas antes de comparar.

Como consertar: para filtrar, use WHERE email IS NULL (aula IS NULL). O manual do SQL Server dá o mesmo conselho para o ISNULL dele: para encontrar NULL, use IS NULL. Deixe IFNULL/COALESCE para mostrar o resultado.

Nos outros bancos

PostgreSQL: tem COALESCE e NULLIF, mas não IFNULL. O manual diz que o COALESCE, do SQL padrão, "oferece o que NVL e IFNULL oferecem em outros sistemas".
MySQL: tem IFNULL(a, b), COALESCE e NULLIF, como o SQLite. Cuidado: o ISNULL(a) do MySQL tem um argumento e só responde 1 (é NULL) ou 0 (não é); ele não troca nada.
SQL Server: ISNULL(a, b) "substitui NULL pelo valor indicado", e também tem COALESCE e NULLIF.
Oracle: NVL(a, b): "se a for nulo, devolve b; senão, devolve a". Também tem COALESCE.

Trechos ilustrativos (não rodam no SQLite):

SELECT nome, ISNULL(email, 'sem email') AS email FROM clientes;

SELECT nome, NVL(email, 'sem email') AS email FROM clientes;

Referência rápida

Escrita	O que faz
`IFNULL(a, b)`	`a`, ou `b` se `a` for `NULL` (exatamente 2 argumentos)
`COALESCE(a, b, c, …)`	O primeiro argumento que não for `NULL`; `NULL` se todos forem (2 ou mais)
`COALESCE(SUM(x), 0)`	Soma que mostra 0 em vez de `NULL` quando não há o que somar
`IFNULL(texto, '') \|\| …`	Evita que um `NULL` apague o texto inteiro
`NULLIF(a, b)`	`NULL` se `a = b`; senão, `a`
`x / NULLIF(y, 0)`	Divisão que dá `NULL` em vez de erro quando `y` é 0
`ISNULL(a, b)` / `NVL(a, b)`	O mesmo que `IFNULL`, no SQL Server / no Oracle (não existem no SQLite)

Mensagem do SQLite	Quer dizer
`wrong number of arguments to function IFNULL()`	`IFNULL` com mais ou menos de 2 argumentos
`wrong number of arguments to function COALESCE()`	`COALESCE` com menos de 2 argumentos
`near "ISNULL": syntax error`	`ISNULL(a, b)` é do SQL Server; use `COALESCE`
`no such function: NVL`	`NVL` é do Oracle; use `COALESCE`

Exercícios

1. A reserva

Complete para mostrar 'sem email' quando o email for NULL:

SELECT nome, (email, 'sem email') FROM clientes;

2. Zero em vez de NULL

Complete para a soma mostrar 0 quando não houver itens:

COALESCE(SUM(i.quantidade * i.preco_unitario), ) AS total_gasto

3. Quanto vale COALESCE(NULL, 'Teresina', 'Fortaleza')?

NULL 'Teresina' 'Fortaleza' 'Teresina, Fortaleza'

4. Qual destas funções é do SQL padrão e funciona no SQLite, no MySQL, no PostgreSQL, no SQL Server e no Oracle?

IFNULL ISNULL NVL COALESCE

5. Por que email || ' (' || cidade || ')' sai NULL para quem não tem email?

Porque juntar um texto com NULL dá NULL; é preciso proteger o email com IFNULL Porque o || não funciona com a cidade Porque faltou o AS Porque o SQLite não junta textos

6. Quanto vale NULLIF(frete, 0) num pedido de frete grátis (frete 0.0)?

0 0.0 NULL Dá erro de divisão por zero

Missão final: o relatório de Fortaleza

Missão: o relatório mostra quanto cada cliente de Fortaleza já gastou em produtos. A Rosângela, que nunca comprou, aparece com NULL. Faça a coluna total_gasto mostrar 0 para ela, sem mudar o valor dos outros.

SELECT c.nome, SUM(i.quantidade * i.preco_unitario) AS total_gasto
FROM clientes AS c
LEFT JOIN pedidos AS pe ON pe.cliente_id = c.id
LEFT JOIN itens_pedido AS i ON i.pedido_id = pe.id
WHERE c.cidade = 'Fortaleza'
GROUP BY c.id, c.nome;

SELECT c.nome, COALESCE(SUM(i.quantidade * i.preco_unitario), 0) AS total_gasto
FROM clientes AS c
LEFT JOIN pedidos AS pe ON pe.cliente_id = c.id
LEFT JOIN itens_pedido AS i ON i.pedido_id = pe.id
WHERE c.cidade = 'Fortaleza'
GROUP BY c.id, c.nome;

Livre: a lista dos clientes com algum dado faltando. Experimente trocar os IFNULL por COALESCE, ou montar uma coluna "contato" com COALESCE(email, cidade).

SELECT nome,
       IFNULL(nascimento, 'não informado') AS nascimento,
       IFNULL(email, 'sem email') AS email
FROM clientes
WHERE nascimento IS NULL OR email IS NULL;

Resumo

IFNULL(a, b) devolve a, ou b se a for NULL. Exatamente 2 argumentos. Existe no SQLite e no MySQL.
COALESCE(a, b, c, …) devolve o primeiro argumento que não for NULL. É do SQL padrão e funciona em todos os bancos: na dúvida, use ele.
A troca acontece só na resposta; a tabela continua com NULL.
Um NULL contamina contas e textos (||); proteja a parte que pode faltar. Em volta de um SUM com LEFT JOIN, COALESCE(SUM(…), 0) mostra 0.
NULLIF(a, b) faz o caminho de volta: vira NULL quando a = b. Útil para tirar valores das médias e para evitar divisão por zero.
Nos outros bancos: ISNULL(a, b) no SQL Server, NVL(a, b) no Oracle. No MySQL, ISNULL(a) é outra coisa (responde 1 ou 0). Para filtrar, use sempre IS NULL.