MySQL – CASE Statement
The MySQL CASE expression is a part of the control flow function that allows us to write an if-else logic to a query. The expression can be used anywhere that uses a valid program or query, such as the SELECT, WHERE, ORDER BY clause, etc.
When the first condition is true, the CASE expression returns the result. Once the condition is met, the traversal is stopped and the output is displayed. If no condition is found to be true, it executes the else block. A NULL value is returned when the else block is not found. In MySQL, the CASE statement is used to handle multiple IF statements within the SELECT clause.
The CASE statement can be used in two different ways:
Simple CASE statement:
As shown below, the first method involves matching a value with the given statement.
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
When the first compare_value comparison is true, it returns the result. Otherwise, the else clause will be returned.
SELECT CASE 2 WHEN 2 THEN 'two' WHEN 1 THEN 'one' ELSE 'more' END;
Following the successful execution of the above command, we will see the following output.
Searched CASE statement:
As a second method, a search condition is considered in the WHEN clauses, and if it is found, the result is returned in the corresponding THEN clause. Otherwise, the else clause will be returned. In the absence of an else clause, the function will return NULL.
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
SELECT CASE BINARY 'A' WHEN 'b' THEN 1 WHEN 'b' THEN 2 END;