SQL CASE

The SQL CASE statement is used to create conditional logic in queries. It allows you to perform IF-ELSE type operations and return values based on conditions. This statement can be used within SELECT, UPDATE, DELETE, and INSERT statements to create more dynamic queries.

 

Types of CASE Statements

1. Simple CASE
2. Searched CASE

1. Simple CASE

The Simple CASE statement compares a given expression to various possible values and returns a result when a match is found.

Syntax:

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

Example:

SELECT first_name,
       CASE department_id
           WHEN 1 THEN 'Sales'
           WHEN 2 THEN 'Marketing'
           ELSE 'Other'
       END AS department_name
FROM employees;

• In this example, the department_id is checked. If the value is 1, it returns 'Sales', if it is 2, it returns 'Marketing', and for any other value, it returns 'Other'.

2. Searched CASE

The Searched CASE statement evaluates multiple conditions (expressions) and returns a result based on the first condition that evaluates to true. It is useful when you need to check complex conditions.

Syntax:

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

Example:

SELECT first_name, salary,
       CASE
           WHEN salary >= 5000 THEN 'High'
           WHEN salary >= 3000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

• In this example, the salary is checked. If it is 5000 or higher, it returns 'High', if it is between 3000 and 4999, it returns 'Medium', and anything below 3000 returns 'Low'.

 

CASE in ORDER BY

The CASE statement can also be used in the ORDER BY clause to dynamically order rows based on a condition.

Example:

SELECT first_name, salary
FROM employees
ORDER BY
    CASE
        WHEN salary >= 5000 THEN 1
        WHEN salary >= 3000 THEN 2
        ELSE 3
    END;

• In this query, employees with a salary of 5000 or higher will be ordered first, followed by those earning between 3000 and 4999, and lastly, employees with a salary lower than 3000.

 

CASE with Aggregates

You can also use the CASE statement inside aggregate functions to apply conditional logic.

Example:

SELECT department_id,
       COUNT(CASE WHEN salary >= 5000 THEN 1 END) AS high_salary_count
FROM employees
GROUP BY department_id;

• This query counts the number of employees in each department with a salary of 5000 or higher.

 

Key Points

• Simple CASE: Compares a column's value to multiple possible values.
• Searched CASE: Evaluates multiple conditions to return a result.
• Aliases: The result of the CASE statement can be given an alias using the AS keyword.
• Conditional Sorting: CASE can be used in the ORDER BY clause to sort data dynamically.
• Flexibility: CASE is a versatile way to incorporate conditional logic directly into SQL queries.

 

Summary

The SQL CASE statement enables you to implement conditional logic within your queries. It can be used to transform data based on specific conditions, dynamically sort rows, and calculate aggregated results. You can use it in both simple and more complex scenarios to enhance the flexibility of your SQL queries.