SQL: CASE with multiple WHEN conditions
Quick Example: SQL CASE with multiple WHEN conditions
SELECT CASE WHEN column_name = 1 THEN 'First' WHEN column_name = 2 THEN 'Second' WHEN column_name = 3 THEN 'Third' ELSE 'Unknown' END FROM Rankings;
SELECT column_name, CASE WHEN condition1 AND condition2 THEN result1 WHEN condition1 OR condition2 THEN result2 WHEN NOT condition3 THEN result3 ELSE default_result END FROM table_name;
How to use CASE with multiple WHEN conditions?
In SQL Server, there are 3 main ways to use CASE with multiple WHEN conditions:
1. Use CASE WHEN with multiple conditions
You can use the SQL CASE WHEN statement for multiple conditions by chaining additional WHEN clauses separated by spaces or newlines. Remember to end the statement with the ELSE clause to provide a default value.
Here's an example:
SELECT customer_id, total_purchase, CASE WHEN total_purchase >= 1000 THEN 'High Spender' WHEN total_purchase >= 500 THEN 'Medium Spender' ELSE 'Low Spender' END AS spending_category FROM customers;
2. Use CASE with multiple conditions
If you want to use multiple conditions within a single WHEN clause, you can use the AND, OR, or NOT logical operators to combine these conditions:
SELECT CASE WHEN SUM(column1) > 100 AND AVG(column2) < 50 THEN result1 WHEN COUNT(column3) > 10 OR MAX(column4) <= 20 THEN result2 ELSE default_result END FROM table_name GROUP BY column_name;
3. CASE WHEN with a subquery
You can provide a subquery as a part of the WHEN condition:
SELECT column_name, CASE WHEN (SELECT COUNT(*) FROM another_table WHERE condition1) > 10 THEN result1 WHEN (SELECT SUM(column1) FROM another_table WHERE condition2) < 100 THEN result2 ELSE default_result END FROM table_name;
Keep in mind: Using CASE WHEN with a subquery may impact performance negatively, especially if the subquery is complex or large. It can increase execution time and may cause suboptimal query plans.
What's the syntax of CASE WHEN?
The CASE WHEN in SQL is a conditional expression used to perform conditional logic in queries. It allows you to perform different actions or calculations based on specific conditions being met.
The basic syntax is:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END
CASE: Begins the expression.
WHEN: Specifies a condition to check.
condition: The condition to be evaluated, e.g., column_name = 'value'.
THEN: Indicates the result to be returned if the condition is met.
result: The value or calculation to return when the condition is true.
ELSE: Optional, specifies a default result if no conditions are met.
default_result: The value or calculation to return if no conditions are true.
END: Ends the CASE expression.
This expression can be used in SELECT, UPDATE, or DELETE statements, as well as in aggregate functions, to apply different calculations or transformations based on specific conditions being met.
Josip Miskovic is a software developer at Americaneagle.com. Josip has 10+ years in experience in developing web applications, mobile apps, and games.Read more posts →
I've used these principles to increase my earnings by 63% in two years. So can you.
Dive into my 7 actionable steps to elevate your career.