SQL COALESCE Function

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.

Example

#

Return the first parameter value that is not NULL.

SELECT COALESCE(NULL, NULL, 'Miami', NULL, 'New York') AS City
Result:  1 record
City
Miami

Syntax

Syntax for the COALESCE function.

COALESCE(arg1, arg2, arg3, ...)

Note: This function takes any number of parameters.


More Examples

COALESCE with COLUMN

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List suppliers with their phone and fax numbers.
If they have no fax, return '****'.
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 ****

COALESCE. With JOIN and Aggregate function.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: List products with total amount sold, sorted from low to high. Unsold products should display zero dollars.
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) 
Result:  78 records
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.


You may also like



Guides


vsn 3.1