The COALESCE function takes any number of arguments and returns the first one that is not null. However, if all arguments are null then the function itself returns null. It is often used when working with a table that contains null values and we are attempting to substitute with something else.
Basic Syntax
SELECT COALESCE (1,2) -- returns 1
SELECT COALESCE (NULL, 2, 3) -- returns 2Example 1
Suppose we have the following table:
| ITEM | PRICE | DISCOUNT |
|---|---|---|
| A | 100 | 20 |
| B | 300 | null |
| C | 200 | 10 |
The data shows the price of each item in dollars, and the discounted amount in dollars. Suppose, then, that we want to see a table with final prices (that is to say, the price of an item minus the discount). In this scenario, we can try a SQL query that simply does something like select (price - discount) from table. |
The issue is that that query will result in a null value for item B, since null cannot be subtracted from zero. The solution is to use COALESCE to substitute null for a 0:
SELECT item, (price - COALESCE(discount,0)) AS final
FROM tableExample 3
Related Notes
- SQL Insert – How to add new rows to a table.
- SQL Delete – Removing rows from a table.
- SQL Transactions and Errors – Grouping statements to maintain atomicity.
- SQL select distinct – Retrieving data from tables.
- SQL nullif