Domine o SQL: Guia Completo da Função COALESCE()

Por

No cenário tecnológico em constante expansão e evolução, é fundamental que desenvolvedores se mantenham atualizados com as últimas tendências. Seja você um novato na área ou um profissional experiente, um profundo conhecimento da manipulação de strings é indispensável. Essa habilidade permite preparar dados de maneira eficaz, como gerar formulários personalizados que atendam às necessidades específicas do seu negócio, e gerenciá-los utilizando funções de servidor SQL embutidas.

Além da manipulação, a capacidade de analisar conjuntos de dados, avaliar seus valores e codificá-los ou decodificá-los para gerar informações relevantes é crucial. Isso facilita a identificação e o tratamento de valores ausentes, permitindo que você compreenda o impacto desses valores em seus cálculos. O resultado é um processo de dados simplificado e livre de valores nulos que possam comprometer os resultados.

Este guia tem como objetivo apresentar a função de coalescência no SQL, uma ferramenta essencial para a criação de programas complexos. Partimos do pressuposto de que você já possui familiaridade com o SQL e busca apenas aprimorar sua compreensão desta função específica. Caso precise de uma introdução ao SQL, nossa série de guias pode ser um ótimo ponto de partida.

O que é a função COALESCE() em SQL e quais são suas aplicações?

A função `COALESCE()` em SQL avalia os parâmetros fornecidos, como listas, em uma ordem específica e retorna o primeiro valor que não seja nulo. Em outras palavras, ela percorre a lista sequencialmente, interrompendo-se no primeiro valor válido que encontrar. Se todos os argumentos na lista forem nulos, a função retornará NULL.

É importante destacar que esta função é amplamente aceita e suportada em diversos bancos de dados, incluindo MySQL, Azure SQL Database, Oracle e PostgreSQL.

Você pode utilizar a função `COALESCE()` nas seguintes situações:

  • Tratamento de valores NULL.
  • Execução de múltiplas consultas em uma única operação.
  • Substituição de longas e complexas declarações CASE.

A função `COALESCE()` oferece uma vantagem em relação às instruções CASE (ou à função ISNULL) por permitir o uso de múltiplos parâmetros, enquanto o CASE se limita a apenas dois. Essa característica resulta em um código mais conciso e simplifica o processo de escrita.

A sintaxe da função é a seguinte:

COALESCE(valorUm, valorDois, valorTrês, …, valorX);

Em servidores SQL, a função `COALESCE()` apresenta diversas características notáveis: aceita argumentos do mesmo tipo de dados, suporta múltiplos parâmetros e permite que argumentos do tipo inteiro sejam processados em cascata para retornar um inteiro como saída.

Recomendamos a leitura do nosso guia completo sobre SQL para consulta posterior.

Antes de explorarmos as aplicações da função `COALESCE()`, é essencial entendermos o conceito de valores NULL.

O que significa um valor NULL em SQL?

Em SQL, o marcador `NULL` é utilizado para indicar a ausência de um valor em um banco de dados. Você pode considerá-lo um valor indefinido ou desconhecido. É fundamental não confundi-lo com uma string vazia ou um valor zero; ele representa a inexistência de um valor. A presença de NULL em colunas de tabelas significa que há informações faltantes.

Em um exemplo prático, em um site de e-commerce, a coluna de ID de um cliente pode conter um valor NULL caso o cliente não tenha fornecido essa informação. O NULL em SQL possui um significado específico, representando um estado e não, como em outras linguagens de programação, a ausência de uma referência a um objeto.

Valores NULL têm um impacto significativo em bancos de dados relacionais. Primeiramente, eles permitem a exclusão de valores específicos ao utilizar outras funções embutidas. Por exemplo, ao gerar uma lista de pedidos totais em um ambiente de produção, é possível que alguns ainda estejam pendentes. Utilizar NULL como um espaço reservado permite que a função `SUM` calcule os totais corretamente.

Além disso, considere a situação em que você precisa calcular uma média utilizando a função `AVG`. Se você trabalhar com valores zero, os resultados serão distorcidos. Ao invés disso, o banco de dados pode remover esses campos e usar NULL, resultando em saídas mais precisas.

Apesar de sua utilidade, os valores NULL possuem algumas desvantagens. Eles são tratados como valores de tamanho variável, o que significa que o banco de dados reserva um espaço em bytes, e o uso de NULLs em excesso pode fazer com que seu banco de dados ocupe mais espaço em disco. Além disso, em algumas funções, você precisará customizar seu código para eliminar os NULLs, o que pode tornar seus procedimentos SQL mais extensos.

Tratando valores NULL com COALESCE()

Valores nulos indicam que um valor pode existir, mas não se sabe qual ele deve ser. Enquanto os dados não são coletados, os valores NULL são substitutos.

Embora os valores NULL possam ser usados com diversos tipos de dados, como decimais, strings, blobs e inteiros, é recomendável evitá-los ao trabalhar com dados numéricos. Isso ocorre porque o uso de NULLs em dados numéricos pode gerar ambiguidade no desenvolvimento do código. Abordaremos esse ponto com mais detalhes adiante.

A função `COALESCE()` pode ser usada de várias formas para tratar valores NULL:

Substituindo valores NULL por um valor específico com COALESCE()

Você pode usar `COALESCE()` para substituir todos os valores NULL por um valor específico. Por exemplo, em uma tabela chamada “funcionários” com uma coluna “salário” que contém valores NULL em caso de não pagamento, você pode utilizar `COALESCE()` para substituir esses valores por zero durante os cálculos. Veja como fazer isso:

SELECT COALESCE(salário, 0) AS salário_ajustado
FROM funcionários;

Selecionando o primeiro valor não nulo de múltiplas opções com COALESCE()

Em alguns casos, você pode precisar trabalhar com o primeiro valor não NULL em uma lista de expressões. Geralmente, isso acontece quando você possui múltiplas colunas com dados relacionados e deseja priorizar aqueles que não são nulos. A sintaxe permanece a mesma:

COALESCE(expressão1, expressão2, …)

Em uma situação prática, considere uma tabela de contatos com as colunas “nome_preferido” e “nome_completo”. Se você deseja gerar uma lista de contatos com seus nomes preferidos (se disponíveis) ou seus nomes completos, pode utilizar a função `COALESCE()`. Veja como:

SELECT COALESCE(nome_preferido, nome_completo) AS nome_exibição
FROM contatos;

Nesse caso, se “nome_preferido” não for NULL, ele será retornado; caso contrário, o “nome_completo” será utilizado como o nome de exibição.

Concatenação de strings com SQL COALESCE

Ao concatenar strings em SQL, a presença de valores nulos pode gerar problemas, resultando em NULL como resultado indesejado. Para corrigir isso, você pode usar a função de coalescência. Veja um exemplo:

Uma simples concatenação de strings pode ser feita da seguinte forma:

SELECT 'Olá, onde você está, ' || 'João' || '?' AS exemplo

O código retornará:

Exemplo: Olá, onde você está, João?

No entanto, se usarmos um valor NULL, como mostrado abaixo:

SELECT 'Olá, onde você está, ' || null || '?' AS exemplo

A saída será:

NULL

Como toda concatenação de string que envolve um valor NULL retorna NULL, o resultado é NULL. No entanto, esse problema pode ser resolvido usando a função `COALESCE()`. Com esta função, podemos retornar uma string vazia ou um espaço em vez de NULL. Por exemplo, ao listar nomes de carros com seus fabricantes, podemos ter a seguinte consulta:

SELECT
carro || ', fabricante: ' || COALESCE(fabricante, '---') AS marca_carro
FROM estoque

Se o fabricante for NULL, teremos ‘–‘ no lugar de NULL. Veja os resultados esperados:

marca_carro: outlander, fabricante: —
marca_carro: esporas voadoras, fabricante: Bentley
marca_carro: atleta real, fabricante: —
marca_carro: salão real, fabricante: Crown

Como você pode observar, os resultados NULL são eliminados e temos a opção de inserir um valor de string de substituição.

A função SQL Coalesce e Pivoting

SQL pivoting é uma técnica utilizada para transformar linhas em colunas. Ela possibilita a transposição de dados de um formato “normalizado” (com muitas linhas e poucas colunas) para um formato “desnormalizado” (com menos linhas e mais colunas). A função `COALESCE` pode ser usada com SQL pivoting para tratar valores nulos em resultados pivotados.

Ao utilizar o PIVOT em SQL, transformamos linhas em colunas, e as colunas resultantes são funções agregadas de alguns dados. Caso uma agregação resulte em um valor nulo para uma determinada célula, podemos usar `COALESCE` para substituir os valores nulos por um valor padrão ou uma representação significativa. Veja um exemplo abaixo:

Considere uma tabela, “vendas”, com as colunas “ano”, “trimestre” e “receita”, e você deseja dinamizar os dados para ter os anos como colunas e a soma da receita de cada trimestre como valores. Porém, alguns trimestres não possuem dados de receita, gerando valores nulos no resultado articulado. Nesse caso, você pode usar `COALESCE` para substituir valores nulos no resultado dinâmico por zero (0).

SELECT
    ano,
    COALESCE(SUM(CASE WHEN trimestre="Q1" THEN receita END), 0) AS receita_Q1,
    COALESCE(SUM(CASE WHEN trimestre="Q2" THEN receita END), 0) AS receita_Q2,
    COALESCE(SUM(CASE WHEN trimestre="Q3" THEN receita END), 0) AS receita_Q3,
    COALESCE(SUM(CASE WHEN trimestre="Q4" THEN receita END), 0) AS receita_Q4
FROM vendas
GROUP BY ano;

Função escalar definida pelo usuário e função de coalescência SQL

Você pode utilizar UDFs escalares e coalescer para executar uma lógica complexa que trata valores nulos. A combinação desses recursos permite obter transformações de dados e cálculos mais sofisticados em consultas SQL. Considere uma tabela, “Empregados”, com esta estrutura:

CREATE TABLE Empregados (
    ID_Empregado INT PRIMARY KEY,
    Nome VARCHAR(50),
    Sobrenome VARCHAR(50),
    Salario INT,
    Bonus INT
);

Suponha que você queira calcular os ganhos totais de cada funcionário (salário mais bônus). No entanto, existem alguns valores ausentes. Neste caso, sua UDF escalar pode lidar com as adições de salário e bônus, enquanto coalesce trata os valores nulos. Veja a seguir o UDF escalar para ganhos totais:

CREATE FUNCTION dbo.CalcularGanhosTotais (@salario INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @ganhosTotais INT;
    SET @ganhosTotais = @salario + COALESCE(@bonus, 0);
    RETURN @ganhosTotais;
END;
Você pode utilizar o UDF escalar com coalesce em uma consulta:
SELECT ID_Empregado, Nome, Sobrenome,
       Salario, Bonus, dbo.CalcularGanhosTotais(Salario, Bonus) AS GanhosTotais
FROM Empregados;

Validação de dados usando SQL Coalesce

Ao trabalhar com bancos de dados, você pode precisar validar valores numéricos. Por exemplo, imagine que você tenha as colunas “nome_do_produto”, “preco” e “desconto” em uma tabela “produtos”. Você deseja recuperar os nomes dos produtos, preços e descontos de cada item, tratando todos os valores de desconto NULL como 0. A função de coalescência pode ser útil. Veja como usá-la:

SELECT nome_produto, preco, COALESCE(desconto, 0) AS desconto
FROM produtos

SQL Coalesce e Colunas Calculadas

Colunas calculadas são colunas virtuais geradas com base em expressões ou outras colunas em uma tabela. Por não serem armazenadas fisicamente no banco de dados, você pode utilizá-las com a função de coalescência em cenários e transformações mais complexas. Veja um exemplo prático:

Considere uma tabela `produtos` com as colunas `preco`, `desconto` e `taxa_imposto`. O objetivo é criar uma coluna computada, `preco_total`, que represente o preço final do produto após a aplicação de desconto e imposto. Caso o desconto ou o imposto não sejam especificados (NULL), você deseja continuar os cálculos usando o valor zero. Veja como usar a função coalescência para essa operação.

CREATE TABLE produtos(
preco DECIMAL(10, 2),
desconto DECIMAL(10, 2),
taxa_imposto DECIMAL(5, 2),
preco_total AS (COALESCE(preco, 0) – COALESCE(preco*desconto, 0))* COALESCE(1+taxa_imposto, 1)
);

No código acima:

  • A coluna calculada `preco_total` é definida como `(COALESCE(preco, 0) – COALESCE(preco*desconto, 0))* COALESCE(1+taxa_imposto, 1)`.
  • Se o preço for NULL, `COALESCE(preco, 0)` garante que ele será tratado como 0.
  • Se o desconto for NULL, `COALESCE(preco*desconto, 0)` garante que ele será tratado como 0, e a multiplicação não afetará o cálculo.
  • Se a `taxa_imposto` for NULL, `COALESCE(1 + taxa_imposto, 1)` garante que ela será tratada como 0, o que significa que nenhum imposto é aplicado, e a multiplicação não afetará o cálculo.

Essa configuração permite gerar `preco_total`, uma coluna calculada, com o preço final real, mesmo com a falta ou presença de valores NULL.

SQL Coalesce e Expressão CASE

Você pode utilizar a união sintaticamente através da expressão CASE. Veja um exemplo:

SELECT
NomeProduto + ' ' + DataEntrega detalhes_produto,
Distribuidor,
CASE
WHEN Celular IS NOT NULL THEN Celular
WHEN TelefoneTrabalho IS NOT NULL THEN TelefoneTrabalho
ELSE 'NA'
END
NumeroContatoEmergencia
FROM
dbo.tb_ContatoEmergencia

Nesta configuração, o CASE consulta como a função `COALESCE`.

Além disso, é possível usar as expressões `COALESCE` e CASE na mesma consulta. Ambas as técnicas podem tratar valores NULL e aplicar lógica condicional simultaneamente. Vamos analisar um exemplo:

Considere uma tabela “produtos” com as colunas “id_produto”, “nome_produto”, “preco” e “desconto”. Alguns produtos têm um desconto específico, enquanto outros não. Se um produto tiver desconto, você deseja exibir o preço com desconto, caso contrário, o preço normal. Veja como usar `COALESCE` e `CASE` em conjunto:

SELECT
    id_produto,
    nome_produto,
    preco,
    COALESCE(
        CASE
            WHEN desconto > 0 THEN preco - (preco * desconto / 100)
            ELSE NULL
        END,
        preco
    ) AS preco_com_desconto
FROM produtos;

No código acima, o `CASE` verifica se o `desconto` é maior que zero e calcula o preço com desconto; caso contrário, ele retorna NULL. A função `COALESCE` recebe o resultado do `CASE` e o `preco` como parâmetros. Ela retorna o primeiro valor não NULL, efetivamente retornando o preço com desconto, se disponível, ou o preço normal caso contrário.

Considerações Finais

Neste artigo, apresentamos diversas maneiras de utilizar a função `COALESCE()` em suas consultas de banco de dados. Ao avaliar parâmetros em uma ordem específica e retornar o primeiro valor não nulo, a função simplifica suas consultas e as torna mais eficientes.

A função `COALESCE()` é uma ferramenta versátil, seja para tratamento de valores nulos, concatenação de strings, dinâmica de dados, validação ou trabalho com colunas calculadas. Ao dominar essa função, desenvolvedores podem lidar melhor com dados ausentes e criar designs de bancos de dados sem erros. Lembre-se de que o domínio completo dessa técnica requer prática contínua.

Agora você pode verificar como criar restrições de chave estrangeira no SQL.