COALESCE (Transact-SQL)

Artigo
03/07/2014

Avalia os argumentos na 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 de COALESCE é um atalho sintático para a expressão CASE. Isto é, o código COALESCE(expression1,...n) é recriado 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 do 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, 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. 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 de vários usuários. Para garantir que resultados estáveis sejam retornados, use o nível de isolamento SNAPSHOT ISOLATION ou substitua COALESE com a função ISNULL. Como alternativa, você pode reescrever a consulta para inserir 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 uma finalidade semelhante, mas podem se comportar de maneira diferente.

Como ISNULL é uma função, ela é avaliada apenas uma vez. Conforme descrito acima, os valores de entrada para a 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, 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 de retorno ISNULL é sempre considerado não anulável (supondo que o valor de retorno seja não nulo) enquanto que COALESCE com parâmetros não nulos é considerado NULL. Assim, as expressões ISNULL(NULL, 1) e COALESCE(NULL, 1), embora equivalentes, têm valores diferentes de nulidade. Isso fará 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 UDF escalar de modo que 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 para ISNULL e COALESCE também são diferentes. Por exemplo, um valor NULO para ISNULL é convertido em int enquanto que, para COALESCE, você deve fornecer um tipo de dados.
ISNULL utiliza somente 2 parâmetros enquanto que COALESCE utiliza 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 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 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

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)

Compartilhar via