Using Multiple Conditions with CASE Statements in SQL

The CASE statement is a powerful tool in SQL that allows you to perform conditional logic within your queries. It’s often used to evaluate an expression and return a specific value based on certain conditions. In this tutorial, we’ll explore how to use multiple conditions with CASE statements in SQL.

Introduction to CASE Statements

Before diving into using multiple conditions, let’s quickly review the basic syntax of a CASE statement:

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

The CASE statement evaluates each condition in order and returns the corresponding result when a condition is true. If none of the conditions are true, it returns the default result specified by the ELSE clause.

Using Multiple Conditions

To use multiple conditions with a CASE statement, you can simply add more WHEN clauses:

CASE 
  WHEN column1 = 'value1' THEN result1
  WHEN column2 > 10 THEN result2
  WHEN column3 LIKE '%pattern%' THEN result3
  ...
  ELSE default_result
END

Each WHEN clause is evaluated independently, and the first condition that is true determines the result.

Using AND and OR Operators

You can also use AND and OR operators to combine multiple conditions within a single WHEN clause:

CASE 
  WHEN column1 = 'value1' AND column2 > 10 THEN result1
  WHEN column3 LIKE '%pattern%' OR column4 IS NULL THEN result2
  ...
  ELSE default_result
END

This allows you to create more complex conditions that involve multiple columns or values.

Nested CASE Statements

In some cases, you may need to use a nested CASE statement to evaluate additional conditions:

CASE 
  WHEN column1 = 'value1' THEN
    CASE 
      WHEN column2 > 10 THEN result1
      ELSE result2
    END
  WHEN column3 LIKE '%pattern%' THEN result3
  ...
  ELSE default_result
END

This allows you to create a hierarchical structure of conditions and results.

Example Use Case

Suppose we have a table called students with columns name, age, and grade. We want to write a query that assigns a label to each student based on their age and grade:

SELECT name, age, grade,
  CASE 
    WHEN age < 18 AND grade > 80 THEN 'Young Achiever'
    WHEN age >= 18 AND grade > 90 THEN 'High Achiever'
    WHEN grade < 70 THEN 'Needs Improvement'
    ELSE 'Average Student'
  END AS label
FROM students;

This query uses multiple conditions and nested logic to assign a label to each student based on their age and grade.

Best Practices

When using multiple conditions with CASE statements, keep the following best practices in mind:

• Use meaningful column aliases and table names to make your queries easier to read.
• Avoid using complex conditions that are difficult to understand or maintain.
• Test your queries thoroughly to ensure they produce the expected results.
• Consider using a separate function or stored procedure to encapsulate complex logic.

By following these best practices and using multiple conditions with CASE statements effectively, you can write more efficient and readable SQL queries that solve real-world problems.