Mastering NULLs Like a Pro: COALESCE vs IFNULL vs NULLIF in Action
NULL is a critical concept in databases, signifying the absence or unknown nature of data. Its significance lies in its role in maintaining data integrity and handling missing or uncertain information gracefully.
In this blog, we’ll explore three key NULL functions that play a pivotal role in efficiently handling and managing null values in MySQL.
- COALESCE -Returns the first not NULL column value
- IFNULL -Returns the second column value when the first column is NULL
- NULLIF -Returns NULL if the first value matches the given condition.
Let’s understand these functions with the help of the dataset.
-- Below script works on MySQL
CREATE database onlineretail;
use onlineretail;
CREATE TABLE order_details (
order_id INT PRIMARY KEY,
product_name VARCHAR(100),
unit_price DECIMAL(10, 2),
quantity INT,
discount_amount INT,
cashback_amount INT
);
/*
order_id: Unique identifier for each order
product_name: Name of the purchased product
unit_price: Unit price of the product
quantity: Quantity of the product ordered
discount_amount: Discount applied to the product
cashback_amount: Portion of the purchase amount to the buyer after the transaction is completed if customer is loyal
Order can be eligible for either cashback or discount or both
*/
INSERT INTO order_details (order_id, product_name, unit_price, quantity,
discount_amount, cashback_amount)
VALUES
(1, 'Laptop', 1200.00, 2, 50,NULL),
(2, 'Smartphone', 600.00, 3, NULL,100),
(3, 'Headphones', 80.00, 5, 50,150),
(4, 'Tablet', 400.00, 1, 300,NULL),
(5, 'Camera', 900.00, 2, NULL,NULL),
(6, 'Unknown', 400.00, 1, NULL,NULL);
select * from order_details;
Coalesce
1.Calculate the total price of the product excluding the cashback
SELECT
order_id,
product_name,
unit_price,
quantity,
discount_amount,
-- Cashback is excluded from the total price calculation
((unit_price * quantity) - discount_amount) AS total_price_without_coalesce,
((unit_price * quantity) - COALESCE(discount_amount, 0)) AS total_price_with_coalesce
FROM
order_details;
The purpose of this query is to calculate the total price for each product in an order, factoring in the unit price, quantity, and discount amount.
Total Price = (unit_price * quantity)-(discount + cashback*)
total_price_without_coalesce -If COALESCE is not applied, the total_price comes to be NULL if the discount_amount is NULL
total_price_with_coalesce -The COALESCE function is used to ensure that if the discount amount is missing or null, it is treated as zero in the calculations for the second total price.
2.In the order details table, are customers saving more money through cashback or discounts?
SELECT
discount_amount,
cashback_amount,
#returns first not null value from discount and cashback column) -Highlighted with yellow.
COALESCE(discount_amount, cashback_amount) AS total_savings_with_coalesce,
#returns first not null value from discount and cashback column) and if NULL values are found, set to 0 -Highlighted in blue
COALESCE(discount_amount, cashback_amount, 0) AS total_savings_with_coalesce_set_to_zero,
#Sets discount value and cashback to zero if NULL is found else finds greatest value among them - Highlighted in green.
(GREATEST(COALESCE(discount_amount, 0),
COALESCE(cashback_amount, 0))) AS total_max_discount_or_cashback
FROM
order_details;
total_savings_with_coalesce (Yellow): Calculates savings by taking the first non-null value between discount_amount and cashback_amount.
total_savings_with_coalesce_set_to_zero (Blue): Calculates savings similarly but sets the result to 0 if both discount_amount and cashback_amount are NULL.
total_max_discount_or_cashback(Green): Calculates savings by selecting the greater value between discount_amount and cashback_amount, even if one of them is NULL.
3. Calculate the total price of each product
SELECT
*,
((unit_price * quantity) - (discount_amount + cashback_amount)) AS total_price_without_coalesce,
((unit_price * quantity) - (COALESCE(discount_amount, 0) + COALESCE(cashback_amount, 0))) AS total_price_with_coalesce
FROM
order_details;
total_price_without_coalesce -If COALESCE is not applied, the total_price comes to be NULL if the discount_amount or cashback_amount is NULL
total_price_with_coalesce -The COALESCE function is used to ensure that if the discount amount or cashback_amount is missing or null, it is treated as zero in the calculations for the second total price.
IFNULL
We can implement COALESCE like functionality with IFNULL function.
- Calculate the total price of the product excluding the cashback
SELECT
order_id,
product_name,
unit_price,
quantity,
discount_amount,
((unit_price * quantity) - discount_amount) AS total_price_without_ifnull,
((unit_price * quantity) - IFNULL(discount_amount, 0)) AS total_price_with_ifnull
FROM
order_details;
total_price_without_ifnull: Calculates the total price by simply subtracting the discount amount from the product of unit price and quantity, and if the discount amount is null, the result remains null.
total_price_with_ifnull: Calculates the total price for each item but uses the IFNULL function to treat null values in the discount amount as 0. This ensures a numerical result, even if the discount amount is missing.
2.In the order details table, are customers saving more money through cashback or discounts?
SELECT
discount_amount,
cashback_amount,
#returns first not null value from discount and cashback column)- Highlighted in yellow.
IFNULL(discount_amount, cashback_amount) AS total_savings_with_ifnull,
#returns first not null value from discount and cashback column) and if NULL values are found, set to 0-Highlighted in blue
IFNULL(discount_amount,IFNULL( cashback_amount, 0)) AS total_savings_with_ifnull_set_to_zero,
#Sets discount value and cashback to zero if NULL is found else finds greatest value among them - Highlighted in green.
(GREATEST(IFNULL(discount_amount, 0),
IFNULL(cashback_amount, 0))) AS total_max_discount_or_cashback
FROM
order_details;
total_savings_with_ifnull (yellow): Calculates savings by selecting the first non-null value between "discount_amount" and "cashback_amount" using the IFNULL function.
total_savings_with_ifnull_set_to_zero (blue): Computes savings but sets the result to 0 if both "discount_amount" and "cashback_amount" are null, making use of nested IFNULL functions.
total_max_discount_or_cashback (green): Calculates savings by taking the greater value between "discount_amount" and "cashback_amount" and, if either of them is null, treating it as 0.
3. Calculate the total price of each product
SELECT
*,
((unit_price * quantity) - (discount_amount + cashback_amount)) AS total_price_without_ifnull,
((unit_price * quantity) - (IFNULL(discount_amount, 0) + IFNULL(cashback_amount, 0))) AS total_price_with_ifnull
FROM
order_details;
total_price_without_ifnull : Calculates the total price for each item by subtracting the sum of "discount_amount" and "cashback_amount" from the product of "unit_price" and "quantity."
total_price_with_null: Calculates the total price for each item. However, it uses the IFNULL function to treat potential null values in the "discount_amount" and "cashback_amount" as 0. This ensures that the result is a numerical value, even if these columns contain null values.
NULLIF
# Apply NULLIF on Product name if ‘unknown’, then do Product name as NULL
select * from order_details;
SELECT
*, NULLIF(product_name, 'unknown') AS enriched_product_name
FROM
order_details;
In the SQL query, we are selecting all records from the “order_details” table. However, you are using the NULLIF function to create a new column named "enriched_product_name." This function checks if the "product_name" is equal to 'unknown,' and if it is, it replaces it with NULL; otherwise, it keeps the original value.
If you enjoyed this article and would like to stay updated with my latest insights, connect with me on LinkedIn and follow me on GitHub.