How the CASE expression works in Mariadb?

The CASE expression in MariaDB is used for creating conditional queries. It’s similar to the IF/ELSE statement in other programming languages, allowing for more complex control over the returned data based on specific conditions.

Syntax

The syntax for the MariaDB CASE expression is as follows:

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

The CASE expression evaluates each condition in order and returns the corresponding result for the first true condition. If no condition is true, the result of the ELSE clause is returned.

Examples

Example 1: Simple CASE Expression

This example demonstrates a simple CASE expression that returns a custom message based on a value:

SELECT CASE WHEN 1 > 0 THEN 'True' ELSE 'False' END;

+----------------------------------------------+
| CASE WHEN 1 > 0 THEN 'True' ELSE 'False' END |
+----------------------------------------------+
| True                                         |
+----------------------------------------------+

The output is True because the condition 1 > 0 is true.

Example 2: CASE Expression in a Query

Here’s how to use a CASE expression within a query:

SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS status FROM users;

This will return a list of names and their status as either ‘Adult’ or ‘Minor’ based on their age.

Example 3: CASE with Multiple Conditions

This example shows a CASE expression with multiple conditions:

SELECT score, CASE
    WHEN score >= 90 THEN 'Excellent'
    WHEN score >= 80 THEN 'Good'
    WHEN score >= 70 THEN 'Average'
    ELSE 'Below Average'
END AS grade FROM students;

This will categorize students’ scores into different grades.

Example 4: Using CASE with Aggregate Functions

A CASE expression can be used with aggregate functions:

SELECT department, SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_earners FROM employees GROUP BY department;

This will count the number of high earners in each department.

Example 5: Nested CASE Expressions

CASE expressions can be nested:

SELECT product, CASE
    WHEN quantity > 100 THEN 'High stock'
    WHEN quantity BETWEEN 50 AND 100 THEN 'Medium stock'
    ELSE 'Low stock'
END AS stock_status FROM inventory;

This will provide a stock status based on the quantity of products.

Related Functions

• The IF() function is used to return a value based on a condition.
• The IFNULL() function is used to return a specified value if the expression is NULL.
• The NULLIF() function is used to return NULL if two expressions are equal.

Conclusion

The CASE expression is a powerful tool in MariaDB that allows for complex conditional logic within SQL queries. It enhances the flexibility and readability of SQL statements and is essential for dynamic data retrieval based on varying conditions. Understanding how to effectively use the CASE expression can greatly improve the efficiency and capability of database operations.