SQL CASE

---

In SQL, conditional logic is essential for performing complex data manipulation and calculations. The CASE statement in SQL provides a way to implement if-then-else logic directly within your queries. It allows you to create conditional statements that return different values based on certain conditions, making your SQL queries more flexible and dynamic.

---

1. What is the SQL CASE Statement?

The SQL CASE statement evaluates a set of conditions and returns a value when a condition is met. It's similar to an if-else statement in programming languages, providing a way to execute different logic based on specific criteria.

There are two types of CASE statements in SQL:

1. Simple CASE Expression: Evaluates a single expression and returns a value based on matching conditions.
2. Searched CASE Expression: Allows multiple conditions to be evaluated, each with a specific condition to check.

---

2. Basic Syntax of the SQL CASE Statement

1. Simple CASE Expression

The simple CASE expression compares an expression to a set of values and returns a result for the first match it finds.

SELECT column1, 
       column2,
       CASE column1
           WHEN value1 THEN 'Result1'
           WHEN value2 THEN 'Result2'
           ELSE 'DefaultResult'
       END AS result_column
FROM table_name;

• column1: The column to evaluate.
• value1, value2: The values that column1 will be compared against.
• Result1, Result2: The result to return if a match is found.
• ELSE: An optional default value to return if no match is found.

2. Searched CASE Expression

The searched CASE expression evaluates multiple conditions and returns a result based on the first true condition.

SELECT column1,
       column2,
       CASE 
           WHEN condition1 THEN 'Result1'
           WHEN condition2 THEN 'Result2'
           ELSE 'DefaultResult'
       END AS result_column
FROM table_name;

• condition1, condition2: The conditions to evaluate (e.g., column1 > 100, column2 = 'A').
• Result1, Result2: The result to return when a condition is true.
• ELSE: An optional default result if no condition is true.

---

3. Examples of Using SQL CASE

Example 1: Using Simple CASE Expression

Let's say you have a sales table with columns employee_id, sales_amount, and commission_rate. You want to create a query that classifies employees into different commission tiers based on their sales amount.

SELECT employee_id,
       sales_amount,
       CASE sales_amount
           WHEN sales_amount >= 10000 THEN 'Tier 1'
           WHEN sales_amount >= 5000 THEN 'Tier 2'
           ELSE 'Tier 3'
       END AS commission_tier
FROM sales;

Explanation:

• The sales_amount is evaluated.
• Employees with sales greater than or equal to 10,000 are assigned to "Tier 1".
• Employees with sales greater than or equal to 5,000 but less than 10,000 are assigned to "Tier 2".
• All other employees are assigned to "Tier 3".

Example 2: Using Searched CASE Expression

Now, let’s use the searched CASE expression to create a query that categorizes employees based on both their sales_amount and commission_rate.

SELECT employee_id,
       sales_amount,
       commission_rate,
       CASE 
           WHEN sales_amount >= 10000 AND commission_rate > 0.1 THEN 'High Performer'
           WHEN sales_amount >= 5000 AND commission_rate > 0.05 THEN 'Average Performer'
           ELSE 'Low Performer'
       END AS performance_category
FROM sales;

Explanation:

• The searched CASE expression checks both the sales_amount and commission_rate to assign a performance category.
• If an employee has sales above 10,000 and a commission rate greater than 10%, they are classified as a "High Performer".
• If their sales are above 5,000 and the commission rate is greater than 5%, they are classified as "Average Performer".
• All other employees are classified as "Low Performer".

Example 3: Using CASE in ORDER BY Clause

You can also use the CASE statement in the ORDER BY clause to conditionally sort your query results. Let’s say you want to sort employees by performance category dynamically.

SELECT employee_id,
       sales_amount,
       commission_rate,
       CASE 
           WHEN sales_amount >= 10000 AND commission_rate > 0.1 THEN 'High Performer'
           WHEN sales_amount >= 5000 AND commission_rate > 0.05 THEN 'Average Performer'
           ELSE 'Low Performer'
       END AS performance_category
FROM sales
ORDER BY 
    CASE 
        WHEN sales_amount >= 10000 AND commission_rate > 0.1 THEN 1
        WHEN sales_amount >= 5000 AND commission_rate > 0.05 THEN 2
        ELSE 3
    END;

Explanation:

• The CASE statement assigns a numeric value based on performance.
• The query is then sorted based on those numeric values, ensuring that "High Performers" are at the top, followed by "Average Performers", and "Low Performers" are at the bottom.

---

4. Advanced Use of SQL CASE: NULL Handling

The CASE statement can also handle NULL values. For example, you can use CASE to replace NULL with a default value in your result set.

Example: Handling NULL in CASE

SELECT employee_id,
       sales_amount,
       commission_rate,
       CASE 
           WHEN commission_rate IS NULL THEN 'No Commission'
           WHEN commission_rate > 0.1 THEN 'High Commission'
           ELSE 'Standard Commission'
       END AS commission_status
FROM sales;

Explanation:

• If the commission_rate is NULL, it is labeled as "No Commission".
• If the commission rate is greater than 10%, the employee is labeled as "High Commission".
• All other employees are labeled as "Standard Commission".

---

---