Case expressions allow you to use the IF ... THEN ... ELSE syntax in SQL statements without calling a stored procedure.
Syntax of conditional expressions
CASE { simple_case_expression
| searched_case_expression
}
[ ELSE else_expr ]
END
Simple case expression simple_case_expression
expr
{ WHEN comparison_expr THEN return_expr }...
Search condition expression searched_case_expression
{ WHEN condition THEN return_expr }...
Usage rules
Validation of conditions
In a simple conditional expression, OceanBase Database searches for the first comparison_expr that equals expr in the WHEN ... THEN clause and returns the corresponding return_expr. If no WHEN ... THEN clause meets this condition and an ELSE clause exists, OceanBase Database returns else_expr. Otherwise, it returns NULL.
In a search condition expression, OceanBase Database searches from left to right until the condition becomes true, and then returns return_expr. If none of the conditions are true and an ELSE clause exists, the database returns else_expr. Otherwise, OceanBase Database returns NULL.
Condition evaluation
OceanBase Database uses short-circuit evaluation. For simple conditional expressions, the database evaluates each comparison_expr only once before comparing it to expr, rather than evaluating all comparison_expr values at once. Therefore, if a previous comparison_expr equals expr, OceanBase Database will not evaluate the next comparison_expr. For search conditional expressions, the database serially evaluates whether each condition is true. If a previous condition is true, OceanBase Database will not evaluate the next condition.
Data types
For simple conditional expressions, the data types of expr and all comparison_expr values must be the same or compatible.
For simple condition expressions and search condition expressions, the data types of all return_exprs must be the same or compatible.
Examples
Simple conditional expression examples
obclient> CREATE TABLE student (name VARCHAR(20), score INT);
obclient> INSERT INTO student VALUES ('Alice', 85), ('Bob', 92), ('Carol', 75);
obclient> SELECT name,
CASE score
WHEN 90 THEN 'Excellent'
WHEN 80 THEN 'Good'
WHEN 70 THEN 'Medium'
WHEN 60 THEN 'Pass'
ELSE 'Fail' END AS level
FROM student
ORDER BY name;
Examples of search condition expressions
obclient> CREATE TABLE employees (name VARCHAR(20), salary DECIMAL(10, 2));
obclient> INSERT INTO employees VALUES ('Alice', 8000), ('Bob', 5000);
obclient> SELECT AVG(CASE WHEN e.salary > 6000 THEN e.salary
ELSE 6000 END) "avg_salary" FROM employees e;