CASE: lógica direto no SELECT

Se — então

Um arquivista de verdade não só lê os dados: ele também os rotula.

No antigo depósito da «Kotomarket» ficam guardados fatos crus:

• o preço de um item;
• o status de um pedido;
• a quantidade em estoque;
• a cidade de um usuário;
• a data de um evento.

Mas quase sempre uma pessoa precisa de mais do que o fato: precisa de um rótulo compreensível.

Por exemplo, a tabela products tem o preço:

O preço por si só já é útil, mas num relatório às vezes você quer ver logo a faixa:

Quem faz esse tipo de rotulagem é o CASE.

O CASE é uma expressão com ramificações:

CASE
  WHEN condição1 THEN valor1
  WHEN condição2 THEN valor2
  ELSE valor_padrão
END

Lê-se quase como uma instrução comum:

se a condição 1 for verdadeira, devolva o valor 1;
senão, se a condição 2 for verdadeira, devolva o valor 2;
senão, devolva o valor padrão.

Um exemplo:

SELECT
    name,
    price,
    CASE
      WHEN price < 1500 THEN 'barato'
      WHEN price < 4000 THEN 'médio'
      ELSE 'caro'
    END AS segment
FROM products;

Aqui o CASE cria uma nova coluna calculada, segment.

Ele não altera os dados da tabela.
Ele só acrescenta a rotulagem ao resultado da consulta.

QUERY: Um rótulo é a opinião do arquivista escrita por cima dos fatos. Pendure etiquetas das quais você não vá se envergonhar daqui a cem anos.

O CASE verifica os ramos de cima para baixo

O detalhe mais importante do CASE:

vale o primeiro WHEN que encaixar.

Assim que o SQL encontra um ramo cuja condição resultou em TRUE, ele pega o valor que vem depois do THEN e não verifica mais nenhum ramo.

Veja este exemplo:

CASE
  WHEN price < 4000 THEN 'não caro'
  WHEN price < 1500 THEN 'barato'
  ELSE 'caro'
END

À primeira vista, parece que um produto de 900 deveria receber o rótulo 'barato'.

Mas não é o que acontece.

Por quê?

Para o preço 900, a primeira condição já é verdadeira:

price < 4000

Ou seja, o CASE já retorna:

não caro

e o ramo:

WHEN price < 1500 THEN 'barato'

nem chega a ser avaliado.

Por isso a ordem dos ramos não é decoração. Ela faz parte da lógica.

Para faixas de valores, o normal é ir da condição mais restrita para a mais ampla:

CASE
  WHEN price < 1500 THEN 'barato'
  WHEN price < 4000 THEN 'médio'
  ELSE 'caro'
END

Assim, o produto de 900 cai no primeiro ramo, e o de 2500, no segundo.

O que o ELSE faz

ELSE é o plano B.

Ele entra em ação quando nenhum WHEN encaixou.

Por exemplo:

CASE
  WHEN price < 1500 THEN 'barato'
  WHEN price < 4000 THEN 'médio'
  ELSE 'caro'
END

Se o preço for 7200, as duas primeiras condições não batem:

7200 < 1500  -- não
7200 < 4000  -- não

Então o valor retornado é o do ELSE:

caro

E o que acontece se você não escrever o ELSE?

CASE
  WHEN price < 1500 THEN 'barato'
  WHEN price < 4000 THEN 'médio'
END

Se o preço for 7200, nenhum WHEN dispara. E não há plano B.

Nessa situação, o CASE retorna NULL.

Ou seja:

se nenhum ramo encaixou e não há ELSE, o resultado é NULL.

Em relatórios, costuma ser melhor escrever um ELSE explícito, para o resultado ficar mais claro para as pessoas:

ELSE 'sem segmento'

ou:

ELSE 'caro'

A escolha depende da lógica de negócio.

CASE é uma expressão, não um comando à parte

O CASE retorna um valor.

Isso significa que você pode usá-lo onde o SQL espera um valor:

• no SELECT;
• no ORDER BY;
• às vezes no WHERE;
• dentro de cálculos;
• dentro de agregações, nos próximos módulos.

Nesta aula, o cenário principal é o SELECT.

SELECT
    name,
    price,
    CASE
      WHEN price < 1500 THEN 'barato'
      WHEN price < 4000 THEN 'médio'
      ELSE 'caro'
    END AS segment
FROM products;

Aqui o CASE funciona como uma coluna calculada.

Dá para pensar nele assim:

para cada linha, calcule um novo valor a partir das condições

Cada linha recebe o seu próprio resultado.

O produto de 900 fica com 'barato'.
O de 2500, com 'médio'.
O de 7200, com 'caro'.

O CASE não filtra linhas por conta própria. Ele não é como o WHERE.

O WHERE decide:

manter a linha ou descartar?

O CASE decide:

qual valor mostrar para esta linha?

CASE para rótulos de negócio

O CASE costuma ser usado para transformar valores técnicos em rótulos compreensíveis para as pessoas.

Por exemplo, na tabela orders existem estes status:

Para o banco, esses valores são convenientes: curtos, estáveis, em inglês.

Mas, em um relatório, dá para mostrar rótulos em português:

SELECT
    id,
    status,
    CASE
      WHEN status = 'paid' THEN 'pago'
      WHEN status = 'pending' THEN 'aguardando pagamento'
      WHEN status = 'cancelled' THEN 'cancelado'
      WHEN status = 'refunded' THEN 'reembolsado'
      ELSE 'status desconhecido'
    END AS status_label
FROM orders;

O resultado:

Essa consulta não muda o status na tabela.

Ela apenas acrescenta ao lado uma versão legível.

Isso é prático em relatórios, exportações, dashboards e exercícios do curso, onde o resultado precisa falar a língua das pessoas.

A forma de busca do CASE

A forma que usamos acima se chama forma de busca:

CASE
  WHEN condição1 THEN valor1
  WHEN condição2 THEN valor2
  ELSE valor_padrão
END

Ela é chamada de searched CASE.

Em cada ramo, depois do WHEN, dá para escrever uma condição completa:

WHEN price < 1500 THEN 'barato'

WHEN status = 'paid' THEN 'pago'

WHEN stock = 0 THEN 'sem estoque'

WHEN price < 1500 AND stock > 0 THEN 'barato e em estoque'

A forma de busca é flexível. Com ela dá para:

• comparar colunas diferentes;
• usar AND e OR;
• verificar NULL com IS NULL;
• montar faixas de valores;
• descrever regras de negócio complexas.

Por exemplo:

SELECT
    name,
    price,
    stock,
    CASE
      WHEN stock = 0 THEN 'sem estoque'
      WHEN price < 1500 AND stock > 0 THEN 'produto barato em estoque'
      WHEN price >= 1500 AND stock > 0 THEN 'produto em estoque'
      ELSE 'confira os dados'
    END AS product_note
FROM products;

Aqui cada ramo é uma condição independente.

Para quem está começando, a forma de busca costuma ser a mais fácil de entender, porque mostra a lógica de forma explícita.

A forma simples do CASE

O CASE tem uma segunda forma: a simples.

Ela é prática quando comparamos a mesma coluna com vários valores exatos.

Por exemplo, em vez de escrever assim:

CASE
  WHEN status = 'paid' THEN 'pago'
  WHEN status = 'pending' THEN 'aguardando'
  WHEN status = 'cancelled' THEN 'cancelado'
  WHEN status = 'refunded' THEN 'reembolsado'
  ELSE 'outro status'
END

dá para escrever de forma mais curta:

CASE status
  WHEN 'paid' THEN 'pago'
  WHEN 'pending' THEN 'aguardando'
  WHEN 'cancelled' THEN 'cancelado'
  WHEN 'refunded' THEN 'reembolsado'
  ELSE 'outro status'
END

Aqui a expressão status aparece uma única vez:

CASE status

E depois vêm os valores com os quais ela será comparada:

WHEN 'paid' THEN 'pago'
WHEN 'pending' THEN 'aguardando'

A forma simples se lê como uma tabela de correspondências:

Ela cai bem em mapeamentos simples:

se o valor for igual a este, mostre aquele rótulo.

Forma simples e forma de busca: qual é a diferença

Vamos colocar as duas formas lado a lado.

A forma de busca:

CASE
  WHEN status = 'paid' THEN 'pago'
  WHEN status = 'pending' THEN 'aguardando'
  ELSE 'outro status'
END

A forma simples:

CASE status
  WHEN 'paid' THEN 'pago'
  WHEN 'pending' THEN 'aguardando'
  ELSE 'outro status'
END

O resultado pode ser o mesmo.

Mas o jeito de escrever é diferente.

Na forma simples, o próprio SQL compara a expressão depois do CASE com os valores depois do WHEN:

status = 'paid'
status = 'pending'

Na forma de busca, é você quem escreve as condições por completo:

WHEN status = 'paid'
WHEN status = 'pending'

A forma simples é mais curta quando as condições são todas do mesmo tipo.

A forma de busca é mais flexível quando as condições são diferentes entre si.

Por exemplo, não dá para expressar direito esta lógica com a forma simples:

CASE
  WHEN status IS NULL THEN 'status não informado'
  WHEN status = 'paid' AND paid_at IS NOT NULL THEN 'pago'
  WHEN status = 'pending' THEN 'aguardando'
  ELSE 'confira o pedido'
END

Aqui há uma verificação de NULL, a checagem de outra coluna, paid_at, e uma condição composta com AND.

Para casos assim, escolha a forma de busca.

A regra prática:

Se você precisa comparar uma coluna com um conjunto de valores exatos, dá para usar o CASE simples.

Se você precisa de condições variadas, faixas, NULL, várias colunas ou AND/OR, use o CASE de busca.

Por que WHEN NULL não funciona no CASE simples

A forma simples tem uma armadilha importante.

Suponha que orders.status às vezes seja NULL.

Quem está começando pode escrever assim:

CASE status
  WHEN 'paid' THEN 'pago'
  WHEN NULL THEN 'status não informado'
  ELSE 'outro status'
END

Parece que o ramo:

WHEN NULL THEN 'status não informado'

deveria disparar quando status for NULL.

Mas ele nunca vai disparar.

Por quê?

A forma simples, CASE status WHEN ..., compara status com os valores dos ramos usando a igualdade comum, =.

Ou seja, o ramo WHEN NULL vira, na prática:

status = NULL

E, pela aula sobre NULL, você já sabe:

status = NULL

não dá TRUE.

Dá UNKNOWN.

E um ramo WHEN só dispara quando a condição dá TRUE.

Por isso, para verificar NULL, você precisa da forma de busca:

CASE
  WHEN status IS NULL THEN 'status não informado'
  WHEN status = 'paid' THEN 'pago'
  WHEN status = 'pending' THEN 'aguardando'
  ELSE 'outro status'
END

Aqui a verificação está escrita corretamente:

status IS NULL

A regra principal:

Para verificar NULL no CASE, use IS NULL — ou seja, a forma de busca.

Os valores de THEN e ELSE precisam ser compatíveis

O CASE retorna um único valor.

Mas ramos diferentes podem retornar versões diferentes desse valor.

Por exemplo:

CASE
  WHEN price < 1500 THEN 'barato'
  WHEN price < 4000 THEN 'médio'
  ELSE 'caro'
END

Todos os ramos retornam texto. Tudo certo.

'barato'
'médio'
'caro'

Já este CASE parece suspeito:

CASE
  WHEN price < 1500 THEN 'barato'
  ELSE 0
END

Um ramo retorna texto:

'barato'

O outro, um número:

0

O banco precisa decidir qual será o tipo do resultado do CASE: texto ou número. Dependendo da situação, isso pode causar um erro ou uma conversão de tipos nada óbvia.

Por isso, um bom hábito:

os ramos THEN e ELSE devem retornar valores de um mesmo tipo, bem definido.

Se você está criando um rótulo de texto, retorne texto em todos os ramos:

CASE
  WHEN price < 1500 THEN 'barato'
  ELSE 'não barato'
END

Se você está criando um ranking numérico, retorne números em todos os ramos:

CASE
  WHEN status = 'paid' THEN 1
  WHEN status = 'pending' THEN 2
  ELSE 3
END

Isso é especialmente importante no ORDER BY, onde o CASE costuma retornar uma prioridade numérica.

CASE no ORDER BY: ordenar pela lógica do negócio

Às vezes a ordenação comum não serve.

Por exemplo, considere os status dos pedidos:

paid
pending
cancelled
refunded

Se você ordená-los em ordem alfabética:

SELECT id, status
FROM orders
ORDER BY status;

o resultado é uma ordem técnica, baseada no texto.

Mas o negócio pode precisar de outra ordem:

1. primeiro os pagos;
2. depois os que aguardam pagamento;
3. depois os cancelados;
4. depois os reembolsos;
5. e então todo o resto.

O alfabeto não conhece essa lógica. Mas você pode defini-la com o CASE:

SELECT id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'paid' THEN 1
    WHEN 'pending' THEN 2
    WHEN 'cancelled' THEN 3
    WHEN 'refunded' THEN 4
    ELSE 5
  END,
  id;

Aqui o CASE retorna uma prioridade numérica:

O ORDER BY ordena por essa prioridade.

E o id no fim garante uma ordem estável dentro de um mesmo status:

ORDER BY CASE ... END, id

Assim, as linhas com o mesmo status ficam ordenadas por id, e não em uma ordem indefinida.

Dá para mostrar o rótulo e ordenar pela mesma lógica

Às vezes é útil fazer as duas coisas ao mesmo tempo:

• mostrar à pessoa um rótulo compreensível;
• ordenar as linhas na ordem do negócio.

Por exemplo:

SELECT
    id,
    status,
    CASE status
      WHEN 'paid' THEN 'pago'
      WHEN 'pending' THEN 'aguardando'
      WHEN 'cancelled' THEN 'cancelado'
      WHEN 'refunded' THEN 'reembolsado'
      ELSE 'outro status'
    END AS status_label
FROM orders
ORDER BY
    CASE status
      WHEN 'paid' THEN 1
      WHEN 'pending' THEN 2
      WHEN 'cancelled' THEN 3
      WHEN 'refunded' THEN 4
      ELSE 5
    END,
    id;

No SELECT, o CASE cria um rótulo de texto:

status_label

No ORDER BY, outro CASE cria uma prioridade numérica de ordenação.

Por que não ordenar pelo próprio rótulo em português?

Porque a ordem alfabética dos rótulos, de novo, pode não coincidir com a lógica do negócio.

Por exemplo, em ordem alfabética 'cancelado' vem antes de 'pago', mas o negócio precisa do contrário.

Por isso, use o texto para exibir e um ranking numérico para ordenar.

QUERY: O CASE não é só um “se — então”. É o jeito de explicar ao arquivo como uma pessoa deve ler os fatos crus.