CASE Statement in MARIADB

In MariaDB, the CASE statement is used similarly to other SQL-based databases like MySQL and MSSQL. It provides conditional logic within SQL queries, allowing you to perform different actions based on different conditions.

Here's the basic syntax of the CASE statement in MariaDB:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

- condition1, condition2, etc., are the conditions to be evaluated.

- result1, result2, etc., are the values or expressions returned when the corresponding condition is true.

- default_result is the value or expression returned if none of the conditions are true (optional).

Let's see an example to illustrate how to use the CASE statement in MariaDB:

Suppose we have a table employees with columns id, name, and salary. We want to categorize employees based on their salary into three categories: "Low", "Medium", and "High".

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    salary DECIMAL(10, 2)

);

INSERT INTO employees (id, name, salary) VALUES

(1, 'John', 50000.00),

(2, 'Alice', 70000.00),

(3, 'Bob', 45000.00),

(4, 'Jane', 90000.00);

Now, let's use a CASE statement to categorize the employees based on their salary:

SELECT 

    name,

    salary,

    CASE 

        WHEN salary < 50000 THEN 'Low'

        WHEN salary >= 50000 AND salary < 80000 THEN 'Medium'

        ELSE 'High'

    END AS salary_category

FROM 

    employees;

This query will categorize employees based on their salary into three categories: "Low", "Medium", and "High". The results will look like this:

| name  | salary   | salary_category |

|-------|----------|-----------------|

| John  | 50000.00 | Medium          |

| Alice | 70000.00 | Medium          |

| Bob   | 45000.00 | Low             |

| Jane  | 90000.00 | High            |

As you can see, the CASE statement evaluated the salary for each employee and categorized them accordingly in MariaDB.