COALESCE (Transact-SQL)

Artigo
01/29/2025

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

Avalia os argumentos na ordem e retorna o valor atual da primeira expressão que não é avaliada como NULL inicialmente. O exemplo a seguir retorna o terceiro valor porque o terceiro valor é o primeiro valor que não é nulo.

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

Nota

Se você quiser concatenar cadeias de caracteres, use STRING_AGG em vez disso.

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 não forem anuláveis, o resultado será digitado como não anulável.

Comentários

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

Comparar 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 subconsulta é considerada não determinística e a subconsulta é 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;

Comparar 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 anteriormente, os valores de entrada da expressão COALESCE podem ser avaliados 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 e COALESCE segue as regras de expressão CASE para retornar 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 NOT NULLcapaz (supondo que o valor retornado seja não anulável). 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 fazem a diferença se você estiver usando essas expressões em colunas computadas, criando restrições de chave ou tornando o valor retornado de uma função escalar definida pelo usuário (UDF) determinística, para que ela possa ser indexada conforme 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 INT 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 INT 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

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que você pode baixar na página inicial Microsoft SQL Server Samples and Community Projects.

a. Retornar dados da primeira coluna que tem um valor não nulo

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, executamos 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

Na primeira linha, o valor 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.

B. Retornar o valor não nulo em uma tabela salarial

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 COALESCE para receber apenas o valor não nulo encontrado em hourly_wage, salarye 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

Compartilhar via

COALESCE (Transact-SQL)

Sintaxe

Argumentos

expressão

Tipos de retorno

Comentários

Comparar COALESCE e CASE

Comparar COALESCE e ISNULL

Exemplos

a. Retornar dados da primeira coluna que tem um valor não nulo

B. Retornar o valor não nulo em uma tabela salarial

Comentários

Recursos adicionais

Compartilhar via

COALESCE (Transact-SQL)

Sintaxe

Argumentos

expressão

Tipos de retorno

Comentários

Comparar COALESCE e CASE

Comparar COALESCE e ISNULL

Exemplos

a. Retornar dados da primeira coluna que tem um valor não nulo

B. Retornar o valor não nulo em uma tabela salarial

Conteúdo relacionado

Comentários

Recursos adicionais