SQL Case

🔹 What is SQL CASE?

The CASE statement in SQL is like an IF-THEN-ELSE statement in programming.

It allows you to:

• Make decisions inside a query
• Return different values based on conditions
• Categorize data
• Create custom output columns

Think of it like this:

“If this condition is true → return this value

If another condition is true → return that value

Otherwise → return something else”

🔹 Why Use SQL CASE?

You use CASE when you want to:

• Categorize data (e.g., High/Medium/Low salary)
• Replace values
• Apply logic in SELECT
• Create reports

🔹 Types of CASE in SQL

There are two types of CASE:

| Type            | Description |
|-----------------|------------|
| Simple CASE     | Compares one column to multiple values |
| Searched CASE   | Uses full conditions (>, <, =, etc.) |

🔹 Sample Employees Table

We’ll use this table for examples:

| id | name    | department | salary |
|----|---------|------------|--------|
| 1  | Alice   | HR         | 50000  |
| 2  | Bob     | Sales      | 60000  |
| 3  | Charlie | IT         | 55000  |
| 4  | David   | HR         | 45000  |
| 5  | Eve     | Sales      | 70000  |

1️⃣ Simple CASE

Used when you compare one column to specific values.

Syntax:

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result
END

Example: Department Labels

SELECT name,
       department,
       CASE department
           WHEN 'HR' THEN 'Human Resources'
           WHEN 'IT' THEN 'Information Technology'
           WHEN 'Sales' THEN 'Sales Department'
           ELSE 'Other'
       END AS department_full_name
FROM Employees;

What Happens?

• If department = 'HR' → show "Human Resources"
• If department = 'IT' → show "Information Technology"
• If department = 'Sales' → show "Sales Department"
• Otherwise → "Other"

2️⃣ Searched CASE

Used when you need conditions (>, <, >=, etc.)

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result
END

Example: Salary Categories

SELECT name,
       salary,
       CASE
           WHEN salary >= 65000 THEN 'High Salary'
           WHEN salary >= 50000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM Employees;

Explanation:

• Salary ≥ 65000 → High Salary
• Salary ≥ 50000 → Medium Salary
• Otherwise → Low Salary

SQL checks from top to bottom.

🔹 Using CASE in ORDER BY

You can even use CASE to control sorting.

SELECT name, department
FROM Employees
ORDER BY
    CASE
        WHEN department = 'HR' THEN 1
        WHEN department = 'IT' THEN 2
        ELSE 3
    END;

This sorts:

1. HR first
2. IT second
3. Others last

🔹 Using CASE with Aggregate Functions

Example: Count employees by salary level.

SELECT
    CASE
        WHEN salary >= 60000 THEN 'High'
        ELSE 'Standard'
    END AS salary_group,
    COUNT(*) AS total_employees
FROM Employees
GROUP BY
    CASE
        WHEN salary >= 60000 THEN 'High'
        ELSE 'Standard'
    END;

🔹 Important Rules

| Rule | Explanation |
|------|------------|
| CASE must end with END | END is mandatory |
| ELSE is optional | But recommended |
| Order matters | First true condition is executed |
| Works inside SELECT, ORDER BY, GROUP BY | Very flexible |

🔹 Visual Explanation

Think of CASE like this:

IF salary >= 65000 → High
ELSE IF salary >= 50000 → Medium
ELSE → Low

It’s just SQL’s way of writing IF-ELSE logic.

🔹 When Should You Use CASE?

Use CASE when:

• You want readable reports
• You need conditional logic inside queries
• You want to transform data without changing the table

-- Simple CASE Example
SELECT name,
       department,
       CASE department
           WHEN 'HR' THEN 'Human Resources'
           WHEN 'IT' THEN 'Information Technology'
           WHEN 'Sales' THEN 'Sales Department'
           ELSE 'Other'
       END AS department_full_name
FROM Employees;

-- Searched CASE Example
SELECT name,
       salary,
       CASE
           WHEN salary >= 65000 THEN 'High Salary'
           WHEN salary >= 50000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM Employees;

-- CASE in ORDER BY
SELECT name, department
FROM Employees
ORDER BY
    CASE
        WHEN department = 'HR' THEN 1
        WHEN department = 'IT' THEN 2
        ELSE 3
    END;

-- CASE with GROUP BY
SELECT
    CASE
        WHEN salary >= 60000 THEN 'High'
        ELSE 'Standard'
    END AS salary_group,
    COUNT(*) AS total_employees
FROM Employees
GROUP BY
    CASE
        WHEN salary >= 60000 THEN 'High'
        ELSE 'Standard'
    END;