CASE Statement in Oracle

In Oracle, the CASE statement is a powerful tool for conditional logic within SQL queries. It allows you to perform different actions based on different conditions. Here's the basic syntax of the CASE statement in Oracle:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

Here's an example of how you might use the CASE statement in a SQL query:

Suppose you have a table called employees with columns employee_id, first_name, last_name, and salary. You want to categorize employees based on their salary ranges.

SELECT

    first_name,

    last_name,

    salary,

    CASE

        WHEN salary >= 100000 THEN 'High Salary'

        WHEN salary >= 50000 THEN 'Medium Salary'

        ELSE 'Low Salary'

    END AS salary_category

FROM

    employees;

In this example:

- If the salary is greater than or equal to 100,000, the employee is categorized as having a "High Salary".

- If the salary is between 50,000 and 99,999, the employee is categorized as having a "Medium Salary".

- If the salary is less than 50,000, the employee is categorized as having a "Low Salary".

The CASE statement evaluates each condition sequentially and returns the result associated with the first condition that evaluates to true. If none of the conditions are met, the ELSE clause provides a default result.

You can also use the CASE statement in combination with other SQL functions and expressions to achieve more complex logic.