Lessons › SQL › Deep Dive: Text, Date & Number Functions

SQL lesson

Null handling in calculations

Missing values in SQL are contagious - any calculation that touches one automatically produces another missing value instead of a number. Before substituting a zero or any stand-in, it's worth deciding what the missing data actually represents, because the right fallback is a business judgment, not a technical default.

Example

Handling NULLs in Retail Calculations

SELECT order_id, channel, revenue - COALESCE(discount, 0) AS net_revenue
FROM orders

Result:

Step by step

1. Run the query without COALESCE so you can see what the unfiltered values look like.

   SELECT order_id, channel, revenue - discount
   FROM orders;

2. COALESCE returns the first non-NULL value from the arguments.

   SELECT order_id, channel, revenue - COALESCE(discount, 0)
   FROM orders;

3. AS gives the COALESCE result a friendly column name so the output reads cleanly.

   SELECT order_id, channel, revenue - COALESCE(discount, 0) AS net_revenue
   FROM orders;

Practice

Try each one in your head first, then open the answer.

SELECT COALESCE(full_name, 0) + COALESCE(0, 0) FROM customers

SELECT COALESCE(price, 0) + COALESCE(0, 0) FROM products

SELECT COALESCE(revenue, 0) + COALESCE(0, 0) FROM orders

SELECT COALESCE(stock_count, 0) + COALESCE(0, 0) FROM inventory

SELECT COALESCE(rating, 0) + COALESCE(0, 0) FROM reviews