Como usar a função COALESCE() em SQL

Os usuários SQL são freqüentemente confrontados com valores NULL em suas consultas e precisam processá-los corretamente. A função COALESCE() ajuda a lidar com os valores NULL. Leia este artigo para aprender como usar COALESCE() em suas consultas.

As tabelas SQL armazenam dados em registros, e os registros são compostos de campos. Pode haver situações em que não sabemos o valor para um campo específico. Por exemplo, vamos supor que temos uma tabela com dados para persons. Tem os campos first_name, last_name, e marital_status. Quando não conhecemos o marital_status para uma determinada pessoa, SQL nos permite atribuir um valor NULL para este campo. Entretanto, o valor NULL não significa que a pessoa não tem um marital_status; significa apenas "não conhecemos esse valor".

Em outras palavras, SQL usa valores NULL para representar a ausência de valor. Entretanto, os valores NULL podem ser complicados de manusear. É por isso que SQL incluiu a função COALESCE(), que é o que vamos falar neste artigo.

Antes de começar os detalhes técnicos do NULL e COALESCE(), sugiro verificar nosso curso interativo em Funções Comuns em SQL. Ele contém uma revisão abrangente dos valores NULL, funções relacionadas ao NULL, e outras funções comuns em SQL.

O que faz a COALESCE()?

Em bancos de dados SQL, qualquer tipo de dado admite NULL como um valor válido; ou seja, qualquer coluna pode ter um valor NULL, independentemente do tipo de dado que seja. (Obviamente, algumas colunas serão obrigatórias (não-nuláveis), mas isto é definido pelo projetista do banco de dados, não o tipo de dados em si). Vamos mostrar um exemplo simples usando a tabela persons.

Podemos usar a função SQL COALESCE() para substituir o valor NULL por um texto simples:

SELECT 	
  first_name, 
  last_name, 
  COALESCE(marital_status,'Unknown')
FROM persons

Na consulta acima, a função COALESCE() é utilizada para retornar o valor 'Unknown' somente quando marital_status é NULL. Quando marital_status não é NULL, COALESCE() retorna o valor da coluna marital_status. Em outras palavras, COALESCE() retorna o primeiro argumento não NULL.

Conheça os dados do exemplo

Para o restante do artigo, demonstraremos a função COALESCE() utilizando a tabela stockque é mostrado abaixo.

Esta tabela armazena registros de dados do produto para um mercado e inclui as colunas product, brand, subcategory, category, family, units, quantity_available (o estoque atual deste produto), e minimum_to_have (o limiar quando o mercado precisa encomendar este produto de seus fornecedores).

Você notará que alguns dos produtos têm uma subcategoria, mas outros não. Por exemplo, o produto "costelas de porco" pertence à subcategoria "carne de porco" na categoria "carne" e a família "alimento". O produto "tomate" pertence à categoria "vegetais" e a família "alimento"; não tem uma subcategoria, portanto existe um NULL neste campo.

Exemplo de Consultas SQL usando a função COALESCE()

Agora, vamos ver como usar a função COALESCE() em alguns exemplos realistas.

Exemplo 1: Use COALESCE() para Substituir NULL por um Rótulo

Queremos mostrar todos os produtos com sua subcategoria, categoria e família. Entretanto, há alguns produtos com um NULL em sua categoria ou subcategoria. Para estes produtos, queremos exibir um texto: 'No Category' ou 'No Subcategory'. Aqui está a consulta que usaríamos:

SELECT product, 
  COALESCE(subcategory,'No Subcategory') AS subcategory,
  COALESCE(category,'No Category') AS category,
  COALESCE(family,'No Family') AS family
FROM stock

Estamos usando a função COALESCE() para substituir os valores NULL por um texto. Você pode ver o resultado abaixo:

Exemplo 2: Use COALESCE() ao concatenar NULL e Strings

Um problema SQL frequente relacionado com os valores NULL é a concatenação de cordas. Muitas operações envolvendo valores NULL retornam um valor NULL como resultado. Se quisermos concatenar duas cordas e uma delas for NULL, o resultado da concatenação será NULL. Aqui está uma simples concatenação de texto:

SELECT 'Hello, how are you ' || 'Peter ' || '?'  AS example

Ele retorna:

Entretanto, se usarmos um texto NULL ...

SELECT 'Hello, how are you ' || null || '?'  AS example

... nós conseguimos:

O resultado é NULL porque cada concatenação de texto envolvendo um valor NULL retorna um valor NULL. Para evitar isso, podemos usar a função COALESCE() para retornar uma string vazia (ou um espaço) em vez de um valor NULL. Por exemplo, suponha que queremos uma lista dos nomes dos produtos com o nome da marca. Podemos escrever a seguinte consulta:

SELECT 
    product || ', brand: ' || COALESCE(brand, '--') AS product_brand
FROM stock

Quando uma marca é NULL, colocaremos um '--' em vez de um NULL. Observe o resultado:

Exemplo 3: Use COALESCE() com Argumentos Múltiplos

Você pode usar a função COALESCE() com mais de dois argumentos. Suponhamos que queremos um relatório que liste os produtos e suas subcategorias. Se a subcategoria for NULL, queremos substituir a subcategoria pela categoria. E se ambas subcategoria e categoria são NULL, queremos substituí-las pela família do produto. Vamos ver a consulta SQL:

SELECT 
   product ||' - '||
   COALESCE(subcategory, category, family, 'no product description ')
   AS product_and_subcategory
FROM stock

Estamos usando a função COALESCE() com quatro argumentos; o primeiro argumento não NULL será retornado, como podemos ver no resultado abaixo:

Para mais detalhes sobre isto, sugiro o artigo How to Tackle SQL NULLs.

Exemplo 4: Use COALESCE() para substituir NULL por um Valor Calculado

A função SQL COALESCE() também pode ser usada para calcular ou estimar um valor quando este valor não está presente. Por exemplo, cada produto tem um limite (representado pela coluna minimum_to_have) que requer um novo pedido ao fornecedor. Entretanto, alguns registros poderiam ter um valor NULL na coluna minimum_to_have; neste caso, podemos definir que o limite será 50% da coluna quantity_available. A consulta para calcular a estimativa do valor limite é:

SELECT product,
  quantity_available,
  minimum_to_have,
  COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold
FROM   stock

A função COALESCE() aqui retorna minimum_to_have quando o valor minimum_to_have não é NULL. Se minimum_to_have for NULL, então COALESCE() retornará quantity_available * 0.5

Exemplo 5: Use COALESCE() com a Cláusula ROLLUP

No próximo exemplo, usaremos a cláusula ROLLUP (uma extensão de GROUP BY) para obter a quantidade total de produtos que temos para cada subcategoria, incluindo um subtotal de produtos para cada categoria e família. Vamos ver a consulta:

SELECT family,
 category,
 subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

A cláusula ROLLUP assume uma hierarquia entre as colunas family, category, e subcategory. Assim, ela gera todos os conjuntos de agrupamentos que fazem sentido considerando a hierarquia: GROUP BY family, GROUP BY family, category e GROUP BY family, category, subcategory. Esta é a razão pela qual ROLLUP é freqüentemente utilizada para gerar subtotais e totais gerais para relatórios.

Vamos ver os resultados abaixo:

Você pode ver alguns NULLs no resultado anterior. Cada NULL significa que esta coluna não estava presente no GROUP BY para o cálculo da quantidade em estoque. Por exemplo, estas linhas ...

... são o resultado da execução de GROUP BY family, category. Esta é a razão para ter um NULL sob a coluna subcategory.

Na próxima consulta, usaremos a função COALESCE() para melhorar a legibilidade do relatório. Substituiremos esses valores NULL por um texto que esclarece o motivo do NULL:

SELECT COALESCE(family,'All Families') AS family,
 COALESCE(category,'All Categories') AS category,
 COALESCE(subcategory,'All Subcategories') AS subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

O resultado é:

No resultado anterior, podemos observar como os valores NULL são substituídos por textos e as linhas dos relatórios com os textos começando com 'All' mostram um subtotal em quantity_in_stock.

Gostaria de sugerir o artigo The SQL COALESCE Function: Manuseio dos valores NULL, se você quiser saber mais sobre isto.

Vantagem de COALESCE() Para processar valores NULL

Neste artigo, demonstramos várias maneiras de usar a função SQL COALESCE(). Cobrimos como usar COALESCE() para substituir valores NULL, como calcular um valor alternativo, e como combinar COALESCE() com a cláusula ROLLUP, entre outros exemplos.

Antes de terminar, tenho outra recomendação para você. Nossa Folha de Consulta gratuita Funções Comuns em SQL permite que você encontre rapidamente detalhes sobre as funções embutidas do SQL, funções agregadas e muito mais. Eu a uso quase todos os dias quando trabalho com SQL. Você também pode experimentar nossa Funções Comuns em SQL curso, onde você pode aprender e praticar as funções SQL numéricas, de texto, data e NULL-handling. Aumente suas habilidades e aumente seus ativos!