/    /  MySQL – CASE Statement

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.

Syntax

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.

Example

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.

c1

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.

Syntax

CASE   
    WHEN [condition] THEN result   
    [WHEN [condition] THEN result ...]   
    [ELSE result]   
END  

Example

SELECT CASE BINARY 'A' WHEN 'b' THEN 1 WHEN 'b' THEN 2 END;

c2