SQL - Logical Functions

SQL - Conditional Logic Functions

SQL is not just for retrieving data; it’s also for transforming it. Conditional logic functions allow you to implement if-then-else style logic directly within your queries. This is incredibly powerful for creating derived columns, cleaning data, and handling special cases without needing to process the data in an application layer.

The CASE Statement

The CASE statement is the cornerstone of conditional logic in SQL and is part of the ANSI SQL standard, making it highly portable across different database systems. It evaluates a list of conditions and returns one of multiple possible result expressions.

Syntax

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

Example: Categorizing Products by Price

Imagine a Products table. We want to add a new, temporary column in our query result called PriceCategory.

SELECT
    ProductName,
    Price,
    CASE
        WHEN Price < 20.00 THEN 'Budget'
        WHEN Price >= 20.00 AND Price < 100.00 THEN 'Standard'
        WHEN Price >= 100.00 THEN 'Premium'
        ELSE 'Uncategorized'
    END AS PriceCategory
FROM Products;

This query will evaluate each product’s price and assign it to a category, making the report much more readable.

COALESCE: Returning the First Non-Null Value

The COALESCE function is a handy shortcut for handling NULL values. It accepts a list of arguments and returns the first one that is not NULL.

Syntax

COALESCE(value1, value2, ..., fallback_value)

Example: Finding a Contact Number

Suppose a Users table has separate columns for different phone numbers. We want to get the best available contact number for each user.

SELECT
    UserName,
    COALESCE(MobilePhone, HomePhone, WorkPhone, 'Not Available') AS ContactNumber
FROM Users;

This query will check MobilePhone first. If it’s NULL, it will check HomePhone. If that’s also NULL, it will check WorkPhone. If all are NULL, it will return the string ‘Not Available’.

NULLIF: Returning NULL if Two Expressions are Equal

The NULLIF function takes two arguments and returns NULL if they are equal. Otherwise, it returns the first argument. This is useful for preventing division-by-zero errors or for treating certain values as NULL.

Syntax

NULLIF(expression1, expression2)

Example: Safe Division

Let’s say we are calculating a ratio from two columns, ItemsSold and ItemsReturned. A value of 0 in ItemsSold would cause a division-by-zero error.

-- This could cause an error if ItemsSold is 0
-- SELECT ItemsReturned / ItemsSold FROM SalesMetrics;

-- Safe version using NULLIF
SELECT ItemsReturned / NULLIF(ItemsSold, 0) AS ReturnRatio
FROM SalesMetrics;

If ItemsSold is 0, NULLIF(ItemsSold, 0) becomes NULL. In SQL, any arithmetic operation with NULL results in NULL, gracefully avoiding the error.

Database-Specific Functions

While CASE, COALESCE, and NULLIF are standard, some databases offer their own functions. For example, SQL Server has IIF(condition, value_if_true, value_if_false), which is a compact alternative to a simple CASE statement. However, for maximum portability, it’s best to stick to the standard functions when possible.