Case when statement in SQL

The CASE WHEN statement in SQL is used for conditional logic within a query, similar to an "if-else" statement in programming. It allows us to return different values based on specific conditions.

SQL Syntax

SELECT column_name,
       CASE 
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
       END AS alias_name
FROM table_name;

Here, result3 returns when no condition is satisfied. Here, alias_name is the name of the column created in the table by the help of this conditional statement.

Example: Suppose we have a table called Employees with a column Salary.

Table: Employees

EmployeeID	EmployeeName	Salary
1	John	80000
2	Alice	50000
3	Bob	30000
4	Carol	75000
5	Dave	45000

Task: We want to categorize the employees based on their salary into three groups:

High: Salary greater than 70,000.
Medium: Salary between 40,000 and 70,000 (inclusive).
Low: Salary less than 40,000.

SQL

SELECT EmployeeID, 
       EmployeeName, 
       Salary,
       CASE 
           WHEN Salary > 70000 THEN 'High'
           WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryCategory
FROM Employees;

The result of the above query is shown below:

EmployeeID	EmployeeName	Salary	SalaryCategory
1	John	80000	High
2	Alice	50000	Medium
3	Bob	30000	Low
4	Carol	75000	High
5	Dave	45000	Medium

Explanation:

John and Carol have salaries above 70,000, so their SalaryCategory is marked as "High".
Alice and Dave fall into the range 40,000–70,000, so they are categorized as "Medium".
Bob has a salary below 40,000, so he is categorized as "Low".

We can apply more complex conditions inside the CASE WHEN block based on our specific use case.

Previous Next