Using SQL's CASE Statement (With Examples)
When accessing databases in SQL conditional logic is frequently required. The CASE statement is one of the most effective methods in SQL for classifying data generating unique labels or filtering results according to dynamic conditions.
With numerous examples provided this tutorial will walk you through the basics of CASE the distinction between simple and searched CASE statements and how to apply it to actual queries.
The CASE Statement: What Is It?
If-THEN-ELSE logic can be implemented inside a query using SQL’s CASE statement. It returns a value based on the first condition that evaluates to true after evaluating the conditions in order.
It is usually used to add logic straight into your SQL queries in the SELECT, ORDER BY, or WHERE clauses.
The CASE Statement Syntax
CASE statements come in two varieties:
1. Basic CASE
This compares a set of values to a column or expression.
CASE expression
WHEN value101 THEN result101
WHEN value102 THEN result102
…
ELSE default_result
END
2. CASE search
More versatility is provided by allowing whole Boolean expressions in every WHEN clause.
CASE
WHEN condition101 THEN result101
WHEN condition2 THEN result102
…
ELSE default_result
END
Example 1: Using SELECT to Categorize Data
Assume you have a table named employees that has a column for salaries. Employees should be categorized as low, medium or high earners.
SELECT
name,
salary,
CASE
WHEN salary < 400 THEN ‘Low’
WHEN salary BETWEEN 400 AND 700 THEN ‘Medium’
WHEN salary > 700 THEN ‘High’
ELSE ‘Unknown’
END AS salary_category
FROM employees;
- creates the salary_category column.
- groups incomes into specified ranges using logic.
Example 2: Using ORDER BY with CASE
- Assume that you wish to arrange your staff so that “High” earners show up first, followed by
- “Medium,” and then “Low.” An order can contain CASE by:
SELECT
name,
salary
FROM employees
ORDER BY
CASE
WHEN salary > 70000 THEN 1
WHEN salary BETWEEN 40000 AND 70000 THEN 2
ELSE 3
END;
Example 3: CASE-Based Conditional Aggregation
Let’s say you wish to use aggregate functions to directly count the number of employees who fit into each category:
SELECT
COUNT(CASE WHEN salary < 40000 THEN 1 END) AS low_earners,
COUNT(CASE WHEN salary BETWEEN 40000 AND 70000 THEN 1 END) AS mid_earners,
COUNT(CASE WHEN salary > 70000 THEN 1 END) AS high_earners
FROM employees;
Example 4: Advanced Filtering: CASE in the WHERE Clause
Suppose you wish to use dynamic filtering such as returning workers with subpar performance only if they work in a crucial department:
SELECT *
FROM employees
WHERE
CASE
WHEN department = ‘Operations’ THEN performance_rating < 3
ELSE TRUE
END;
Example 5: Using CASE to Format Output
Database values can occasionally be changed to make them easier to read. Assume that a table has the status codes 1, 2 and 3. They should be displayed as text labels.
SELECT
order_id,
customer_id,
CASE status
WHEN 1 THEN ‘Pending’
WHEN 2 THEN ‘Shipped’
WHEN 3 THEN ‘Delivered’
ELSE ‘Unknown’
END AS status_label
FROM orders;
The Best Methods for Applying CASE
- Make sure it’s readable: Readability may be harmed by an excessive number of nested or complicated CASE statements.
- Make good use of ELSE: Unless you are positive that every row satisfies a WHEN condition always include an ELSE clause.
- For conditions including ranges, null checks or comparisons, prefer searching CASE since it is more adaptable and superior.
In conclusion
One useful feature in SQL that lets you incorporate logic straight into your queries is the CASE statement. CASE allows you the freedom to make your searches more intelligent and dynamic whether you’re conducting conditional aggregation dynamically sorting results categorizing values or creating custom labels.
Think about if a CASE statement would be the most streamlined approach the next time you encounter a complicated data scenario. It can improve the expressiveness, maintainability and power of your SQL queries when used carefully.
