CASE WHEN syntax in SQL – Examples and usage
Conditional logic is an indispensable component of database programming, enabling data retrieval based on specific conditions, making the presentation of data more meaningful, and allowing for dynamic content based on the logic defined. The CASE WHEN
construct in SQL provides a powerful way to implement this logic, ensuring that data is not only fetched, but also presented in a manner that is contextually relevant and insightful.
Introduction
The CASE WHEN
statement in SQL serves as a conditional expression, akin to ‘if-then-else’ constructs in traditional programming languages. It lets you perform conditional logic within SQL queries, enabling a more flexible and dynamic approach to data retrieval. Its importance cannot be understated – from customizing reports to generating user-specific results, CASE WHEN
is commonly employed to ensure that data insights are aligned with specific requirements.
Basic Syntax
The general structure of the CASE
statement is straightforward. It can be visualized as:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
Here, based on the conditions specified, the corresponding result is returned. If none of the conditions are met, the result after the ELSE
keyword is returned.
Simple Example
Let’s say you want to classify a day as either ‘Weekday’ or ‘Weekend’ based on its name:
SELECT day_name,
CASE
WHEN day_name IN ('Saturday', 'Sunday') THEN 'Weekend'
ELSE 'Weekday'
END AS day_type
FROM days;
Here, if the day_name
is either ‘Saturday’ or ‘Sunday’, ‘Weekend’ will be returned; otherwise, ‘Weekday’ is returned.
Simple CASE Expression
A simple CASE expression compares an expression to a set of simple expressions to determine the result. Its structure is:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE resultN
END
Example
Let’s say you have a table of student scores and you want to classify them into grades:
SELECT student_name, score,
CASE score
WHEN 90 THEN 'A'
WHEN 80 THEN 'B'
WHEN 70 THEN 'C'
ELSE 'D'
END AS grade
FROM students;
Here, scores of 90 will be classified as ‘A’, 80 as ‘B’, and so on.
Searched CASE Expression
Unlike the simple CASE expression that checks against one specific column, a searched CASE expression evaluates a set of boolean conditions to determine the result.
Example
To classify data based on multiple conditions, consider:
SELECT student_name, score,
CASE
WHEN score >= 90 AND attendance > 75 THEN 'A'
WHEN score >= 80 AND attendance > 70 THEN 'B'
ELSE 'C'
END AS grade
FROM students;
Here, both score and attendance are considered to classify the student into grades.
Using CASE in SELECT statements
The CASE
statement can be employed in SELECT
statements to conditionally return specific values based on defined conditions, allowing for dynamic content in query results.
Example
To categorize products based on their prices, you might write:
SELECT product_name, price,
CASE
WHEN price > 1000 THEN 'Expensive'
WHEN price > 500 THEN 'Moderate'
ELSE 'Cheap'
END AS price_category
FROM products;
This query categorizes products into ‘Expensive’, ‘Moderate’, or ‘Cheap’ based on their prices.
Using CASE in ORDER BY clause
In SQL, the CASE
expression is incredibly versatile. One of its prominent uses is within the ORDER BY
clause to conditionally order your query results. By integrating CASE
with ORDER BY
, you can sort results based on a dynamic condition rather than fixed columns.
Example
Imagine a scenario where you have a product table and you want to order products first by those which are out of stock and then by product name alphabetically. Here’s how you could achieve this:
SELECT product_name, in_stock
FROM products
ORDER BY
CASE
WHEN in_stock = 0 THEN 1
ELSE 2
END,
product_name;
Using CASE with Aggregate Functions
The CASE
expression isn’t just for sorting. It’s powerful when combined with aggregate functions like SUM, AVG, and COUNT. This allows for conditional aggregations based on specific criteria.
Example
Suppose you have sales data and want to sum sales conditionally based on regions:
SELECT region,
SUM(CASE WHEN category = 'Electronics' THEN sales ELSE 0 END) AS Electronics_Sales,
SUM(CASE WHEN category = 'Apparel' THEN sales ELSE 0 END) AS Apparel_Sales
FROM sales_data
GROUP BY region;
Using CASE in UPDATE statements
CASE
is also instrumental in conditionally updating table data. You can selectively modify rows based on specific conditions.
Example
To increase prices by 10% for products which are out of stock:
UPDATE products
SET price = price * 1.10
WHERE
CASE
WHEN in_stock = 0 THEN TRUE
ELSE FALSE
END;
Using CASE in INSERT statements
Inserting data based on conditions from another table or set of data is another fantastic use of the CASE
expression.
Example
Suppose you want to insert data into a discounts
table based on product ratings from a reviews
table:
INSERT INTO discounts(product_id, discount_rate)
SELECT product_id,
CASE
WHEN average_rating >= 4.5 THEN 20
WHEN average_rating BETWEEN 4 AND 4.49 THEN 10
ELSE 5
END AS discount_rate
FROM reviews;
Nesting CASE Expressions
Nesting refers to embedding one CASE
expression inside another. This can be useful for handling multiple layers of conditions. However, caution is advised as excessive nesting can make SQL statements harder to read and debug.
Example
Consider a system where you categorize users based on their age:
1SELECT name, age,
2 CASE
3 WHEN age < 13 THEN 'Child'
4 WHEN age BETWEEN 13 AND 19 THEN
5 CASE
6 WHEN has_driver_license = 1 THEN 'Teen (Driver)'
7 ELSE 'Teen'
8 END
9 ELSE 'Adult'
10 END AS age_category
11FROM users;
Limitations and Considerations
While CASE
is versatile, remember:
- Each
CASE
expression can only return one output value. - Overusing or nesting too deeply can lead to confusion and performance issues.
- For optimal performance, always index fields used in your
CASE
conditions.
Common Mistakes and How to Avoid Them
- Overcomplicating logic: Keep your
CASE
logic as simple as possible. If it gets too convoluted, consider breaking it into multiple queries or using temporary tables. - Forgetting the
END
keyword: EveryCASE
must conclude withEND
. - Misordering the conditions: SQL evaluates conditions from top to bottom. Ensure the most specific conditions are checked first.
Alternative Approaches
While CASE
is powerful, SQL offers other methods for conditional logic:
COALESCE Function
The COALESCE
function returns the first non-null value in a list. This can be especially useful when you want to provide a default value for potential NULL entries. Check out the official documentation for COALESCE to dive deeper.
IIF Function
The IIF
function offers a shorthand for a simple CASE
expression, taking three arguments: the condition, the value if true, and the value if false. It’s especially useful for simple conditions. Here’s the official IIF documentation for more details.
Conclusion
The CASE
expression in SQL is a powerful tool that offers dynamic, conditional logic for your queries. From sorting and aggregating to updating and inserting, its applications are broad. We recommend practicing these concepts on codedamn’s platform to solidify your understanding.
Sharing is caring
Did you like what Mayank Sharma wrote? Thank them for their work by sharing it on social media.
No comments so far
Curious about this topic? Continue your journey with these coding courses: