If Phone is NULL, SQL returns:
Not Available
Otherwise, it returns the actual phone number.
1️⃣8️⃣ COALESCE() with Multiple Options
You can provide multiple alternatives.
SELECT
COALESCE(Work_Email, Personal_Email, 'No Email')
AS Contact_Email
FROM Customers;
SQL checks:
1. Work Email
2. Personal Email
3. "No Email"
It returns the first non-NULL value.
This is extremely useful when combining multiple possible sources of information.
1️⃣9️⃣ NULLIF()
NULLIF() returns NULL if two expressions are equal.
For example:
NULLIF(Sales, 0)
If Sales is:
0
the result becomes:
NULL
Otherwise, the original Sales value is returned.
2️⃣0️⃣ Why NULLIF() Is Useful
Suppose you're calculating:
Profit Margin = Profit / Sales
If Sales is zero:
Profit / Sales
could cause a division-by-zero error.
You can use:
SELECT
Profit / NULLIF(Sales, 0) AS Profit_Margin
FROM Orders;
If Sales = 0:
NULLIF(0,0) → NULL
So the division doesn't attempt to divide by zero.
This is an important practical technique.
2️⃣1️⃣ CASE WHEN + NULL
You can also explicitly handle missing values.
SELECT
Customer_Name,
CASE
WHEN Phone IS NULL THEN 'Missing'
ELSE 'Available'
END AS Phone_Status
FROM Customers;
Result:
Customer_Name | Phone_Status
John | Available
Sarah | Missing
Mike | Available
This is useful for data-quality reports.
2️⃣2️⃣ Categorize Customers
Suppose you want to classify customers based on total spending:
₹1,00,000+ → VIP
₹50,000+ → Premium
₹20,000+ → Standard
Below ₹20,000 → Basic
After calculating customer-level sales, you could use:
CASE
WHEN Total_Sales >= 100000 THEN 'VIP'
WHEN Total_Sales >= 50000 THEN 'Premium'
WHEN Total_Sales >= 20000 THEN 'Standard'
ELSE 'Basic'
END
This type of segmentation is widely used in business analytics.
2️⃣3️⃣ CASE WHEN for KPI Status
Suppose the target is:
₹10,00,000
and actual sales are stored in Total_Sales.
You could create:
CASE
WHEN Total_Sales >= 1000000 THEN 'Target Achieved'
ELSE 'Below Target'
END
This turns a raw number into a business interpretation.
2️⃣4️⃣ CASE WHEN for Profitability
Suppose:
Profit > 0 → Profitable
Profit = 0 → Break-even
Profit < 0 → Loss
Use:
CASE
WHEN Profit > 0 THEN 'Profitable'
WHEN Profit = 0 THEN 'Break-even'
ELSE 'Loss'
END AS Profit_Status
This is a simple but powerful analytical transformation.
2️⃣5️⃣ CASE WHEN for Data Cleaning
Suppose your dataset contains:
India
INDIA
india
IN
You can standardize values with a CASE expression:
CASE
WHEN Country IN ('India', 'INDIA', 'india', 'IN')
THEN 'India'
ELSE Country
END AS Standardized_Country
For a small number of known inconsistencies, this can be useful.
For larger or recurring transformations, you may want to handle standardization upstream in your data pipeline.
2️⃣6️⃣ A Powerful Interview Pattern
You will frequently encounter queries like:
SELECT
Region,
SUM(Sales) AS Total_Sales,
CASE
WHEN SUM(Sales) >= 1000000
THEN 'Target Achieved'
ELSE 'Below Target'
END AS Target_Status
FROM Orders
GROUP BY Region;
