At least one condition must be true.

8️⃣ CASE WHEN with Aggregation

Here's where CASE WHEN becomes extremely powerful.

Suppose you want to count high-value orders.

You can write:

SELECT
    COUNT(
        CASE
            WHEN Sales >= 100000 THEN 1
        END
    ) AS High_Value_Orders
FROM Orders;

This counts only orders where Sales is at least ₹100,000.

9️⃣ Conditional SUM

Suppose you want:

Total sales from high-value orders.

Use:

SELECT
    SUM(
        CASE
            WHEN Sales >= 100000 THEN Sales
            ELSE 0
        END
    ) AS High_Value_Sales
FROM Orders;

This calculates sales only for qualifying orders.

This technique is called conditional aggregation.

🔟 Conditional Aggregation by Region

Suppose you want to compare:

North sales

South sales

in the same result.

SELECT
    SUM(
        CASE
            WHEN Region = 'North' THEN Sales
            ELSE 0
        END
    ) AS North_Sales,

    SUM(
        CASE
            WHEN Region = 'South' THEN Sales
            ELSE 0
        END
    ) AS South_Sales
FROM Orders;

Result:

North_Sales | South_Sales

500,000 | 350,000

This is extremely useful when building analytical reports.

1️⃣1️⃣ CASE WHEN with GROUP BY

You can create categories and then aggregate them.

For example:

SELECT
    CASE
        WHEN Sales >= 100000 THEN 'High'
        WHEN Sales >= 50000 THEN 'Medium'
        ELSE 'Low'
    END AS Sales_Category,
    COUNT(*) AS Order_Count
FROM Orders
GROUP BY
    CASE
        WHEN Sales >= 100000 THEN 'High'
        WHEN Sales >= 50000 THEN 'Medium'
        ELSE 'Low'
    END;

This tells you how many orders belong to each sales category.

1️⃣2️⃣ What Is NULL?

NULL represents missing or unknown information.

It is important to understand:

NULL is not the same as zero.

For example:

Salary = 0

means the salary value is explicitly zero.

But:

Salary = NULL

means the value is missing or unknown.

Similarly:

Discount = NULL

doesn't necessarily mean:

Discount = 0

It means:

No value is available.

1️⃣3️⃣ NULL Is Not an Empty String

These are different:

NULL

''

' '

0

NULL

Missing/unknown value.

Empty string

A text value containing no characters.

Space

A string containing a space.

Zero

A numeric value equal to zero.

This distinction is extremely important when cleaning data.

1️⃣4️⃣ Don't Use = NULL

A common beginner mistake is:

WHERE Email = NULL

This is incorrect for testing NULL.

Instead, use:

WHERE Email IS NULL

To find non-NULL values:

WHERE Email IS NOT NULL

1️⃣5️⃣ Find Missing Values

Suppose you want customers whose phone numbers are missing:

SELECT *
FROM Customers
WHERE Phone IS NULL;

This is useful for data-quality analysis.

1️⃣6️⃣ Count Missing Values

You can use conditional aggregation:

SELECT
    COUNT(*) AS Total_Customers,
    COUNT(
        CASE
            WHEN Phone IS NULL THEN 1
        END
    ) AS Missing_Phone
FROM Customers;

Now you can see:

Total_Customers | Missing_Phone

10,000 | 350

So:

350 customers have missing phone numbers.

1️⃣7️⃣ COALESCE()

COALESCE() returns the first non-NULL value.

For example:

SELECT
    Customer_Name,
    COALESCE(Phone, 'Not Available') AS Phone
FROM Customers;