CASE Statement in MYSQL

In MySQL, the CASE statement is similar to other SQL database systems, allowing for conditional logic within queries. Here's the syntax of the CASE statement in MySQL along with an example:

SELECT

    order_id,

    order_date,

    CASE

        WHEN DATEDIFF(CURDATE(), order_date) <= 30 THEN 'Recent'

        WHEN DATEDIFF(CURDATE(), order_date) <= 90 THEN 'Within 3 Months'

        ELSE 'More than 3 Months Ago'

    END AS order_age

FROM

    orders;

In this example, suppose you have a table called orders with columns order_id and order_date. You want to categorize orders based on their age:

- If the order was placed within the last 30 days, it's categorized as "Recent".

- If the order was placed within the last 90 days but more than 30 days ago, it's categorized as "Within 3 Months".

- If the order was placed more than 90 days ago, it's categorized as "More than 3 Months Ago".

The CASE statement evaluates each condition sequentially and returns the result associated with the first condition that evaluates to true. If none of the conditions are met, the ELSE clause provides a default result.

In MySQL, CURDATE() function is used to get the current date, and DATEDIFF() function calculates the difference in days between two dates (order_date and the current date).

This example demonstrates how to use the CASE statement in MySQL for conditional logic within a SQL query. You can customize the conditions and result expressions based on your specific requirements.