COALESCE (Transact-SQL)

Artigo
10/31/2024

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Ponto de extremidade de análise do SQL Warehouse no Microsoft Fabric

Avalia os argumentos na ordem e retorna o valor atual da primeira expressão que não é avaliada como NULL inicialmente. Por exemplo, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); retorna o terceiro valor porque esse é o primeiro valor que não é nulo.

Convenções de sintaxe de Transact-SQL

Sintaxe

COALESCE ( expression [ ,...n ] )

Argumentos

expressão
É uma expressão de qualquer tipo.

Tipos de retorno

Retorna o tipo de dados de expressão com a maior precedência de tipo de dados. Se todas as expressões forem não anuláveis (nonnullable), o resultado será digitado como nonnullable.

Comentários

Se todos os argumentos forem NULL, COALESCE retornará NULL. Pelo menos um dos valores nulos precisa ser do tipo NULL.

Comparando COALESCE e CASE

A expressão COALESCE é um atalho sintático para a expressão CASE. Ou seja, o código COALESCE(expression1,...n) é reescrito pelo otimizador de consulta como a seguinte expressão CASE:

CASE  
WHEN (expression1 IS NOT NULL) THEN expression1  
WHEN (expression2 IS NOT NULL) THEN expression2  
...  
ELSE expressionN  
END

Dessa forma, os valores de entrada (expression1, expression2, expressionN e assim por diante) são avaliados várias vezes. Uma expressão de valor que contém uma consulta aninhada é considerada não determinística e a consulta aninhada é avaliada duas vezes. Esse resultado está em conformidade com o padrão SQL. Em ambos os casos, resultados diferentes podem ser retornados entre a primeira avaliação e as avaliações posteriores.

Por exemplo, quando o código COALESCE((subquery), 1) é executado, a subconsulta é avaliada duas vezes. Como resultado, você pode obter resultados diferentes dependendo do nível de isolamento da consulta. Por exemplo, o código pode retornar NULL no nível de isolamento READ COMMITTED em um ambiente multiusuário. Para assegurar que resultados estáveis sejam retornados, use o nível de isolamento SNAPSHOT ISOLATION ou substitua COALESCE pela função ISNULL. Como alternativa, você pode reescrever a consulta para enviar a consulta aninhada por push para uma seleção aninhada, conforme mostrado no exemplo a seguir:

SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END  
FROM  
(  
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x  
) AS T;

Comparando COALESCE e ISNULL

A função ISNULL e a expressão COALESCE têm uma finalidade semelhante, mas podem ter um comportamento diferente.

Como ISNULL é uma função, ela é avaliada apenas uma vez. Conforme descrito acima, a entrada de valores para a expressão COALESCE pode ser avaliada várias vezes.
A determinação de tipo de dados da expressão resultante é diferente. ISNULL usa o tipo de dados do primeiro parâmetro, COALESCE segue as regras da expressão CASE e retorna o tipo de dados de valor com a precedência mais alta.
A nulidade da expressão resultante é diferente para ISNULL e COALESCE. O valor retornado ISNULL sempre é considerado como um valor que não permite valor nulo (supondo que o valor retornado não permita valor nulo). Por outro lado,COALESCE com parâmetros não nulos é considerado NULL. Portanto, as expressões ISNULL(NULL, 1) e COALESCE(NULL, 1), embora equivalentes, têm valores diferentes de nulidade. Esses valores farão diferença se você estiver usando essas expressões em colunas computadas, criando restrições de chave ou tornando determinístico o valor retornado de um UDF escalar para que ele possa ser indexado, como mostrado no exemplo a seguir:
```
USE tempdb;  
GO  
-- This statement fails because the PRIMARY KEY cannot accept NULL values  
-- and the nullability of the COALESCE expression for col2   
-- evaluates to NULL.  
CREATE TABLE #Demo   
(   
  col1 INTEGER NULL,   
  col2 AS COALESCE(col1, 0) PRIMARY KEY,   
  col3 AS ISNULL(col1, 0)   
);   

-- This statement succeeds because the nullability of the   
-- ISNULL function evaluates AS NOT NULL.  

CREATE TABLE #Demo   
(   
  col1 INTEGER NULL,   
  col2 AS COALESCE(col1, 0),   
  col3 AS ISNULL(col1, 0) PRIMARY KEY   
);  
```
As validações de ISNULL e de COALESCE também são diferentes. Por exemplo, um valor NULL para ISNULL é convertido em int, já para COALESCE, você precisa fornecer um tipo de dados.
ISNULL utiliza apenas dois parâmetros. Por outro lado, COALESCE usa um número variável de parâmetros.

Exemplos

a. Executando um exemplo simples

O exemplo a seguir mostra como COALESCE seleciona os dados da primeira coluna que tem um valor não nulo. Este exemplo usa o banco de dados AdventureWorks2022.

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;

B. Executando um exemplo complexo

No exemplo a seguir, a tabela wages inclui três colunas que contêm informações sobre o salário anual dos funcionários: valor por hora, salário e comissão. No entanto, um funcionário recebe apenas um tipo de pagamento. Para determinar o valor total pago a todos os funcionários, use a função COALESCE para receber apenas o valor não nulo encontrado em hourly_wage, salary e commission.

SET NOCOUNT ON;  
GO  
USE tempdb;  
IF OBJECT_ID('dbo.wages') IS NOT NULL  
    DROP TABLE wages;  
GO  
CREATE TABLE dbo.wages  
(  
    emp_id        TINYINT   IDENTITY,  
    hourly_wage   DECIMAL   NULL,  
    salary        DECIMAL   NULL,  
    commission    DECIMAL   NULL,  
    num_sales     TINYINT   NULL  
);  
GO  
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)  
VALUES  
    (10.00, NULL, NULL, NULL),  
    (20.00, NULL, NULL, NULL),  
    (30.00, NULL, NULL, NULL),  
    (40.00, NULL, NULL, NULL),  
    (NULL, 10000.00, NULL, NULL),  
    (NULL, 20000.00, NULL, NULL),  
    (NULL, 30000.00, NULL, NULL),  
    (NULL, 40000.00, NULL, NULL),  
    (NULL, NULL, 15000, 3),  
    (NULL, NULL, 25000, 2),  
    (NULL, NULL, 20000, 6),  
    (NULL, NULL, 14000, 4);  
GO  
SET NOCOUNT OFF;  
GO  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS money) AS 'Total Salary'   
FROM dbo.wages  
ORDER BY 'Total Salary';  
GO

Veja a seguir o conjunto de resultados.

Total Salary  
------------  
10000.00  
20000.00  
20800.00  
30000.00  
40000.00  
41600.00  
45000.00  
50000.00  
56000.00  
62400.00  
83200.00  
120000.00  
  
(12 row(s) affected)

C: Exemplo simples

O exemplo a seguir demonstra como COALESCE seleciona os dados da primeira coluna que tem um valor não nulo. Considere para este exemplo que a tabela Products contém estes dados:

Name         Color      ProductNumber  
------------ ---------- -------------  
Socks, Mens  NULL       PN1278  
Socks, Mens  Blue       PN1965  
NULL         White      PN9876

Em seguida, executaremos a seguinte consulta COALESCE:

SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull   
FROM Products ;

Veja a seguir o conjunto de resultados.

Name         Color      ProductNumber  FirstNotNull  
------------ ---------- -------------  ------------  
Socks, Mens  NULL       PN1278         PN1278  
Socks, Mens  Blue       PN1965         Blue  
NULL         White      PN9876         White

Observe que na primeira linha, o valor de FirstNotNull é PN1278, não Socks, Mens. Esse valor funciona assim porque a coluna Name não foi especificada como um parâmetro para COALESCE no exemplo.

D: Exemplo complexo

O exemplo a seguir usa COALESCE para comparar os valores em três colunas e retornar apenas o valor não nulo encontrado nas colunas.

CREATE TABLE dbo.wages  
(  
    emp_id        TINYINT   NULL,  
    hourly_wage   DECIMAL   NULL,  
    salary        DECIMAL   NULL,  
    commission    DECIMAL   NULL,  
    num_sales     TINYINT   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;

Veja a seguir o conjunto de resultados.

Total Salary  
------------  
10000.00  
20000.00  
20800.00  
30000.00  
40000.00  
41600.00  
45000.00  
50000.00  
56000.00  
62400.00  
83200.00  
120000.00

Confira também

ISNULL (Transact-SQL)
CASE (Transact-SQL)

Compartilhar via

COALESCE (Transact-SQL)

Sintaxe

Argumentos

Tipos de retorno

Comentários

Comparando COALESCE e CASE

Comparando COALESCE e ISNULL

Exemplos

a. Executando um exemplo simples

B. Executando um exemplo complexo

C: Exemplo simples

D: Exemplo complexo

Confira também

Comentários

Recursos adicionais