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