Advanced SQL Techniques - Conditional Logic

Conditional logic is a cornerstone of advanced SQL, allowing you to embed if-then-else style decision-making directly into your queries. This enables dynamic data transformation, custom categorization, and graceful handling of missing or specific values. The primary tools for conditional logic are the CASE statement and a family of helpful functions including COALESCE, NULLIF, and IIF.

---

The CASE Statement: The Powerhouse of SQL Logic

The CASE statement is the most versatile and widely supported method for implementing conditional logic. It comes in two flavors: simple and searched.

Simple CASE

A simple CASE statement compares a single expression against a series of distinct values. It's perfect for direct equality checks.

Syntax:

CASE input_expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Example: Let's categorize employees based on their job title.

SELECT
    employee_name,
    job_title,
    CASE job_title
        WHEN 'Manager' THEN 'Management'
        WHEN 'Developer' THEN 'Technical Staff'
        WHEN 'Analyst' THEN 'Technical Staff'
        ELSE 'Support Staff'
    END AS job_category
FROM
    employees;

In this example, the job_title column is evaluated. If it matches 'Manager', 'Management' is returned. If it's 'Developer' or 'Analyst', 'Technical Staff' is returned. For all other titles, 'Support Staff' is the result.

Searched CASE

The searched CASE statement is more powerful, allowing you to evaluate a series of independent boolean expressions (<, >, LIKE, IN, etc.). The first condition that evaluates to true determines the result.

Syntax:

SQL

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

Example: Let's group products into price tiers based on their cost.

SELECT
    product_name,
    price,
    CASE
        WHEN price > 1000 THEN 'Premium'
        WHEN price BETWEEN 250 AND 1000 THEN 'Standard'
        WHEN price < 250 THEN 'Economy'
        ELSE 'Not Priced'
    END AS price_tier
FROM
    products;

Here, each product's price is checked against a series of conditions. A product costing $1500 would be labeled 'Premium', while one at $500 would be 'Standard'.

---

Specialized Conditional Functions

While CASE is the all-rounder, SQL provides several other functions for more specific scenarios.

COALESCE: Finding the First Non-Null Value

The COALESCE function takes a list of expressions and returns the first one that is not NULL. This is incredibly useful for providing default or fallback values.

Syntax:

COALESCE(expression1, expression2, ..., default_value)

Example: Imagine a contacts table where a person might have a mobile number, a home number, or no number listed. We want to display the best available number.

SELECT
    contact_name,
    COALESCE(mobile_phone, home_phone, 'Not Available') AS primary_contact_number
FROM
    contacts;

If mobile_phone is not NULL, it's returned. Otherwise, it checks home_phone. If both are NULL, it returns the default string 'Not Available'.

NULLIF: Returning NULL on Equality

The NULLIF function compares two expressions. If they are equal, it returns NULL. If they are not equal, it returns the first expression. This is often used to prevent errors like division by zero or to handle specific data-entry conventions where a value should be treated as missing.

Syntax:

NULLIF(expression1, expression2)

Example: You have a dataset where a product's stock is marked as 0 when it's out of stock. To avoid a "division by zero" error when calculating the average price per unit in stock, you can use NULLIF.

SELECT
    product_name,
    total_value / NULLIF(stock_quantity, 0) AS avg_price_per_unit
FROM
    inventory;

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

IIF: A Compact IF-THEN-ELSE

The IIF function is a concise way to write a simple CASE statement. It evaluates a boolean expression and returns one of two values. Note that IIF is specific to some SQL dialects like SQL Server and Access, while CASE is universally supported.

Syntax:

IIF(boolean_expression, value_if_true, value_if_false)

Example: Quickly flag orders as 'Shipped' or 'Pending'.

SELECT
    order_id,
    IIF(shipped_date IS NOT NULL, 'Shipped', 'Pending') AS shipping_status
FROM
    orders;

This is functionally equivalent to:

CASE
    WHEN shipped_date IS NOT NULL THEN 'Shipped'
    ELSE 'Pending'
END

By mastering these conditional logic techniques, you can write more robust, flexible, and intelligent SQL queries that transform raw data into meaningful insights.