Com a tecnologia crescendo e evoluindo, é essencial manter-se atualizado com as últimas tendências como desenvolvedor. Seja um iniciante ou um especialista, uma sólida compreensão da manipulação de strings ajuda você a preparar os dados (por exemplo, gerar um formulário diferente do existente, tornando-o utilizável para o seu negócio) e gerenciá-lo usando funções de servidor SQL embutidas.
Além da manipulação de dados, você pode examinar conjuntos de dados, avaliar valores de dados e codificá-los ou decodificá-los para gerar dados mais significativos. Como resultado, isso ajuda você a navegar pelos valores ausentes nos conjuntos de dados, compreender seu impacto nos cálculos e simplificar o trabalho geral com o processo de dados para evitar valores nulos que podem arruinar os resultados da operação.
Este guia orienta você pela função de coalescência no SQL, que ajuda a criar programas complexos. A postagem pressupõe que você encontrou e trabalhou com SQL e apenas procura reforçar sua compreensão dessa função específica. Nossa série de guias SQL pode ajudá-lo a começar rapidamente.
últimas postagens
O que é COALESCE () no SQL e seus usos?
A função coalesce no SQL avalia parâmetros (argumentos) em uma ordem especificada, como listas, e retorna o primeiro valor não nulo. Simplificando, a função avalia sua lista sequencialmente e termina na instância do primeiro valor não nulo. Se todos os argumentos da lista forem nulos, a função retornará NULL.
Além disso, a função é inclusiva e suportada em outros bancos de dados como MYSQL, Azure SQL Database, Oracle e PostgreSQL.
Você pode usar Coalesce nas seguintes instâncias quando:
- Manipulação de valores NULL.
- Executando várias consultas como uma.
- Evitando declarações CASE longas e demoradas.
Quando usado no lugar de instruções CASE (ou a função ISNULL), coalesce usa muitos parâmetros, ao contrário de CASE, que usa apenas dois. Essa abordagem permite escrever menos código e facilita o processo de escrita.
Aqui está a sintaxe:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
Coalesce no servidor SQL tem várias propriedades, incluindo argumentos do mesmo tipo de dados, aceitando muitos parâmetros e argumentos do tipo inteiro a serem cascateados por uma função yield para retornar um inteiro como saída.
Leia também: Ultimate SQL Cheat Sheet para marcar para mais tarde
Mas antes de entrar em como usar o coalesce, vamos entender NULL.
O que é um valor NULL em SQL?
O marcador único NULL no SQL indica a inexistência de um valor no banco de dados. Você pode pensar nisso como um valor indefinido ou desconhecido. Por favor, não caia na armadilha de pensar nisso como uma string vazia ou um valor zero; é a ausência de um valor. A ocorrência nula nas colunas da tabela representa informações ausentes.
Em um caso de uso prático, a coluna de dados em uma coluna de banco de dados de um site de comércio eletrônico pode ser preenchida com um valor NULL se um cliente não fornecer seu ID. Nulo em SQL é único; é um estado, ao contrário de outras linguagens de programação, onde significa “não apontar para um objeto específico”.
Os valores NULL no SQL têm um impacto significativo nos bancos de dados relacionais. Primeiro, eles permitem que você exclua valores específicos ao trabalhar com outras funções internas. Por exemplo, você pode gerar uma lista de pedidos totais em um ambiente de produção, mas outros ainda precisam ser concluídos. Usar NULL como seu espaço reservado permite que a função SUM interna adicione os totais.
Além disso, considere os casos em que você precisa gerar a média usando a função AVG. Se você trabalhar com valores zero, os resultados serão distorcidos. Em vez disso, o banco de dados pode remover esses campos e usar NULL, resultando em saídas precisas.
Os valores NULL não têm desvantagens. São considerados valores de comprimento variável, sendo bytes ou vários deles. Como o banco de dados deixa espaço para esses bytes se eles excederem o que está armazenado no banco de dados, o resultado é que seu banco de dados ocupa mais espaço no disco rígido em vez de usar valores regulares.
Além disso, ao trabalhar com algumas funções, você precisará personalizá-las para eliminar NULLS. Isso, como resultado, torna seus procedimentos SQL mais longos.
Manipulando valores NULL com COALESCE ()
Valores nulos indicam que você pode ter um valor, mas não sabe qual deve ser. Até que você colete dados que preencham seus campos com valores reais, os valores NULL são os procuradores.
Embora você possa usar valores NULL para vários tipos de dados em seu banco de dados, incluindo decimais, strings, blobs e inteiros, é uma boa prática evitá-los ao lidar com dados numéricos.
A desvantagem é que, quando usado para valores numéricos, você provavelmente precisará de esclarecimentos ao desenvolver o código que funciona com dados. Mais sobre isso mais tarde.
As diferentes maneiras como COALESCE () podem ser usadas para lidar com o valor NULL:
Usando COALESCE () para substituir valores nulos por um valor específico
Você pode usar COALESCE () para retornar valores específicos para todos os valores nulos. Por exemplo, você pode ter uma tabela chamada “funcionários” com a coluna “salário”, que pode conter valores nulos caso o salário dos funcionários não tenha sido creditado. Portanto, ao fazer alguns cálculos, você pode querer trabalhar com um valor específico, zero neste caso, para todas as entradas NULL. Veja como fazer isso.
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
Usando COALESCE () para selecionar o primeiro valor não nulo de várias opções
Às vezes, você pode querer trabalhar com os primeiros valores não NULL em uma lista de expressões. Nesses casos, muitas vezes você tem várias colunas com dados relacionados e deseja priorizar seus valores não NULL. A sintaxe permanece.
COALESCE (expression1, expression2, …)
Em um caso prático, suponha que você tenha uma tabela de contatos com as colunas prefer_name e full_name. E você deseja gerar uma lista de contatos lado a lado com seus nomes preferidos (se disponíveis) ou seus nomes completos. Veja como lidar com isso.
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
Se o preferido_name não for NULL para este caso de teste, ele será retornado. Caso contrário, o nome completo será retornado como o nome de exibição.
Concatenação de String com SQL Coalesce
Você pode encontrar problemas com SQL ao concatenar strings se valores nulos estiverem envolvidos. Nesses casos, NULL é retornado como um resultado indesejável. Agora que NULL não é o resultado desejado, você pode corrigir o problema usando a função de coalescência. Abaixo está um exemplo.
Uma concatenação simples de strings é feita por:
SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example
O código retorna:
ExemploOlá, onde está você, John?
No entanto, se você usar um valor NULL, conforme mostrado abaixo:
SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example
A saída é agora.
Como toda concatenação de string de texto envolvendo um valor NULL retorna NULL, o resultado acima é NULL. O problema é, no entanto, resolvido usando o coalesce (). Usando esta função, você retorna uma string vazia (ou um espaço) em vez de NULL. Por exemplo, suponha que você esteja listando nomes de carros com seus fabricantes; aqui está sua consulta.
SELECT car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand FROM stock
Se o fabricante for NULL, você terá o ‘–’ em vez de NULL. Aqui estão os resultados esperados.
car_brandoutlander, fabricante: — esporas voadoras, fabricante: Bentleyroyal atleta, fabricante: —royal saloon, fabricante: Crown
Como você pode ver, os resultados NULL são eliminados, com a opção de inserir o valor da string de substituição.
SQL Coalesce Function e Pivoting
SQL pivoting é uma técnica usada para transformar linhas em colunas. Permite transpor (girar) dados da forma “normalizada” (com muitas linhas e menos colunas) para a “desnormalizada” (menos linhas e mais colunas). A função coalesce pode ser usada com SQL pivoting para lidar com valores nulos em resultados pivotados.
Quando você PIVOT em SQL, transforme linhas em colunas; as colunas resultantes são funções agregadas de alguns dados. Se, em qualquer caso, uma agregação resultar em nulo para uma determinada célula, você pode usar `COALESCE` para substituir os valores nulos por um valor padrão ou representação significativa. Abaixo está um exemplo.
Considere uma tabela, vendas, com as colunas ano, trimestre e receita, e você deseja dinamizar os dados; de modo que você tenha anos como colunas e a soma da receita de cada trimestre como valores. Porém, alguns trimestres não possuem dados de receita, fornecendo valores nulos no resultado articulado. Nesse caso, você pode usar COALESCE para substituir valores nulos no resultado dinâmico por zero (0).
SELECT
year,
COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;
Função escalar definida pelo usuário e função de coalescência SQL
Você pode usar UDFs escalares e coalescer para executar uma lógica complexa que lida com valores nulos. A combinação desses recursos ajudará você a obter transformações de dados e cálculos mais sofisticados em consultas SQL. Considere uma tabela, Employees, com esta estrutura.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
Você pode querer calcular os ganhos totais de cada funcionário (salário mais bônus). No entanto, existem alguns valores ausentes. Nesse caso, sua UDF escalar pode lidar com as adições de salário e bônus, enquanto coalesce lida com os valores nulos. Aqui está o UDF escalar para ganhos totais.
CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
DECLARE @totalEarnings INT;
SET @totalEarnings = @salary + COALESCE(@bonus, 0);
RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;
Validação de dados usando SQL Coalesce
Ao trabalhar com bancos de dados, você pode querer validar valores numéricos. Por exemplo, digamos que você tenha as colunas nome_do_produto, preço e desconto em uma tabela produtos. Você deseja recuperar os nomes dos produtos, preços e descontos de cada item. Mas você gostaria de tratar todos os valores de desconto NULL como 0. A função de coalescência pode ser útil. Veja como usá-lo.
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL Coalesce e Colunas Calculadas
Colunas calculadas são colunas virtuais calculadas com base em expressões ou outras colunas em uma tabela. Como as colunas calculadas não são armazenadas fisicamente no banco de dados, você pode aproveitá-las com a função de coalescência ao lidar com cenários e transformações complexas. Aqui está um exemplo prático de caso de uso.
Considere uma tabela `products` com as colunas `price`, `discount` e `tax_rate`. Nesse caso, você deseja criar uma coluna computada, `total_price`, para representar o preço final do produto após a aplicação de desconto e imposto. Se o desconto ou o imposto não for especificado (NULL), você deseja prosseguir com seus cálculos usando um zero. Veja como aproveitar a coalescência para se adequar à operação.
CREATE TABLE products( price DECIMAL(10, 2), discount DECIMAL(10, 2), tax_rate DECIMAL(5, 2), total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) );
No código acima, aqui está o que acontece.
A configuração acima permite gerar o total_price, uma coluna calculada, com o preço final real, apesar de faltar ou ter valores NULL.
SQL Coalesce e Expressão CASE
Você pode usar sintaticamente a união por meio da expressão CASE. Aqui está um exemplo:
SELECT Productname + ‘ ’+ deliverydate productdetails, dealer, CASE WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE ‘NA’ END EmergencyContactNumber FROM dbo.tb_EmergencyContact
Na configuração acima, CASE consulta como a função COALESCE.
Além disso, é possível usar as expressões COALESCE e CASE na mesma consulta. As duas técnicas podem lidar com valores NULL e aplicar lógica condicional simultaneamente. Vamos ilustrar isso com um exemplo.
Considere um caso em que você tem uma tabela, produtos com as colunas product_id, product_name, preço e desconto. Alguns de seus produtos têm um desconto específico, enquanto outros não. Se um produto tiver desconto, você deseja mostrar o preço com desconto, caso contrário, o regular deve ser exibido.
SELECT
product_id,
product_name,
price,
COALESCE(
CASE
WHEN discount > 0 THEN price - (price * discount / 100)
ELSE NULL
END,
price
) AS discounted_price
FROM products;
No código acima, o `CASE` verifica se o `desconto` é maior que zero, e calcula o preço com desconto, senão retorna NULL. A função `COALESCE` recebe o resultado de `CASE` e `price` como seus parâmetros. Ele retorna o primeiro valor não NULL, retornando efetivamente o preço com desconto, se disponível, ou o preço normal, se não houver.
Palavras Finais
Este post demonstrou várias maneiras de usar a função `COALESCE` em suas consultas de banco de dados. Ao avaliar os parâmetros em uma ordem especificada e retornar o primeiro valor não NULL, a função coalesce simplifica as consultas, tornando-as eficientes.
Coalesce é uma função versátil, esteja você lidando com valores nulos, concatenação de strings, dinamização de dados, validação ou trabalhando com colunas computadas. Ao dominar a função de coalescência, os desenvolvedores podem navegar pelos dados ausentes e criar designs de banco de dados sem erros. Lembre-se, para dominar a técnica; você pode precisar de uma prática mais aprofundada.
Agora você pode verificar como criar restrições de chave estrangeira no SQL.