Conditional Expression Syntax

MySQL supports two forms of conditional expressions using the CASE construct for implementing decision-making logic within queries.

Simple CASE Based on Column Values

The simple form compares a specific column's value against predefined constants:

CASE column_expression
    WHEN constant_value_1 THEN return_value_1
    WHEN constant_value_2 THEN return_value_2
    ...
    [ELSE fallback_result]
END

When the column matches any defined constant, the corresponding result is returned. If no match occurs and a ELSE clause exists, that default value is used; otherwise, NULL is returned.

Example implementation converting user account states:

SELECT 
    user_id,
    CASE account_state
        WHEN 'enabled' THEN 'Active User'
        WHEN 'disabled' THEN 'Inactive User'
        ELSE 'Status Unknown'
    END AS user_status_description
FROM customer_accounts;

Searched CASE with Boolean Conditions

This variant evaluates arbitrary boolean expressions rather then direct value comparisons:

CASE
    WHEN boolean_condition_1 THEN outcome_1
    WHEN boolean_condition_2 THEN outcome_2
    ...
    [ELSE default_outcome]
END

Each condition can involve multiple columns, functions, or operators to determine the appropriate result.

Example categorizing individuals by age ranges:

SELECT 
    person_id,
    full_name,
    CASE
        WHEN years_old >= 18 THEN 'Adult'
        WHEN years_old BETWEEN 6 AND 17 THEN 'Minor'
        ELSE 'Child'
    END AS demographic_category
FROM population_data;

Implemantation Considerations

• Multiple CASE expressions may be nested for handling intricate business rules
• Return values support all data types including numbers, text, dates, and temporal values
• Expressions following THEN can include calculations, function calls, or subqueries
• The ELSE branch is optional but recommended to prevent unexpected NULL results
• These constructs work in SELECT projections, WHERE filters, ORDER BY clauses, GROUP BY operations, and HAVING conditions

Practical Use Cases

• Transforming raw data into standardized formats during reporting
• Performing conditional aggregations like counting records meeting certain criteria
• Implementing row-level access controls based on dynamic evaluation
• Executing real-time business computations such as tiered pricing models or performance grading systems