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 the CASE statement in detail with examples.

Syntax

Here is the basic syntax of the CASE statement within a SELECT statement.

  • The CASE statement returns result1 when condition1 is satisfied.
  • It returns result2 when condition2 is satisfied.
  • It returns resultN when conditionN is satisfied.
  • If none of the conditions are satisfied, then the result is returned.
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionN THEN resultN
    ELSE result
END;

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.

SELECT With CASE Without ELSE Block

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 understood how to use the CASE statement with multiple conditions.

Related Links