COALESCE (Transact-SQL)
Retorna a primeira expressão não-nula entre seus argumentos.
.gif "Ícone de vínculo de tópico")
Sintaxe
COALESCE ( expression [ ,...n ] )
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.
.gif "Observação") 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 ;
GO
SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product ;
GO
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)