Quick Example: SQL CASE with multiple WHEN conditions
WHEN column_name = 1 THEN 'First'
WHEN column_name = 2 THEN 'Second'
WHEN column_name = 3 THEN 'Third'
WHEN condition1 AND condition2 THEN result1
WHEN condition1 OR condition2 THEN result2
WHEN NOT condition3 THEN result3
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:
WHEN total_purchase >= 1000 THEN 'High Spender'
WHEN total_purchase >= 500 THEN 'Medium Spender'
ELSE 'Low Spender'
END AS spending_category
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:
WHEN SUM(column1) > 100 AND AVG(column2) < 50 THEN result1
WHEN COUNT(column3) > 10 OR MAX(column4) <= 20 THEN result2
GROUP BY column_name;
3. CASE WHEN with a subquery
You can provide a subquery as a part of the WHEN condition:
WHEN (SELECT COUNT(*) FROM another_table WHERE condition1) > 10 THEN result1
WHEN (SELECT SUM(column1) FROM another_table WHERE condition2) < 100 THEN result2
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:
WHEN condition1 THEN result1
WHEN condition2 THEN result2
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.
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.