CASE Statement in MARIADB

In MariaDB, the CASE statement functions similarly to other SQL database systems, providing conditional logic within queries. Below is the syntax of the CASE statement in MariaDB, 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, let's say we have a table named orders with columns order_id and order_date. We aim 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 MariaDB, CURDATE() function retrieves the current date, and DATEDIFF() function calculates the difference in days between two dates (order_date and the current date).

This example demonstrates the usage of the CASE statement in MariaDB for incorporating conditional logic within SQL queries. You can tailor the conditions and result expressions to suit your specific requirements.