COALESCE (Transact-SQL)

Artigo
01/07/2014

Avalia os argumentos em ordem e retorna o valor atual da primeira expressão que inicialmente não é avaliada como NULL.

Aplica-se a: SQL Server (SQL Server 2008 até a versão atual), Banco de dados SQL do Windows Azure (versão inicial até a versão atual).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

COALESCE ( expression [ ,...n ] )

Argumentos

expression
É uma expressão de qualquer tipo.

Tipos de retorno

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

Comentários

Se todos os argumentos forem o NULL, COALESCE retorna NULL. Pelo menos um dos valores nulos deve ser um NULL com tipo.

Comparando COALESCE e CASE

A expressão COALESCE é um atalho sintático para a expressão CASE. Isto é, 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

Isso significa que os valores de entrada (expression1, expression2, expressionN etc.) serão avaliados várias vezes. Além disso, em conformidade com o padrão SQL, uma expressão de valor que contém uma subconsulta é considerada não determinística e a subconsulta é avaliada duas vezes. Em ambos os casos, os resultados diferentes podem ser retornados entre a primeira avaliação e as avaliações subsequentes.

Por exemplo, quando o código COALESCE((subquery), 1) é executado, a subconsulta é avaliada duas vezes. Assim, você pode obter resultados diferentes de acordo com o nível de isolamento da consulta. Por exemplo, o código pode retornar NULL no nível de isolamento READ COMMITTED em um ambiente de vários usuários. Para garantir o retorno de resultados estáveis, use o nível de isolamento SNAPSHOT ISOLATION ou substitua COALESE pela função ISNULL. Outra opção é reescrever a consulta para forçar a subconsulta em 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;

Comparando COALESCE e ISNULL

A função ISNULL e a expressão COALESCE têm finalidades semelhantes, mas podem se comportar de maneira diferente.

Como ISNULL é uma função, ela é avaliada apenas uma vez. Conforme descrito antes, 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; COALESCE segue as regras da expressão CASE e retorna o tipo de dados de valor com a precedência mais alta.
A nulidade (NULL) da expressão resultante é diferente para ISNULL e COALESCE. O valor de retorno ISNULL é sempre considerado NOT NULL (supondo que o valor de retorno seja não nulo), enquanto COALESCE com parâmetros não nulos é considerado NULL. Então, as expressões ISNULL(NULL, 1) e COALESCE(NULL, 1), embora equivalentes, têm valores de nulidade diferentes. Isso faz diferença quando você está usando essas expressões em colunas computadas, criando restrições de chave ou tornando o valor de retorno de uma UDF escalar determinístico de modo 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 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 para ISNULL e COALESCE também são diferentes. Por exemplo, um valor NULL para ISNULL é convertido em int; no caso de COALESCE, você deve fornecer um tipo de dados.
ISNULL usa apenas 2 parâmetros, enquanto COALESCE usa uma quantidade 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 AdventureWorks2012.

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 os salários anuais 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

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

(12 row(s) affected)

Consulte também

Referência

ISNULL (Transact-SQL)

CASE (Transact-SQL)

Share via