SQL: CASE with multiple WHEN conditions | 3 Simple Ways

Quick Example: SQL CASE with multiple WHEN conditions

sql

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;

sql

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:

sql

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:

sql

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:

sql

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:

sql

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.

Published on: Mar 30, 2023