COALESCE returns the first value in the parameter list that is not NULL.
This function accepts multiple parameters of the same data type (usually column names).
COALESCE is a shortcut for a CASE expression, and also for multiple ISNULL functions.
Return the first parameter value that is not NULL.
SELECT COALESCE(NULL, NULL, 'Miami', NULL, 'New York') AS City
| City |
|---|
| Miami |
Syntax for the COALESCE function.
COALESCE(arg1, arg2, arg3, ...)
Note: This function takes any number of parameters.
| SUPPLIER |
|---|
| Id |
| CompanyName |
| ContactName |
| City |
| Country |
| Phone |
| Fax |
SELECT CompanyName, Phone,
COALESCE(Fax, '****') AS Fax
FROM Supplier
| CompanyName | Phone | Fax |
|---|---|---|
| Exotic Liquids | (171) 555-2222 | **** |
| New Orleans Cajun Delights | (100) 555-4822 | **** |
| Grandma Kelly's Homestead | (313) 555-5735 | (313) 555-3349 |
| Tokyo Traders | (03) 3555-5011 | **** |
| Cooperativa de Quesos 'Las Cabras' | (98) 598 76 54 | **** |
 |
||
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
SELECT ProductName,
COALESCE(SUM(I.UnitPrice * I.Quantity), 0) AS 'Total Sold'
FROM Product P
LEFT OUTER JOIN OrderItem I ON I.ProductId = P.Id
GROUP BY ProductName
ORDER BY SUM(I.UnitPrice * I.Quantity)
| ProductName | Total Sold |
|---|---|
| Stroopwafels | 0.00 |
| Chocolade | 1542.75 |
| Geitost | 1713.50 |
| Genen Shouyu | 1813.50 |
| Laughing Lumberjack Lager | 2562.00 |
| Longlife Tofu | 2566.00 |
| Gravad lax | 3047.20 |
| Aniseed Syrup | 3080.00 |
|
|
Note: Stroopwafels is the only unsold product. The Total Sold displays 0.00, instead of NULL, because of the COALESCE function.
