CASE

MySQL

Example

SELECT
    name,
    CASE
        WHEN age < 18 THEN 'Child'
        WHEN age BETWEEN 18 AND 64 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM
    people;

Output

| name  | age_group |
|-------|-----------|
| Maria | Adult     |
| John  | Senior    |
| Alice | Child     |
| Bob   | Adult     |

Explanation

In the SQL SELECT statement, the CASE statement evaluates the age column. If the age is less than 18, it returns ‘Child’. If the age is between 18 and 64, it returns ‘Adult’. Else, it returns ‘Senior’. These results are labeled as ‘age_group’ in the final output.

PostgreSQL

Example

SELECT
    id,
    CASE
        WHEN age < 18 THEN 'Underage'
        WHEN age >= 18 AND age <= 60 THEN 'Adult'
        ELSE 'Senior'
    END as Age_Group
FROM users;

Output

id	Age_Group
1	Adult
2	Senior
3	Underage

Explanation

The CASE statement in the query assigns a value to each row based on conditions related to the age field. If the age is less than 18, it returns ‘Underage’. If age is 18 or more but less than or equal to 60, it returns ‘Adult’. For any other values, it returns ‘Senior’.

SQL Server

Example

SELECT
    EmployeeId,
    FirstName,
    LastName,
    CASE
        WHEN Salary > 10000 THEN 'High'
        WHEN Salary > 5000 THEN 'Medium'
        ELSE 'Low'
    END as SalaryRange
FROM Employee

Output

| EmployeeId | FirstName | LastName | SalaryRange |
|------------|-----------|----------|--------------|
| 1          | John      | Doe      | High         |
| 2          | Jane      | Doe      | Medium       |
| 3          | Bob       | Smith    | Low          |

Explanation

The CASE statement in the SQL code evaluates each row’s Salary column and categorizes it into ‘High’, ‘Medium’, or ‘Low’. If the Salary is over 10000, it falls into the ‘High’ category. If it is over 5000 but not over 10000, it falls into the ‘Medium’ category. If it doesn’t meet either of those conditions, by default it falls into the ‘Low’ category. The output table presents the resultant columns after the execution of the SQL query.

Oracle

Example

SELECT first_name,
        CASE
            WHEN salary > 50000 THEN 'High'
            ELSE 'Low'
        END AS Salary_Range
FROM employees;

Output

 FIRST_NAME | SALARY_RANGE
------------|--------------
 John       | High
 Jane       | Low

Explanation

In the example provided, the CASE statement is used to introduce a condition into a SQL query. The condition checks each row in the column “salary”. When the value in salary is greater than 50000, ‘High’ is returned by the CASE statement; for all other values ‘Low’ is returned. This result is then labelled as “Salary_Range”.

SQLite

Example

SELECT Name, Continent,
CASE
  WHEN Population > 1000000000 THEN 'Very Large'
  WHEN Population > 100000000 THEN 'Large'
  ELSE 'Small'
END AS Population_Size
FROM Country;

Output

| Name       | Continent | Population_Size |
|------------|-----------|-----------------|
| China      | Asia      | Very Large      |
| India      | Asia      | Very Large      |
| USA        | N.America | Large           |
| Australia  | Oceania   | Small           |
...

Explanation

The CASE statement is used to perform conditional logic in SQL. In the provided example, it separates countries from the “Country” table into ‘Very Large’, ‘Large’, and ‘Small’ based on population. For each row in the table, it checks the condition in sequence - if the population exceeds 1 billion, it is classified ‘Very Large’, if it exceeds 100 million it is ‘Large’, otherwise, it is ‘Small’.