COALESCE()

COALESCE() scans a list of values and returns the first one that isn't NULL.

About 5–10 minutes · no sign-up needed

Example

Replacing Missing Retail Discounts with COALESCE()

SELECT order_id, customer_id, COALESCE(discount, 0) AS discount
FROM orders

Result:

Run the query without COALESCE so you can see what the unfiltered values look like.
```
SELECT order_id, customer_id, discount
FROM orders;
```

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

SELECT order_id, customer_id, COALESCE(discount, 0)
FROM orders;

AS gives the COALESCE result a friendly column name so the output reads cleanly.
```
SELECT order_id, customer_id, COALESCE(discount, 0) AS discount
FROM orders;
```

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

1. Using customers, write a query that shows signup_date but replaces NULL values with 0.

SELECT COALESCE(signup_date, 0) FROM customers

2. Using products, write a query that shows brand but replaces NULL values with 'Unknown'.

SELECT COALESCE(brand, 'Unknown') FROM products

3. Using orders, write a query that shows customer_id but replaces NULL values with 'Unknown'.

SELECT COALESCE(customer_id, 'Unknown') FROM orders

4. Using inventory, write a query that shows warehouse but replaces NULL values with 'Unknown'.

SELECT COALESCE(warehouse, 'Unknown') FROM inventory

5. Using reviews, write a query that shows product_id but replaces NULL values with 'Unknown'.

SELECT COALESCE(product_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.

About 5–10 minutes · no sign-up needed