CASE

The SQL CASE statement is a powerful tool that allows you to perform conditional logic in your SQL queries. The statement is used to evaluate a condition or set of conditions and return a value based on the result of that evaluation.

Syntax

The basic syntax of the SQL CASE statement is as follows:

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

In this syntax, the CASE statement evaluates each WHEN condition in order, returning the corresponding result when a condition is true. If none of the conditions are true, the statement returns the result specified in the ELSE clause.

Example

Let’s take a look at an example. Suppose we have a table called “employees” with columns for “employee_id”, “first_name”, “last_name”, “salary”, and “department_id”. We want to create a query that returns the employee’s full name, department name, and a “salary range” based on their salary. Here’s how we could do it with the SQL CASE statement:

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    departments.department_name,
    CASE 
        WHEN salary < 50000 THEN 'Low'
        WHEN salary >= 50000 AND salary < 80000 THEN 'Medium'
        ELSE 'High'
    END AS salary_range
FROM 
    employees 
    JOIN departments ON employees.department_id = departments.department_id;

In this example, we use the CONCAT function to combine the employee's first and last names into a single column called "full_name". We also join the "employees" table with the "departments" table using the "department_id" column.

The interesting part is the CASE statement used to determine the "salary_range". In this case, we define three conditions using the WHEN keyword. If an employee's salary is less than 50,000, the query returns 'Low'. If the salary is between 50,000 and 80,000, the query returns 'Medium'. Finally, if the salary is greater than or equal to 80,000, the query returns 'High'.

The SQL CASE statement can be used for a variety of purposes, including data transformation, data cleansing, and data validation. With its flexibility and power, it's a valuable tool to have in your SQL toolbox.