# 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:**

```sql
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.

```sql
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

```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.

```sql
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:**

```sql
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.

```sql
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:**

```sql
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`.

```sql
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:**

```sql
IIF(boolean_expression, value_if_true, value_if_false)
```

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

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

This is functionally equivalent to:

```sql
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.