SQL Advanced Functions - CASE, COALESCE, and NULLIF Explained

Introduction

SQL provides advanced functions like CASE, COALESCE, and NULLIF that are essential for handling conditional logic, null values, and value comparisons efficiently. These functions allow you to write more dynamic and robust queries for real-world applications. In this article, we’ll explore these powerful tools with practical examples.

1. CASE: Conditional Logic in Queries

The CASE function allows you to perform conditional logic directly in SQL queries, similar to IF-THEN-ELSE in programming.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

Use Cases:
• Transforming values based on conditions.
• Creating calculated columns.
• Conditional aggregation.

Example 1: Categorizing Sales Amounts

Task: Classify sales as “High”, “Medium”, or “Low” based on the Amount.

Query:

SELECT SaleID, Amount,
    CASE
        WHEN Amount > 1000 THEN 'High'
        WHEN Amount BETWEEN 500 AND 1000 THEN 'Medium'
        ELSE 'Low'
    END AS SaleCategory
FROM Sales;

Result:

SaleID	Amount	SaleCategory
1	1200	High
2	800	Medium
3	400	Low

Example 2: Conditional Aggregation

Task: Calculate the total sales for high-value and low-value sales separately.

Query:

SELECT
    SUM(CASE WHEN Amount > 1000 THEN Amount ELSE 0 END) AS HighValueSales,
    SUM(CASE WHEN Amount <= 1000 THEN Amount ELSE 0 END) AS LowValueSales
FROM Sales;

Result:

HighValueSales	LowValueSales
2200	1200

2. COALESCE: Handling Null Values

The COALESCE function returns the first non-null value from a list of expressions. It is particularly useful for handling NULL values in data.

Syntax:

COALESCE(expression1, expression2, ..., expressionN)

Use Cases
• Providing default values for nulls.
• Combining multiple columns or expressions.

Example 1: Replacing Null Values with Defaults

Task: Replace null Discount values with 0.

Query:

SELECT SaleID, Amount, COALESCE(Discount, 0) AS FinalDiscount
FROM Sales;

Result:

SaleID	Amount	FinalDiscount
1	1200	50
2	800	0
3	400	10

Example 2: Combining Multiple Columns

Task: Display the primary contact number for each customer, using the mobile number if available, or the landline number otherwise.

Query:

SELECT CustomerID,
    COALESCE(MobileNumber, LandlineNumber, 'No Contact Available') AS PrimaryContact
FROM Customers;

Result:

CustomerID	PrimaryContact
1	123-456-7890
2	555-555-5555
3	No Contact Available

3. NULLIF: Comparing Values for Equality

The NULLIF function returns NULL if two expressions are equal; otherwise, it returns the first expression. It’s useful for avoiding divide-by-zero errors or flagging redundant data.

Syntax:

NULLIF(expression1, expression2)

Use Cases:
• Preventing divide-by-zero errors.
• Simplifying comparisons.

Example 1: Avoiding Divide-by-Zero Errors

Task: Calculate the profit margin as Profit / Revenue, but avoid division by zero.

Query:

SELECT ProductID, Revenue, Profit,
    Profit / NULLIF(Revenue, 0) AS ProfitMargin
FROM Financials;

Result:

ProductID	Revenue	Profit	ProfitMargin
101	1000	200	0.2
102	0	0	NULL

Example 2: Flagging Redundant Data

Task: Identify rows where ActualValue matches ExpectedValue.

Query:

SELECT RecordID, ActualValue, ExpectedValue,
    CASE
        WHEN NULLIF(ActualValue, ExpectedValue) IS NULL THEN 'Match'
        ELSE 'Mismatch'
    END AS ComparisonResult
FROM Records;

Result:

RecordID	ActualValue	ExpectedValue	ComparisonResult
1	50	50	Match
2	40	50	Mismatch

Summary of Functions

Function	Purpose	Example Use Case
CASE	Conditional logic within queries.	Categorizing data based on conditions.
COALESCE	Returns the first non-null value.	Replacing nulls with default values.
NULLIF	Returns NULL if two values are equal.	Avoiding divide-by-zero errors.

Conclusion

Understanding and leveraging advanced SQL functions like CASE, COALESCE, and NULLIF can significantly improve the versatility and efficiency of your queries. These functions enable you to handle conditional logic, null values, and comparisons more effectively. Practice these examples in your projects to master their usage!

---

Explore more

Luca LiuFollow

Hello there! 👋 I'm Luca, a Business Intelligence Developer with passion for all things data. Proficient in Python, SQL, Power BI, Tableau, SAP Business Objects.

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

🚀 Connect with me on LinkedIn