codedamn

CASE WHEN syntax in SQL – Examples and usage

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: Every CASE must conclude with END.
  • 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.

0/10000

No comments so far