CASE Expression in Oracle SQL

Topic Introduction: In Oracle SQL, the CASE expression is used for conditional logic within SQL queries. It allows you to perform conditional operations and return different values based on specified conditions. The CASE expression can be used in the SELECT, FROM, WHERE, ORDER BY, and other clauses of SQL statements.

The basic syntax of the CASE expression in Oracle SQL is as follows:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE else_result

END

WHEN condition1 THEN result1: This specifies a condition and the corresponding result value to return if the condition is true.

WHEN condition2 THEN result2: You can have multiple WHEN clauses to check multiple conditions.

ELSE else_result: This is optional and specifies a default result to return when none of the conditions match.

Here's a simple example of using the CASE expression in a SELECT statement to categorize employees based on their salaries:

SELECT

    last_name,

    salary,

    CASE

        WHEN salary >= 50000 THEN 'High Salary'

        WHEN salary >= 30000 THEN 'Medium Salary'

        ELSE 'Low Salary'

    END AS salary_category

FROM employees;

In this example:

The CASE expression checks the value of the salary column for each employee.

If the salary is greater than or equal to 50000, it returns 'High Salary'.

If the salary is between 30000 and 49999, it returns 'Medium Salary'.

If none of the conditions are met, it returns 'Low Salary'.

You can also use CASE expressions in other parts of SQL queries. For example, in the WHERE clause to filter rows based on conditions, or in the ORDER BY clause to sort rows conditionally.

Here's an example of using the CASE expression in the WHERE clause:

SELECT last_name, hire_date

FROM employees

WHERE CASE

    WHEN hire_date >= TO_DATE('2022-01-01', 'YYYY-MM-DD') THEN 'New Hire'

    ELSE 'Not a New Hire'

END = 'New Hire';

In this query, it selects employees hired on or after January 1, 2022, by using the CASE expression in the WHERE clause to categorize employees as 'New Hire' or 'Not a New Hire' based on their hire date.

The CASE expression is a powerful tool for conditional logic in SQL queries and allows you to create more flexible and customized result sets.