Artigo
09/28/2010

COALESCE (Transact-SQL)

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

Ícone de vínculo de tópico 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 usando-se o atributo da 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

Exemplo simples

O exemplo a seguir demonstra como COALESCE seleciona os dados da primeira coluna que tem um valor não-nulo.

USE AdventureWorks ;

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product ;

Exemplo complexo

No exemplo a seguir, a tabela wages contém três colunas com 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

Conjunto de resultados.

Salário total

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

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 linhas afetadas)

Consulte também

Referência

ISNULL (Transact-SQL)

CASE (Transact-SQL)

Partilhar via