BBampaa

Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

IS NULL and COALESCE() for missing data

IS NULL finds every spot in your data where a value was never recorded - nothing slipped in, not even a zero or a blank space, just pure emptiness. COALESCE then fills those gaps with a fallback you choose.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Finding and Filling Missing Retail Values

SELECT order_id, customer_id, COALESCE(discount, 0) AS discount, COALESCE(region, 'Unknown') AS region, ship_date
FROM orders

Result:

order_idcustomer_iddiscountregionship_date
O2001C10110.0Northeast2024-03-05
O2002C1020Southwest2024-03-06
O2003C1035.0Unknown2024-03-07
O2004C1040Midwest
O2005C10515.0Southeast2024-03-09

Step by step

  1. Run the query without COALESCE so you can see what the unfiltered values look like.
    SELECT order_id, customer_id, discount, COALESCE(region, 'Unknown') AS region, ship_date
FROM orders;
  2. COALESCE returns the first non-NULL value from the arguments.
    SELECT order_id, customer_id, COALESCE(discount, 0), COALESCE(region, 'Unknown') AS region, ship_date
FROM orders;
  3. AS gives the COALESCE result a friendly column name so the output reads cleanly.
    SELECT order_id, customer_id, COALESCE(discount, 0) AS discount, COALESCE(region, 'Unknown') AS region, ship_date
FROM orders;

Practice

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

1. Using customers, write a query that returns customer_id or 'Unknown' when customer_id is NULL.
SELECT COALESCE(customer_id, 'Unknown') FROM customers
2. Using products, write a query that returns brand or 'Unknown' when brand is NULL.
SELECT COALESCE(brand, 'Unknown') FROM products
3. Using orders, write a query that returns revenue or 0 when revenue is NULL.
SELECT COALESCE(revenue, 0) FROM orders
4. Using inventory, write a query that returns stock_count or 0 when stock_count is NULL.
SELECT COALESCE(stock_count, 0) FROM inventory
5. Using reviews, write a query that returns review_id or 'Unknown' when review_id is NULL.
SELECT COALESCE(review_id, 'Unknown') FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed