CASE Expression

The SQL CASE Expression

In SQL, the CASE expression provides conditional logic similar to an "if-then-else" statement in other programming languages. It allows you to evaluate a set of conditions and return a result based on the first condition that evaluates to true, or a default value if no condition is met. The CASE expression can be used in SELECT statements to generate computed columns, in WHERE clauses to filter rows based on conditions, and in ORDER BY clauses to control the sorting order based on conditions.

Here's the basic syntax for the CASE expression:

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

Alternatively, you can use the searched form of the CASE expression, where each WHEN clause contains a separate condition:

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

Here's an example to illustrate the usage of the CASE expression in a SELECT statement:

Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary, and we want to create a new column indicating whether each employee's salary is above or below the company's average salary. We can use the CASE expression to achieve this:

  SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary,
    CASE
        WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above Average'
        ELSE 'Below Average'
    END AS salary_comparison
  FROM employees;
     

In this example:

• The CASE expression evaluates whether each employee's salary is greater than the average salary of all employees.
• If the condition is true, 'Above Average' is returned; otherwise, 'Below Average' is returned.
• The AS keyword is used to assign a name to the computed column, which is salary_comparison.

The CASE expression is a powerful tool for performing conditional logic within SQL queries, allowing you to customize the results based on various conditions.