COALESCE (Transact-SQL)

Artigo
01/29/2025

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

Avalia os argumentos em ordem e retorna o valor atual da primeira expressão que inicialmente não avalia para NULL. 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');

Observação

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

Transact-SQL convenções de sintaxe

Sintaxe

COALESCE ( expression [ , ...n ] )

Argumentos

expressão

Uma expressão de qualquer tipo.

Tipos de devolução

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 deve ser um NULLdigitado.

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

Como tal, os valores de entrada (expression1, expression2, expressionNe 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. Este 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 futuras.

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 sob o nível de isolamento READ COMMITTED em um ambiente multiusuário. Para garantir 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 subconsulta para uma subseleção, 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 comportar-se de forma diferente.

Como ISNULL é uma função, ela é avaliada apenas uma vez. Conforme descrito anteriormente, os valores de entrada para a expressão COALESCE podem ser avaliados várias vezes.
A determinação do 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 maior precedência.
A NULLability da expressão de resultado é diferente para ISNULL e COALESCE. O valor de retorno ISNULL é sempre considerado NOT NULLcapaz (assumindo que o valor de retorno é não anulável). Por outro lado,COALESCE com parâmetros não nulos é considerado NULL. Assim, as expressões ISNULL(NULL, 1) e COALESCE(NULL, 1), embora iguais, têm valores de anulabilidade diferentes. Esses valores fazem a diferença se você estiver usando essas expressões em colunas computadas, criando restrições de chave ou tornando determinístico o valor de retorno de uma função definida pelo usuário (UDF) escalar, para que ele possa ser indexado 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 para ISNULL e COALESCE também são diferentes. Por exemplo, um valor de NULL para ISNULL é convertido em int embora, para COALESCE, você deve fornecer um tipo de dados.
ISNULL leva apenas dois parâmetros. Por outro ladoCOALESCE leva 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 da home page Microsoft SQL Server Samples and Community Projects.

Um. 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. Suponha, para este exemplo, que a tabela Products contém esses 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;

Aqui está 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 é 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 de salários

No exemplo a seguir, a tabela wages inclui três colunas que contêm informações sobre os salários anuais dos funcionários: o salário por hora, o salário e a comissão. No entanto, um funcionário recebe apenas um tipo de remuneração. 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

Aqui está 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

Partilhar via

COALESCE (Transact-SQL)

Sintaxe

Argumentos

expressão

Tipos de devolução

Comentários

Comparar COALESCE e CASE

Comparar COALESCE e ISNULL

Exemplos

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

B. Retornar o valor não nulo em uma tabela de salários

Comentários

Recursos adicionais

Partilhar via

COALESCE (Transact-SQL)

Sintaxe

Argumentos

expressão

Tipos de devolução

Comentários

Comparar COALESCE e CASE

Comparar COALESCE e ISNULL

Exemplos

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

B. Retornar o valor não nulo em uma tabela de salários

Conteúdo relacionado

Comentários

Recursos adicionais