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.
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.
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.