Case Expression in SQL

Case

In SQL, the CASE expression is used to perform conditional logic within a query. It allows you to specify conditions and return different values based on those conditions. The CASE expression can be used in various parts of a SQL query, such as the SELECT statement, WHERE clause, ORDER BY clause, and more.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

• condition1, condition2, etc.: Conditions that you want to evaluate.
• result1, result2, etc.: Values to return if the corresponding condition is true.
• default_result: Value to return if none of the conditions are true (optional).

Example:

Suppose we have a table named employees with columns first_name, last_name, and salary.

To categorize employees based on their salary ranges, you can use a CASE expression within a SELECT statement:

SELECT first_name, last_name,
    CASE
        WHEN salary >= 50000 AND salary < 70000 THEN 'Low'
        WHEN salary >= 70000 AND salary < 90000 THEN 'Medium'
        WHEN salary >= 90000 THEN 'High'
        ELSE 'Unknown'
    END AS salary_category
FROM employees;

In this example:

• We use the CASE expression to categorize employees into different salary categories.
• If an employee's salary falls within a specific range, the corresponding category is returned.
• If the salary does not fall within any defined range, the 'Unknown' category is returned.

In SQL, the CASE expression can also be used in an UPDATE statement to conditionally update values in a table based on specified criteria.

Syntax:

UPDATE table_name
SET column_name =
    CASE
        WHEN condition1 THEN value1
        WHEN condition2 THEN value2
        ...
        ELSE default_value
    END
WHERE condition;

Example:

UPDATE employees
SET salary_grade =
    CASE
        WHEN salary >= 50000 AND salary < 70000 THEN 'Low'
        WHEN salary >= 70000 AND salary < 90000 THEN 'Medium'
        WHEN salary >= 90000 THEN 'High'
        ELSE 'Unknown'
    END;

In this example:

• We use the CASE expression to determine the appropriate salary grade based on the salary of each employee.
• If an employee's salary falls within a specific range, the corresponding salary grade is assigned.
• If the salary does not fall within any defined range, the 'Unknown' salary grade is assigned.