COALESCE retorna o primeiro valor da lista que não é NULL. É assim que o SQL troca um valor ausente por um valor alternativo. Os exemplos funcionam no PostgreSQL, MySQL, SQLite e SQL Server.
Como usar COALESCE
Passe para o COALESCE quantos valores quiser. Ele os verifica da esquerda para a direita e retorna o primeiro que não é NULL, ou NULL se todos forem:
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
Em uma query real, os valores costumam ser colunas, com um literal no final como último recurso:
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
Regras importantes
- Ele para no primeiro valor não NULL, então os argumentos seguintes não são avaliados depois que um é encontrado. Coloque primeiro o valor que mais costuma estar preenchido.
- Todos os argumentos precisam ter tipos compatíveis. Misturar número com texto falha no PostgreSQL e no SQL Server, então faça o cast antes:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
Uma string vazia não é NULL. O COALESCE deixa
''passar direto. Envolva a coluna emNULLIF(column, '')para tratar strings vazias também como ausentes:
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
Sintaxe alternativa
Você também vai encontrar atalhos de dois argumentos: IFNULL no MySQL e no SQLite, e ISNULL no SQL Server:
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
Prefira COALESCE. Ele aceita qualquer número de argumentos, e o ISNULL do SQL Server dá ao resultado o tipo do primeiro argumento, o que pode cortar seu valor alternativo:
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
Uma expressão CASE faz o mesmo trabalho, só que de forma mais longa: COALESCE(nickname, name) é uma abreviação de CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END. Veja o guia da instrução CASE no SQL para quando você precisar de mais poder.
Quando usar COALESCE?
Use COALESCE sempre que um NULL deixaria seu resultado errado, feio ou vazio. A maioria dos usos se encaixa em um destes quatro casos.
Recorrer a outra coluna
Quando o valor que você quer está em uma de várias colunas, o COALESCE escolhe a primeira preenchida: apelido ou nome completo, celular ou fixo, endereço de entrega ou de cobrança.
Usar um padrão quando todos os valores faltam
Coloque um literal no final da lista e o resultado nunca será NULL. É o que você quer em relatórios e telas, onde “Endereço desconhecido” ou 0.00 fica melhor que um espaço em branco.
Preencher lacunas com um valor calculado
O valor alternativo não precisa ser uma coluna ou constante. Pode ser uma expressão, como um nome de usuário ou e-mail montado a partir de outras colunas, o que torna o COALESCE útil para limpar dados importados.
Manter NULLs fora dos cálculos
Qualquer operação aritmética com NULL resulta em NULL, então um desconto ou bônus ausente anula o total inteiro. COALESCE(discount, 0) transforma o valor ausente em um número que o cálculo pode usar.
Exemplos reais de COALESCE
Mostrar um preço, seja qual for o cadastrado
Produtos podem ter preço promocional, preço normal ou (antes do lançamento) nenhum dos dois. Verifique-os em ordem e use zero como padrão:
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
Escolher o melhor telefone para ligar
Os contatos têm até três números. Mostre o primeiro disponível e avise quando não houver nenhum:
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
Gerar um e-mail provisório
Funcionários sem e-mail cadastrado recebem um montado a partir do nome, para que toda linha do diretório tenha um valor:
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
No MySQL e no SQL Server, junte as strings com CONCAT() em vez de ||.
Somar um pedido quando descontos são opcionais
Sem COALESCE, uma linha sem desconto faz o total do pedido sair errado, porque o SUM ignora completamente as linhas com NULL:
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
Leitura adicional sobre COALESCE
Neste blog:
-
MySQL COALESCE: COALESCE junto com o
IFNULLdo MySQL - Guia da instrução CASE no SQL: lógica condicional além de verificações de NULL
- Guia do operador diferente no SQL: por que comparações com NULL nunca dão match
- Guia de GROUP BY no SQL: as agregações que o COALESCE costuma alimentar
- Guia de SUBSTRING no SQL: extraindo texto de colunas que podem ser NULL
Documentação oficial:
Beekeeper Studio é um cliente de banco de dados gratuito e open source
A melhor ferramenta de consultas e edição de SQL que já usei. Tem tudo o que preciso para gerenciar meu banco de dados. - ⭐⭐⭐⭐⭐ Mit
O Beekeeper Studio é rápido, intuitivo e fácil de usar. Ele suporta vários bancos de dados e funciona muito bem no Windows, Mac e Linux.
O que os usuários dizem sobre o Beekeeper Studio
"O Beekeeper Studio substituiu completamente meu antigo fluxo de trabalho com SQL. É rápido, intuitivo e torna o trabalho com banco de dados agradável de novo."
"Já testei muitas GUIs de banco de dados, mas o Beekeeper encontra o equilíbrio perfeito entre recursos e simplicidade. Simplesmente funciona."