MySQL SELECT CASE: Usage Guide

1 year ago
Emily Johnson
1 minute

In MySQL, the CASE statement in a SELECT query is used to return different values based on conditions. The general syntax format is as follows:

SELECT 
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS alias_name
FROM table_name;

Condition1, condition2, and so on are logical conditions, while result1, result2, and so on are the values returned when the conditions match. Default_result is the default value returned when none of the conditions match, and alias_name is the alias for the returned result.

The following is an example:

Let’s say we have a table named ‘products’ with columns ‘product_id’ and ‘product_price’. Now we want to return the corresponding price range based on the product price.

SELECT 
    product_id,
    product_price,
    CASE
        WHEN product_price < 50 THEN 'Cheap'
        WHEN product_price BETWEEN 50 AND 100 THEN 'Moderate'
        WHEN product_price > 100 THEN 'Expensive'
        ELSE 'Unknown'
    END AS price_range
FROM products;

In this example, return the corresponding price range based on different product prices. If the product price is less than 50, return ‘Cheap’; if the product price is between 50 and 100, return ‘Moderate’; if the product price is greater than 100, return ‘Expensive’; otherwise return ‘Unknown’.

bannerAds