WHEN

MySQL

Example

SET @grade = 90;

SELECT
  CASE
    WHEN @grade >= 90 THEN 'A'
    WHEN @grade >= 80 THEN 'B'
    ELSE 'C'
  END;

Output

+--------------------+
| A                  |
+--------------------+

Explanation

In the provided SQL example, a variable @grade is set to 90. Following this, a SELECT CASE statement is used. This statement checks if @grade is greater than or equal to 90 and if it’s true, it returns ‘A’. If the condition fails, the next condition checks if @grade is greater than or equal to 80. If true, it returns ‘B’. If both conditions fail, it defaults to ‘C’. The output of the executed statement is ‘A’ since the @grade (90) is greater than or equal to 90.

PostgreSQL

Example

DO
$do$
BEGIN
   IF 2 > 1 THEN
     RAISE NOTICE '2 is greater than 1';
   ELSE
     RAISE NOTICE '2 is not greater than 1';
   END IF;
END
$do$
;

Output

NOTICE:  2 is greater than 1

Explanation

In the given example, PostgreSQL executes an inline anonymous block where the IF statement compares two numbers. If the condition matches (2 is indeed greater than 1), then it raises a notice ‘2 is greater than 1’, else it raises a notice ‘2 is not greater than 1’. In this case, it matches the condition and hence the output is ‘2 is greater than 1’.

SQL Server

Example

DECLARE @TestValue INT;
SET @TestValue = 3;

SELECT
    CASE
        WHEN @TestValue = 1 THEN 'Option1'
        WHEN @TestValue = 2 THEN 'Option2'
        ELSE 'OptionOther'
    END AS Result;

Output

|  Result    |
|------------|
| OptionOther|

Explanation

In this example, a variable @TestValue is declared and assigned a value of 3. Using the CASE statement with multiple WHEN clauses, different output values are expressed. Since the @TestValue equals 3, it doesn’t match with ‘Option1’ or ‘Option2’ and the ELSE clause gets executed, resulting in ‘OptionOther’.

Oracle

Example

SELECT employee_id,
       last_name,
       job_id,
       CASE
           WHEN job_id = 'IT_PROG' THEN 'IT Programmer'
           WHEN job_id = 'FI_MGR' THEN 'Finance Manager'
           ELSE 'Other'
       END job_title
FROM employees;

Output

EMPLOYEE_ID    LAST_NAME    JOB_ID    JOB_TITLE
------------ ------------ -------- ---------------
100           King         FI_MGR   Finance Manager
101           Kochhar      AD_VP    Other
102           De Haan      AD_VP    Other

Explanation

The CASE clause in SQL statement is a conditional statement that provides a way of doing if-then-else logic within SQL. The above example demonstrates how to use the WHEN clause within a CASE statement in Oracle SQL. It assigns different job titles based on the respective job ID. IT_PROG is titled ‘IT Programmer’, FI_MGR is titled ‘Finance Manager’ and any other job ID will be titled simply ‘Other’.

SQLite

Example

SELECT name,
CASE
    WHEN population < 1000000 THEN 'Small city'
    WHEN population < 5000000 THEN 'Medium city'
    ELSE 'Large city'
END AS city_size
FROM city;

Output

| name   | city_size  |
|--------|------------|
| City A | Small city |
| City B | Medium city|
| City C | Large city |

Explanation

In this example, the CASE statement is used to categorize cities into Small, Medium, and Large based on the population. If the population is less than 1000000, it qualifies as Small city. If it’s greater than or equal to 1000000 but less than 5000000, it’s a Medium city. Otherwise, it’s a Large city. The result of the CASE statement is included in the output under the column named city_size.