How the CASE operator works in Mariadb?

The CASE operator is a conditional operator that allows you to execute different statements based on one or more conditions.

Posted on

The CASE operator is a conditional operator that allows you to execute different statements based on one or more conditions. The CASE operator can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses.

Syntax

The syntax of the CASE operator is as follows:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE result_else
END

Where:

  • condition_1, condition_2, …, condition_n are expressions that evaluate to TRUE or FALSE.
  • result_1, result_2, …, result_n are expressions that return a value when the corresponding condition is TRUE.
  • result_else is an optional expression that returns a value when none of the conditions are TRUE.

The CASE operator evaluates the conditions in order, from top to bottom, and returns the first result that matches a TRUE condition. If none of the conditions are TRUE, it returns the result_else if specified, or NULL otherwise.

Alternatively, you can use the following syntax of the CASE operator:

CASE expression
  WHEN value_1 THEN result_1
  WHEN value_2 THEN result_2
  ...
  WHEN value_n THEN result_n
  ELSE result_else
END

Where:

  • expression is an expression that returns a value to compare with the value_1, value_2, …, value_n.
  • value_1, value_2, …, value_n are expressions that return values to compare with the expression.
  • result_1, result_2, …, result_n are expressions that return a value when the expression is equal to the corresponding value.
  • result_else is an optional expression that returns a value when the expression is not equal to any of the value.

The CASE operator compares the expression with each value in order, from top to bottom, and returns the first result that matches an equal value. If none of the values are equal, it returns the result_else if specified, or NULL otherwise.

Examples

Example 1: Using the CASE operator in a SELECT statement

In this example, we use the CASE operator in a SELECT statement to display different messages based on the values in the grade column of the students table.

SELECT name, grade,
  CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Average'
    WHEN 'D' THEN 'Poor'
    WHEN 'F' THEN 'Fail'
    ELSE 'Invalid'
  END AS message
FROM students;

The output is:

+------+-------+----------+
| name | grade | message  |
+------+-------+----------+
| John | A     | Excellent|
| Mary | B     | Good     |
| Bob  | C     | Average  |
| Alice| D     | Poor     |
| Tom  | F     | Fail     |
| Jane | Z     | Invalid  |
+------+-------+----------+

Example 2: Using the CASE operator in a WHERE clause

In this example, we use the CASE operator in a WHERE clause to filter the rows based on a variable. We declare a variable named @grade and assign it a value of 'A'. Then we use the CASE operator to select only the rows that have the same grade as the variable, or the rows that have an invalid grade.

SET @grade = 'A';

SELECT name, grade
FROM students
WHERE grade = @grade
  OR grade NOT IN ('A', 'B', 'C', 'D', 'F');

The output is:

+------+-------+
| name | grade |
+------+-------+
| John | A     |
| Jane | Z     |
+------+-------+

Example 3: Using the CASE operator in an ORDER BY clause

In this example, we use the CASE operator in an ORDER BY clause to sort the rows based on a custom order. We want to sort the rows by the grade column, but we want to put the invalid grades at the bottom. We use the CASE operator to assign a numeric value to each grade, and then sort by that value in ascending order.

SELECT name, grade
FROM students
ORDER BY
  CASE grade
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'C' THEN 3
    WHEN 'D' THEN 4
    WHEN 'F' THEN 5
    ELSE 6
  END;

The output is:

+------+-------+
| name | grade |
+------+-------+
| John | A     |
| Mary | B     |
| Bob  | C     |
| Alice| D     |
| Tom  | F     |
| Jane | Z     |
+------+-------+

Some other functions that are related to the CASE operator are:

  • IF(): Returns a value based on a condition. The syntax is IF(condition, value_if_true, value_if_false).
  • IFNULL(): Returns the first argument if it is not NULL, otherwise returns the second argument. The syntax is IFNULL(arg1, arg2).
  • NULLIF(): Returns NULL if the two arguments are equal, otherwise returns the first argument. The syntax is NULLIF(arg1, arg2).
  • COALESCE(): Returns the first non-NULL argument from a list of arguments. The syntax is COALESCE(arg1, arg2, ..., argn).

For example, you can use the IF() function to achieve the same result as the first example of the CASE operator:

SELECT name, grade,
  IF(grade = 'A', 'Excellent',
    IF(grade = 'B', 'Good',
      IF(grade = 'C', 'Average',
        IF(grade = 'D', 'Poor',
          IF(grade = 'F', 'Fail', 'Invalid')
        )
      )
    )
  ) AS message
FROM students;

Conclusion

The CASE operator is a powerful operator that allows you to execute different statements based on one or more conditions. The CASE operator can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The CASE operator can have two forms: the simple form and the searched form. The simple form compares an expression with a list of values, and the searched form evaluates a list of conditions. The CASE operator returns the first result that matches a TRUE condition or value, or the ELSE result if specified, or NULL otherwise. The CASE operator can be combined with other functions to perform various operations and analyses.