SQL CASE Statement

The CASE statement has the functionality of an IF-THEN-ELSE statement.

The CASE statement examines every condition and returns a value when the first condition is satisfied. When a condition is TRUE, it will stop reading and return the result. If no conditions are TRUE, it returns the value in the ELSE clause.

The CASE statement will return NULL if there is no ELSE clause and no conditions are TRUE.

Syntax

The syntax for the CASE statement is as follows:

CASE expression

    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    WHEN valueN THEN resultN
    
    ELSE result

END;

OR

CASE 

    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionN THEN resultN

    ELSE result

END;

Parameters:

• colmun_list: The expression that will be compared against each of the provided values.

• value1, value2, ... valueN: The values that will be used in the evaluation. When a value matches the expression, the CASE statement will return the corresponding result and not evaluate any further values.

• condition1, condion2, ... conditionN: The conditions that will be evaluated. When a condition is found to be TRUE, the CASE statement will return the corresponding result and not evaluate any further conditions. All conditions must be the same datatype.

• result1, result2, ... resultN: The value returned when a condition is found to be TRUE. All results must be the same datatype.

Note:

• The CASE statement evaluates conditions in the order listed. When a condition is found to be TRUE, the CASE will return the corresponding result and stop evaluating any further conditions.

• If no condition is found to be TRUE, then the CASE statement will return the value specified in the ELSE clause.

• The CASE statement will return NULL if there is no ELSE clause and no conditions are TRUE.

Demo Table

Let us suppose that we have a table called "Employee" with the following data:

SQL CASE Example

The following SQL statement goes through each row of the "Employee" table and returns a specified value when the salary is greater than (>), less than (<), or equal (=) 8000.

SELECT empo_no, name, salary, 
CASE 
    WHEN salary > 8000 THEN 'The salary is greater than 8000'
    WHEN salary < 8000 THEN 'The salary is less than 8000'
    ELSE 'The salary is equal to 8000'
END AS salary_text
FROM employee;

After the execution of the above statement, the output will be as follows:

+--------+------------------+--------+---------------------------------+
| emp_no | name             | salary | salary_text                     |
+--------+------------------+--------+---------------------------------+
| 1001   | James Smith      | 6600   | The salary is less than 8000    |
| 1002   | Maria Martinez   | 9000   | The salary is greater than 8000 |
| 1003   | Andrew Rodriguez | 9500   | The salary is greater than 8000 |
| 1004   | Robert Hernandez | 9600   | The salary is greater than 8000 |
| 1005   | Donna Williams   | 7400   | The salary is less than 8000    |
| 1006   | James Johnson    | 8000   | The salary is equal to 8000     |
| 1007   | Stephanie Miller | 8500   | The salary is greater than 8000 |
| 1008   | Nelson Lopez     | 7300   | The salary is less than 8000    |
+--------+------------------+--------+---------------------------------+