Conditional Statements

By the end of this lesson, you will be able to transform raw data values into meaningful categories using SQL CASE expressions for conditional logic.

What it is

A CASE expression in SQL is a control-flow statement that allows you to return different values based on specific conditions. Think of it as an "if-then-else" logic block embedded directly within your query. It evaluates conditions sequentially and returns the result associated with the first condition that evaluates to true. If no conditions are met, it returns an optional default value defined by the ELSE clause.

Key related terms include conditional aggregation (using CASE inside aggregate functions like SUM or COUNT) and data binning (grouping continuous variables into discrete intervals).

Why it matters

• Data Cleaning: Standardize inconsistent text entries (e.g., mapping "M", "Male", and "m" all to "Male").
• Feature Engineering: Create new categorical columns from numerical data, such as grouping ages into "Teen," "Adult," and "Senior."
• Conditional Aggregation: Calculate metrics for subsets of data without filtering rows out entirely (e.g., calculating average sales only for completed orders).
• Readability: Replace complex nested subqueries with clear, linear logic within the SELECT clause.

Syntax or steps

The basic syntax follows this structure:

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

You can also use a simpler "simple CASE" format when comparing one column against multiple values:

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Example

Suppose you have a table named employees with a column salary. You want to categorize employees into salary bands.

SELECT 
    employee_id,
    salary,
    CASE
        WHEN salary < 50000 THEN 'Junior'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Mid-Level'
        WHEN salary > 80000 THEN 'Senior'
        ELSE 'Unknown'
    END AS salary_band
FROM employees;

Explanation:

• WHEN salary < 50000 THEN 'Junior': Checks if the salary is below 50k. If true, assigns 'Junior'.
• WHEN salary BETWEEN 50000 AND 80000...: Checks the middle range. Note that BETWEEN is inclusive.
• ELSE 'Unknown': Catches any nulls or unexpected values not covered by previous conditions.
• AS salary_band: Names the resulting computed column.

Common mistakes

• Ordering Issues: Conditions are evaluated top-to-bottom. If you check salary > 40000 before salary > 80000, high earners will incorrectly match the first condition. Always order from most specific to least specific.
• Missing END Keyword: Forgetting to close the expression with END causes syntax errors.
• Inconsistent Data Types: All results returned by THEN and ELSE must be compatible types (e.g., don't mix strings and integers unless implicit conversion is safe).
• NULL Handling: Comparisons involving NULL often evaluate to unknown/false. Use IS NULL explicitly rather than = NULL.

When to use it

Practice

Guided Exercise: Write a query that selects product_name and creates a column price_category where prices under $10 are 'Budget', $10-$50 are 'Standard', and over $50 are 'Premium'.

Challenge: Modify the query to count how many products fall into each category using CASE inside a SUM() function.

Hint for Challenge: Use SUM(CASE WHEN price < 10 THEN 1 ELSE 0 END).

Quick check

Question: What happens if none of the WHEN conditions are met and there is no ELSE clause?

Answer: The expression returns NULL.

Summary

SQL CASE expressions provide powerful row-level conditional logic essential for data transformation and analysis. Mastering their syntax and evaluation order allows analysts to clean data, engineer features, and perform complex aggregations efficiently within a single query.