Handling Missing Product Prices with COALESCE
In real-world databases, it's common for some data fields to be missing. This can lead to unexpected results in queries, especially when performing calculations or displaying information. This challenge focuses on using the COALESCE function to gracefully handle NULL values in a product catalog, ensuring that our queries always produce meaningful results.
Problem Description
You are tasked with querying a Products table that contains information about various products, including their names and prices. However, some products might not have a price recorded, resulting in NULL values in the price column. You need to write a SQL query that retrieves the product name and its price. If a product's price is NULL, you should display a default value of 0.00 instead. This ensures that all products are represented with a numerical price, even if it's zero, making subsequent analysis or display much simpler.
Key Requirements:
- Retrieve the
product_namefrom theProductstable. - Retrieve the
pricefrom theProductstable. - If the
priceisNULL, substitute it with0.00. - The query should be efficient and handle potentially large datasets.
Expected Behavior:
The output should be a list of all products, each with its name and its corresponding price. Products with a recorded price will show that price, while products with a NULL price will show 0.00.
Examples
Example 1:
Input:
Products table:
| product_id | product_name | price |
|---|---|---|
| 1 | Laptop | 1200.50 |
| 2 | Keyboard | 75.00 |
| 3 | Mouse | NULL |
Output:
| product_name | display_price |
|---|---|
| Laptop | 1200.50 |
| Keyboard | 75.00 |
| Mouse | 0.00 |
Explanation:
For 'Laptop' and 'Keyboard', their existing prices are displayed. For 'Mouse', since the price is NULL, COALESCE substitutes it with 0.00.
Example 2:
Input:
Products table:
| product_id | product_name | price |
|---|---|---|
| 4 | Monitor | 300.00 |
| 5 | Webcam | NULL |
| 6 | Speakers | 150.75 |
| 7 | Microphone | NULL |
Output:
| product_name | display_price |
|---|---|
| Monitor | 300.00 |
| Webcam | 0.00 |
| Speakers | 150.75 |
| Microphone | 0.00 |
Explanation:
Similar to Example 1, NULL prices for 'Webcam' and 'Microphone' are replaced with 0.00.
Constraints
- The
Productstable will contain at least one row. product_namewill always be a non-NULLstring.pricecan be a decimal number orNULL.- The query should execute within typical database performance expectations for such an operation.
Notes
The COALESCE function in SQL returns the first non-NULL expression from its list of arguments. Consider what arguments you need to provide to COALESCE to achieve the desired output. You will likely need to alias the resulting column to something descriptive, like display_price.