SQL CASE Expression

SQL CASE expression is a powerful tool that allows you to add conditional logic to your SQL queries.

It can be used in a SELECT, UPDATE, or DELETE statement to control the flow of data based on certain conditions.

In this guide, we will explore the different ways in which you can use the CASE expression in SQL and provide examples of how to use it.

What is the SQL CASE Expression?

The SQL CASE expression is a way to add conditional logic to your SQL statements.

It evaluates a set of conditions and returns a different result based on the outcome of those conditions.

The basic syntax for a CASE expression is as follows:

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

The WHEN clause is used to specify the condition to be evaluated, and the THEN clause is used to specify the result that should be returned if the condition is true.

The ELSE clause is used to specify the result that should be returned if none of the conditions are true.

Simple CASE Expressions

A simple CASE expression is used to evaluate a single expression and return a different result based on the outcome of that expression.

The basic syntax for a simple CASE expression is as follows:

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE resultN
END

For example, consider a table named employees with a column named salary and another column named salary_range.

We can use a simple CASE expression to update the salary_range column based on the value of the salary column:

UPDATE employees
SET salary_range =
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary >= 30000 AND salary < 60000 THEN 'Medium'
WHEN salary >= 60000 THEN 'High'
ELSE 'Unknown'
END

Searched CASE Expressions

A searched CASE expression is used to evaluate multiple expressions and return a different result based on the outcome of those expressions.

The basic syntax for a searched CASE expression is as follows:

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

For example, consider a table named orders with a column named status and another column named status_description.

We can use a searched CASE expression to update the status_description column based on the value of the status column:

UPDATE orders
SET status_description =
CASE
WHEN status = 'Pending' THEN 'Order is pending'
WHEN status = 'Shipped' THEN 'Order has been shipped'
WHEN status = 'Delivered' THEN 'Order has been delivered'
ELSE 'Unknown status'
END

Using CASE Expressions in SELECT Statements

CASE expressions can also be used in SELECT statements to control the data that is returned.

For example, consider a table named employees with a column named salary.

We can use a CASE expression to create a new column named salary_range that categorizes the salary of each employee:

SELECT name, salary,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary >= 30000
AND salary < 60000 THEN 'Medium'
WHEN salary >= 60000 THEN 'High'
ELSE 'Unknown'
END as salary_range
FROM employees

This query will return the name, salary, and salary range of all employees in the `employees` table.

Using CASE Expressions in GROUP BY and ORDER BY Clauses

CASE expressions can also be used in GROUP BY and ORDER BY clauses to control the way data is grouped or sorted.

For example, consider a table named `employees` with a column named `salary`.

We can use a CASE expression to group employees by salary range:

SELECT salary_range, COUNT(*) as count
FROM (
SELECT name, salary,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary >= 30000 AND salary < 60000 THEN 'Medium'
WHEN salary >= 60000 THEN 'High'
ELSE 'Unknown'
END as salary_range
FROM employees) as e
GROUP BY salary_range

This query will return the number of employees in each salary range.

We can also use a CASE expression to order the data in a specific way:

SELECT name, salary,
CASE
WHEN salary < 30000 THEN 'Low'
WHEN salary >= 30000 AND salary < 60000 THEN 'Medium'
WHEN salary >= 60000 THEN 'High'
ELSE 'Unknown'
END as salary_range
FROM employees
ORDER BY
CASE salary_range
WHEN 'Low' THEN 1
WHEN 'Medium' THEN 2
WHEN 'High' THEN 3
ELSE 4
END

This query will return all the employees name, salary and salary range and order them by their salary range in the order of High, Medium, Low, Unknown.

Conclusion

SQL CASE expressions provide a powerful way to add conditional logic to your SQL statements.

They can be used in SELECT, UPDATE, or DELETE statements to control the flow of data based on certain conditions.

Whether you’re looking to group or sort data, update columns based on specific values, or simply make your queries more dynamic, CASE expressions are a valuable tool to have in your SQL toolbox.