CASE WHEN in SQL. How it works including examples.

Introduction to the CASE WHEN Statement in SQL

The CASE WHEN statement in SQL is a powerful tool for making conditional computations and queries. It allows you to add if-else logic to SQL queries, helping to segment data and perform different actions based on specific criteria.

Syntax

The basic syntax of the CASE WHEN statement in SQL is as follows:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

🧑‍💻 Where:

• condition1, condition2, …: These are conditional statements that are evaluated sequentially.
• result1, result2, …: These are the results returned when corresponding conditions are met.
• ELSE resultN: This is the fallback result returned if none of the conditions are met (optional).

Example

Let’s consider an example where we have a table of employee details, and we want to classify employees based on their years of experience.

Assume we have a table named employees with the following structure:

EmployeeID	Name	YearsOfExperience
1	John	2
2	Jane	5
3	Dave	8

To classify employees as “Junior”, “Mid-level”, or “Senior” based on their years of experience, we can use the CASE WHEN statement in the following query:

SELECT 
    EmployeeID,
    Name,
    YearsOfExperience,
    CASE
        WHEN YearsOfExperience < 3 THEN 'Junior'
        WHEN YearsOfExperience BETWEEN 3 AND 7 THEN 'Mid-level'
        ELSE 'Senior'
    END AS ExperienceLevel
FROM 
    employees;

📌 In this example:

➡️ The CASE statement is used to evaluate each employee’s YearsOfExperience and classify them accordingly.

• If YearsOfExperience is less than 3, the employee is classified as ‘Junior’.
• If YearsOfExperience is between 3 and 7 (inclusive), the employee is classified as ‘Mid-level’.
• If none of the above conditions are met, the employee is classified as ‘Senior’.

The resulting output of this query will be:

EmployeeID	Name	YearsOfExperience	ExperienceLevel
1	John	2	Junior
2	Jane	5	Mid-level
3	Dave	8	Senior

The CASE WHEN statement is incredibly versatile and can be used for various conditional querying and data manipulation scenarios. Whether you’re classifying data or calculating conditional values, this powerful feature helps make your SQL queries more dynamic and meaningful.