COALESCE

🎬 Scenario: Managing Missing Data in Amazon Orders

Your manager at Amazon, Sophia, noticed that some orders are missing delivery dates and some total spending values are NULL.

Can you ensure that missing delivery dates display ‘Not Delivered’ and that NULL spending amounts default to 0?

Your task is to use COALESCE() to replace missing values and NULLIF() to clean up inconsistent data. Use the amazon_orders and amazon_customers tables to address the questions.

amazon_orders table:

amazon_customers table:

1. Using COALESCE() to Replace NULLs

The COALESCE() function returns the first non-NULL value from a list of arguments.

Syntax

1SELECT COALESCE(column_name, default_value) FROM table_name;

• If column_name is NULL, COALESCE() returns default_value.
• If column_name is not NULL, the original value is returned.

Example Query: Handling Missing Delivery Dates

1SELECT order_id, customer_id,  
2       COALESCE(CAST(delivery_date AS VARCHAR), 'Not Delivered') AS formatted_delivery_date  
3FROM amazon_orders  
4ORDER BY order_id;

What’s Happening?

• COALESCE(delivery_date, 'Not Delivered') replaces NULL delivery dates with "Not Delivered".

⚠️ Important Note:

• All values passed to COALESCE() must be of the same data type.
• If total_spent is a DECIMAL or FLOAT, ensure the replacement value (0) is also of the same type (e.g., 0.00 instead of 0 in some databases).
• Mismatched types can cause errors in some SQL engines.

2. Using COALESCE() to Replace NULL Financial Values

Sophia wants to ensure that all missing total_spent values are displayed as $0 instead of NULL. This is important for financial reports where missing values should be treated as zero rather than unknown.

Example Query: Handling NULL Customer Spending

1SELECT customer_id, name,  
2       COALESCE(total_spent, 0) AS adjusted_total_spent  
3FROM amazon_customers  
4ORDER BY customer_id;

What’s Happening?

• COALESCE(total_spent, 0) replaces any NULL values with 0.

3. Using NULLIF() to Handle Inconsistent Data

The NULLIF() function returns NULL if two values are equal, otherwise it returns the first value.

Syntax

1SELECT NULLIF(value1, value2) FROM table_name;

• If value1 = value2, NULL is returned.
• Otherwise, value1 is returned.

Example Query: Handling Zero Total Spending

1SELECT customer_id, name,  
2       NULLIF(total_spent, 0) AS valid_total_spent  
3FROM amazon_customers  
4ORDER BY customer_id;

What’s Happening?

• NULLIF(total_spent, 0) replaces 0 with NULL to avoid including customers who haven’t spent anything.

✍️ SQL Exercises

Exercise 1: Replacing NULL Delivery Dates

Sophia needs a report that ensures missing delivery dates display ‘Not Delivered’.

Filter condition:

• Retrieve order ID, customer ID, and formatted delivery date.
• Replace NULL delivery dates with "Not Delivered".
• Sort by order_id in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Handling Zero Total Spending

Sophia also wants to replace zero total spending values with NULL to differentiate customers who haven’t made purchases.

Filter condition:

• Retrieve customer ID, name, and valid total spent.
• Replace 0 values with NULL.
• Sort by customer_id in ascending order.

Write an SQL query to return the requested data.