Description

The CASE clause can be used to define multiple conditions, each returning a different value when the condition is satisfied.

  • It contains a number of conditions and their values, which are read one after the other, and stops reading the conditions when the first condition is met and returns its value.
  • Once a condition is met, it stops reading the conditions and returns the respective value.
  • If none of the conditions are met, then it returns the value from the ELSE clause.
  • If the ELSE condition is not defined and none of the conditions are met, then a NULL value is returned.
  • It can have any number of conditions.

Let's look at a SELECT statement using the CASE statement with multiple conditions without an ELSE block.

Table Date

Before executing any queries, consider the below data on the tables.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel   1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 50000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Example

The below SELECT statement returns the employees and their salaries, along with a message using the CASE statement.

  • Each condition returns a text, based on their gender.
  • It doesn't have an ELSE block, so the CASE statement returns a NULL when none of the conditions are satisfied.

Run this on IDE

SELECT employee_id, employee_name, gender, 
CASE
    WHEN gender = 'M' THEN 'Male'
    WHEN gender = 'F' THEN 'Female'
END AS gender_text
FROM employees;

After successful execution, the output contains the below data.

employee_id employee_name gender gender_text
1 Robin Hood M Male
2 Tony Blank M Male
3 Andrew Russel   NULL
4 James Cooper M Male
5 Rose Cutler F Female

Overall

We now looked at an example that explains the usage of the CASE statement with multiple conditions and no ELSE block.

Related Links