SQL CASE expression is a powerful feature that enables you to conditionally evaluate an expression and return different results depending on the result of the evaluation. It provides a way to write a more flexible and complex query by allowing you to create different cases or scenarios in which different expressions or conditions are met.
Why and When to Use SQL CASE Expression?
SQL CASE expression is useful in situations where you want to apply conditional logic to an expression in your SQL query.
It allows you to compare one or more values to a set of conditions and return a result depending on the match.
You can use the SQL CASE expression to perform the following:
You can use the SQL CASE expression to transform data by applying different expressions to different conditions. This can be useful when you want to convert one value to another based on certain conditions.
Create custom grouping
You can use the SQL CASE expression to create custom groups based on certain conditions. For example, you can group products by their category or price range.
Calculate derived values
You can use the SQL CASE expression to calculate derived values based on certain conditions. For example, you can calculate discounts based on the order amount.
How to Use SQL CASE Expression?
SQL CASE expression has two forms: simple and searched.
The simple CASE expression has the following syntax:
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ... ELSE default_result END
The searched CASE expression has the following syntax:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END
The “expression” in the simple CASE expression represents the expression to be evaluated, and the “value1”, “value2”, etc. represent the values that are being compared to the expression. The “result1”, “result2”, etc. are the values that are returned when the corresponding value is matched. The “default_result” is the value that is returned when none of the values are matched.
The “condition1”, “condition2”, etc. in the searched CASE expression represent the conditions to be evaluated, and the “result1”, “result2”, etc. are the values that are returned when the corresponding condition is matched. The “default_result” is the value that is returned when none of the conditions are matched.
SQL CASE Expression Examples:
Here are some examples of how to use SQL CASE expression:
Simple CASE Expression
SELECT product_name, unit_price, CASE unit_price WHEN 10 THEN 'Low price' WHEN 20 THEN 'Medium price' WHEN 30 THEN 'High price' ELSE 'Unknown price' END AS price_category FROM products;
In this example, the SQL CASE expression is used to categorize products based on their unit price. If the unit price matches any of the values, the corresponding price category is returned. If none of the values match, “Unknown price” is returned.
Searched CASE Expression
SELECT order_id, order_date, CASE WHEN order_total < 50 THEN 'Low' WHEN order_total BETWEEN 50 AND 100 THEN 'Medium' ELSE 'High' END AS order_category FROM orders;
In this example, the SQL CASE expression is used to categorize orders based on their total amount. If the order total is less than 50, “Low” is returned. If the order total is between 50 and 100, “Medium” is returned. If the order total is greater than 100, “High” is returned.
Using CASE expression with aggregate functions
SELECT SUM(CASE WHEN age >= 18 THEN 1 ELSE 0 END) AS num_adults, SUM(CASE WHEN age < 18 THEN 1 ELSE 0 END) AS num_children, AVG( CASE WHEN gender = 'male' THEN income ELSE NULL END ) AS avg_male_income, AVG( CASE WHEN gender = 'female' THEN income ELSE NULL END ) AS avg_female_income FROM users;
This query uses the CASE expression inside aggregate functions to calculate the number of adults and children in a table, as well as the average income for males and females separately.
Using CASE expression with multiple conditions
SELECT name, CASE WHEN age < 18 THEN 'Minor' WHEN age BETWEEN 18 AND 65 THEN 'Adult' ELSE 'Senior' END AS age_group FROM users;
This query uses a CASE expression with multiple conditions to categorize users into different age groups based on their age.
Using CASE expression with subqueries:
SELECT name, ( SELECT COUNT(*) FROM orders WHERE customer_id = users.id ) AS num_orders, CASE WHEN ( SELECT COUNT(*) FROM orders WHERE customer_id = users.id ) >= 3 THEN 'Frequent customer' ELSE 'Occasional customer' END AS customer_type FROM users;
This query uses a CASE expression with a subquery to determine whether a user is a frequent or occasional customer based on the number of orders they have placed.
Using CASE expression with date functions:
SELECT order_date, CASE WHEN DATEPART(month, order_date) BETWEEN 1 AND 3 THEN 'Q1' WHEN DATEPART(month, order_date) BETWEEN 4 AND 6 THEN 'Q2' WHEN DATEPART(month, order_date) BETWEEN 7 AND 9 THEN 'Q3' ELSE 'Q4' END AS quarter FROM orders;
This query uses a CASE expression with the DATEPART function to group orders into quarters based on their order date.
Using CASE expression with joins:
SELECT users.name, CASE WHEN orders.id IS NULL THEN 'No orders' ELSE 'Has orders' END AS order_status FROM users LEFT JOIN orders ON users.id = orders.customer_id;
This query uses a CASE expression with a LEFT JOIN to determine whether a user has placed any orders or not. If the user has not placed any orders, the query returns “No orders”, otherwise it returns “Has orders”.