Snippet

Handling Null Values Safely with COALESCE

The ANSI SQL standard COALESCE function evaluates arguments sequentially from left to right and returns the first non-NULL value. It prevents unexpected NULL results in calculations and reporting.

snippet.sql

sql

SELECT
  product_id,
  product_name,
  COALESCE(discount_price, list_price, 0.00) AS effective_price
FROM products;

Breakdown

COALESCE(discount_price, list_price, 0.00) AS effective_price

Evaluates arguments in left-to-right order and returns the first non-NULL value.

discount_price,

First priority price column checked for existence of a value.

list_price,

Fallback price evaluated when discount_price contains NULL.

0.00)

Default non-NULL literal returned if all prior expressions evaluate to NULL.

Previous snippet Next snippet

More Intermediate SQL exercises →

Handling Null Values Safely with COALESCE

Related