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, each returning a different value.

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

SELECT With CASE

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 salary.
  • It has an ELSE block, that returns a text if none of the conditions are satisfied.

Run this on IDE

SELECT employee_id, employee_name, salary, 
CASE
    WHEN salary > 50000 THEN 'The salary is greater than 50000.'
    WHEN salary = 50000 THEN 'The salary is equal to 50000.'
    ELSE 'The salary is less than 50000.'
END AS salary_text
FROM employees;

After successful execution, the output contains the below data.

employee_id employee_name salary salary_text
1 Robin Hood 25000 The salary is less than 50000.
2 Tony Blank 89000 The salary is greater than 50000.
3 Andrew Russel 28000 The salary is less than 50000.
4 James Cooper 50000 The salary is equal to 50000.
5 Rose Cutler 65000 The salary is greater than 50000.

Overall

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

Related Links