Coalesce usage: Returns the first non-empty expression in its argument (similar to isnull)

Coalesce usage: Returns the first non-empty expression in its argument.

Transact-SQL Syntax conventions

Grammar

COALESCE (expression [,... n])

Parameters

Expression

An expression of any type.

return type

Returns the data type of the expression with the highest priority for the data type.

Note

If all parameters are NULL, COALESCE returns NULL.

Attention:

At least one null value should be a null type.

COALESCE (expression1,... N) is equivalent to this case function:

Copy Code

Case

When (expression1 are not NULL) THEN expression1

...

When (Expressionn are not NULL) THEN Expressionn

ELSE NULL

End

Although ISNULL is equivalent to coalesce, their behavior is different. An expression that contains a ISNULL with a non-null parameter is treated as NOT null, and an expression containing a coalesce with non-null arguments is considered null. In SQL Server, to create an index on an expression containing a coalesce with a non-null parameter, you can use the PERSISTED column property to persist the computed column as shown in the following statement:

Copy Code

CREATE TABLE #CheckSumTest

(

ID int Identity,

Num int DEFAULT (RAND () * 100),

Rowchecksum as COALESCE (CHECKSUM (ID, num), 0) PERSISTED PRIMARY KEY

)

Example

In the following example, the wages table includes the following three columns of information about the employee's yearly salary: hourly wage, salary, and commission. However, each employee can only accept one payment method. To determine the total amount of money paid to all employees, use the COALESCE function, which only accepts non-null values found in Hourly_wage, salary, and commission.

Copy Code

SET NOCOUNT on;

Go

Use master;

IF EXISTS (SELECT name from Sys.tables

WHERE name = ' wages ')

DROP TABLE wages;

Go

CREATE TABLE Wages

(

emp_id tinyint identity,

Hourly_wage decimal NULL,

Salary decimal NULL,

Commission decimal NULL,

Num_sales tinyint NULL

);

Go

INSERT Wages VALUES (10.00, NULL, NULL, NULL);

INSERT Wages VALUES (20.00, NULL, NULL, NULL);

INSERT Wages VALUES (30.00, NULL, NULL, NULL);

INSERT Wages VALUES (40.00, NULL, NULL, NULL);

INSERT wages VALUES (null, 10000.00, NULL, NULL);

INSERT wages VALUES (null, 20000.00, NULL, NULL);

INSERT wages VALUES (null, 30000.00, NULL, NULL);

INSERT wages VALUES (null, 40000.00, NULL, NULL);