COALESCE (Transact-SQL)

Retorna a primeira expressão não nula entre seus argumentos.

Convenções de 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.

Observação
Pelo menos um dos valores nulos deve ser um NULL com tipo.

COALESCE(expression1,...n) equivale à seguinte expressão CASE:

CASE

   WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

Embora ISNULL e COALESCE sejam equivalentes, podem se comportar de maneira diferente. Uma expressão que envolve ISNULL com parâmetros não nulos é considerada NOT NULL, enquanto as expressões que envolvem COALESCE com parâmetros não nulos são consideradas NULL. No SQL Server, para indexar expressões que envolvem COALESCE com parâmetros não nulos, a coluna computada pode ser persistente com o uso do atributo de coluna PERSISTED como na instrução a seguir:

CREATE TABLE #CheckSumTest 
    (
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
    );

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.

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

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

------------

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

(12 row(s) affected)

Consulte também

Referência

ISNULL (Transact-SQL)

CASE (Transact-SQL)