How do I do multiple CASE WHEN conditions using SQL Server 2008?

sql

best-practices

join

case-statement

byAnton Shumikhin·Jan 12, 2025

--SQL, it's like instant coffee. Immediate results.
SELECT 
  CASE
    WHEN condition1 THEN result1
    --A condition met faster than your coffee gets cold
    WHEN condition2 THEN result2
    ELSE defaultResult
    --Always have a backup plan
  END AS ResultColumn
FROM YourTable;

A CASE expression contains multiple WHEN conditions thus giving us ResultColumn in SQL Server 2008. If the conditions are not met, ELSE part comes to the rescue just like the backup coffee at your desk.

Step by step breakdown

You might be asking, "How do I handle a wide range of conditions?" Here's your dish, served hot and spicy:

Handling complicated conditions

For complex conditions, nest CASE statements. It's like stacked pancakes, but the syrup is extra:

SELECT 
  CASE 
    --Like battling the Monday Blues
    WHEN condition1 THEN 
      CASE 
        WHEN subCondition1 THEN subResult1 
        ELSE subResultDefault
        --In sleep, we trust
      END 
    WHEN condition2 THEN result2 
    ELSE defaultResult 
  END AS ResultColumn 
FROM YourTable;

This approach helps you break down complex scenarios into manageable tasks for a proper decision tree.

Merging conditions with OR

When multiple conditions lead to the same result, save some writing:

SELECT 
  CASE 
    --Less writing, more coffee
    WHEN condition1 OR condition2 THEN result1 
    ELSE defaultResult 
  END AS ResultColumn 
FROM YourTable;

This use of OR can reduce redundancy and keep your query of course short and sweet.

Orders of conditions

Just as ordering a dessert before the main course is not common, CASE evaluates conditions in order:

SELECT 
  CASE 
    --First come, first served
    WHEN highPriorityCondition THEN highPriorityResult 
    WHEN lowerPriorityCondition THEN lowerPriorityResult 
    ELSE defaultResult 
  END AS ResultColumn 
FROM YourTable;

This helps you get a hold of your thought process and prioritize your conditions.

Combining INNER JOIN with CASE

Mix CASE in to INNER JOIN to check multiple tables. It's like checking your fridge and pantry before you start cooking:

SELECT 
  t1.Column1, 
  CASE 
    --It's a match...no Tinder required
    WHEN t1.Column2 = t2.Column2 THEN 'Match' 
    ELSE 'No Match' 
  END AS MatchStatus 
FROM 
  Table1 t1 
INNER JOIN Table2 t2 ON t1.KeyColumn = t2.KeyColumn;

This combines data with practical conditional logic for efficient checks.

A case of conditions: Embracing advanced tips and best practices

Now we understand the basics, but let's become the Sherlock Holmes of SQL and investigate further:

Aliases for readability

Aliases are like good nicknames, they give clarity and are easier to remember:

SELECT 
  CASE 
    --Better than calling it 'that column thingy'
    WHEN condition THEN 'AliasName1' 
    ELSE 'AliasName2' 
  END AS DescriptiveResult 
FROM YourTable;

Aliases provide descriptive names that make your query results a piece of cake to understand.

Scenario consideration

Bear in mind to cover all scenarios. We don't want any NULL surprises in our result:

SELECT 
  CASE 
    WHEN condition1 THEN result1 
    WHEN condition2 THEN result2 
    --Unmatched conditions yield NULL, like socks disappearing in the laundry
    --No ELSE statement here
  END AS ResultColumn 
FROM YourTable;