Consider a scenario where you have a product catalog in a database.
Each product might have multiple fields storing the potential price:
- ListPrice
- DiscountedPrice
- SpecialOfferPrice
These prices might be NULL if they're not applicable.
The Problem
Determine the actual selling price of a product by checking these price sources in a specific order.
The first thing you might think of is a CASE. But there is a more elegant way to solve this problem: COALESCE.
COALESCE is useful when you have multiple potential sources for a value and want to use the first available non-null one.
This is how it works:
-
This function evaluates the columns or expressions listed in its arguments from left to right.
-
It starts with SpecialOfferPrice, then moves to DiscountedPrice, and finally, if both are null, it uses ListPrice.
-
COALESCE returns the first non-null value among its arguments. Be careful, MSSQL will throw an error if all arguments are null.
-
For clarity in the output, use the alias SellingPrice as the result of the COALESCE function.
COALESCE simplifies the logic compared to using a series of CASE statements.
It's straightforward and easy to read.
Have you ever used it?