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;