SQL CASE Expression

The CASE expression in SQL is a powerful tool that allows you to add conditional logic to your queries. It is similar to an IF-THEN-ELSE statement in programming languages.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

Example: Basic Usage

SELECT order_id, 
    CASE
        WHEN quantity > 30 THEN 'Large'
        WHEN quantity BETWEEN 10 AND 30 THEN 'Medium'
        ELSE 'Small'
    END AS order_size
FROM Orders;

Example: Using CASE in WHERE Clause

SELECT customer_id, customer_name, country
FROM Customers
WHERE country = 
    CASE
        WHEN customer_id = 1 THEN 'Germany'
        WHEN customer_id = 2 THEN 'Mexico'
        ELSE 'UK'
    END;

Sample Tables

Consider the following Orders table:

Orders:
| order_id | product_id | quantity |
|----------|------------|----------|
| 1        | 101        | 5        |
| 2        | 102        | 25       |
| 3        | 103        | 35       |

Result of CASE Expression

After running the first example query, the result would look like this:

| order_id | order_size |
|----------|------------|
| 1        | Small      |
| 2        | Medium     |
| 3        | Large      |

Important Notes

• The CASE expression must end with END.
• You can use CASE in both SELECT and WHERE clauses.
• If no conditions are met, the ELSE part will be executed. If ELSE is not provided and no conditions are met, the result will be NULL.