SQL is a powerful query language used to manage and manipulate relational databases. One of its most useful features is the ability to make decisions within queries using the CASE statement. Today, we'll delve into a specific use of CASE: managing three conditions in a single statement.
Before diving into the three-condition CASE, let's quickly recap the basic concept. The SQL CASE statement executes different actions based on different conditions. It's like an 'if, else if, else' structure in programming, designed to simplify and clarify complex decision-making processes.
The Anatomy of a CASE Statement
A basic CASE statement has the following syntax:
```sql CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultn END ```
The expression is tested against each WHEN condition. If a condition is true, the corresponding result is returned. If no conditions are met, the result of the ELSE clause is returned.
One Condition: The Simple CASE
The most straightforward use of CASE involves a single condition. Here's an example that assigns a grade based on a test score:
```sql SELECT score, CASE WHEN score >= 90 THEN 'A' ELSE 'B' END AS grade FROM students; ```
In this example, students scoring 90 or above get an 'A', while everyone else gets a 'B'.
Two Conditions: Nesting CASEs
If you need to evaluate two conditions, you can nest CASE statements. Let's refine the previous example to also distinguish between 'A' and 'A-':
```sql SELECT score, CASE WHEN score >= 90 THEN CASE WHEN score >= 97 THEN 'A' ELSE 'A-' END ELSE 'B' END AS grade FROM students; ```
In this case, if the score is 90 or above, the nested CASE statement decides whether to assign an 'A' or 'A-'. If not, a 'B' is given.
Combining Three Conditions: The CASE Triangle
Adding a third condition to a CASE statement requires a different approach, as nesting two CASE statements can quickly become unwieldy. A better strategy is to explicitly list out the possible outcomes and their corresponding conditions.
Using UNION for Three Conditions
One way to combine three conditions is by using a UNION clause to unite three separate CASE statements. For instance, let's define grades for scores 90-100 as 'A', 'A-', and 'B+' (for score 85):
First CASE:
```sql SELECT score, CASE WHEN score >= 97 THEN 'A' ELSE 'A-' END AS grade FROM students; ```
Second CASE:
```sql SELECT score, CASE WHEN score >= 90 AND score < 97 THEN 'A-' ELSE 'B+' END AS grade FROM students; ```
Third CASE:
```sql SELECT score, CASE WHEN score < 90 THEN 'B' END AS grade FROM students; ```
Combining these with UNION gives:
```sql SELECT score, grade FROM ( SELECT score, CASE WHEN score >= 97 THEN 'A' ELSE 'A-' END AS grade FROM students UNION ALL SELECT score, CASE WHEN score >= 90 AND score < 97 THEN 'A-' ELSE 'B+' END AS grade FROM students UNION ALL SELECT score, CASE WHEN score < 90 THEN 'B' END AS grade FROM students ) QUERY; ```
This approach works, but it's not the most elegant or maintainable solution. A cleaner way is to use the CASE expression in a SELECT statement with the WHEN and THEN clauses layered.
The Multi-Branched CASE Statement
For three conditions, you can use the following syntax:
```sql SELECT score, CASE WHEN score >= 97 THEN 'A' WHEN score >= 90 AND score < 97 THEN 'A-' ELSE 'B' END AS grade FROM students; ```
Here, if the score is 97 or above, the grade is 'A'. If it's between 90 and 97, it's 'A-', and if none of the above apply, the student gets a 'B'.
The elegance of this method lies in its simplicity. It's clearer and easier to maintain than using UNION, and better handles a range of values (in this case, 90-97) for a single grade. It's a great illustration of how SQL can simplify complex logic with a straightforward language construct.
The power of SQL lies in its versatility and robustness. The CASE statement is a prime example of this - through simple syntax, it enables complex decision-making in a clear and efficient manner. Whether you're handling one, two, or three conditions, the CASE statement is an invaluable tool in your SQL arsenal.