COALESCE (T-SQL)

Returns the first nonnull expression among its arguments.If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to the following
CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

Example

--************ coalesce *******

CREATE TABLE dbo.wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);

INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(10.00, NULL, NULL, NULL),
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(20.00, NULL, NULL, NULL),
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(30.00, NULL, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(40.00, NULL, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 10000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 20000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 30000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 40000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 15000, 3)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 25000, 2)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 20000, 6)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 14000, 4)

SELECT * FROM dbo.wages

SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';

-- using CASE Statement

SELECT CAST(CASE WHEN hourly_wage IS NOT NULL THEN (hourly_wage * 40 * 52)
WHEN salary IS NOT NULL THEN salary
WHEN commission IS NOT NULL AND num_sales IS NOT NULL THEN (commission * num_sales)
END AS MONEY) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';