SQL CASE Expression
SQL CASE Expression
The SQL CASE expression is used to perform conditional logic in SQL queries. It allows you to evaluate a condition and return a value based on that condition. The syntax for the CASE expression is as follows:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
In this syntax, each condition is evaluated in order, and the first condition that is true will return the corresponding result. If none of the conditions are true, the ELSE clause will return the final result.
Here’s an example of using the CASE expression to create a new column that categorizes employees by salary range:
SELECT employee_id, first_name, last_name, salary,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary >= 50000 AND salary < 100000 THEN 'Medium'
ELSE 'High'
END AS salary_range
FROM employees;
In this example, a new column called salary_range is created using the CASE expression. Employees are categorized as “Low”, “Medium”, or “High” based on their salary.
You can also use the CASE expression in conjunction with other SQL clauses, such as the WHERE clause:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary >= 50000 AND salary < 100000 THEN 'Medium'
ELSE 'High'
END = 'Low';
In this example, the WHERE clause is used to filter the result set to only include employees with a salary range of “Low”.
Overall, the SQL CASE expression is a powerful tool for performing conditional logic in SQL queries, and can be used in a variety of scenarios to manipulate and categorize data.