COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expression 2 or returns a default set value.

In same way Coalesce will take the null values and replace with the given default value or act according to expression defined for null.

e.g:-

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050
   ORDER BY product_id, list_price, min_price, "Sale";

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5