1. Conditional logic with CASE statements
Now that you've gotten a taste of what we can do with `CASE` statements, let's take it a bit deeper.
2. Using ELSE
Sometimes, things don't neatly fall into a `WHEN`-`THEN` statement. To handle these scenarios, we can use the `ELSE` keyword.
`ELSE` is placed at the bottom of a `CASE` statement and is followed by the value to be returned if none of the conditions above are met. This is a great way to catch edge-cases.
In this example, if a student's grade number does not match the options outlined using `WHEN`-`THEN`, we'll return "Not in High School".
3. Evaluating a condition
So far, we've only used the equal-sign to compare two values. But this isn't the only operators that we can use. We'll use tools like `IN`, greater than or less than, and `BETWEEN` when evaluating a certain condition.
We can also combine the result of multiple conditional statements using operators like `AND`, `OR`, or `NOT`.
One important thing to keep in mind; since Snowflake is a column-oriented database, data is scanned by column rather than row. This makes `CASE` statements quite efficient, especially when compared to row-oriented databases, like Postgres.
4. Categorizing temperatures
Here, we're writing a case statement that evaluates the outside temperature (in Fahrenheit) and assigns a description. If the value is between 70 and 90, including both, the description is "Ideal for Swimming". If the value is between 50 and 70, it's "Perfect for Sports", and so on.
If none of these conditions are met, the description "Extreme Temperatures" is assigned using `ELSE`. We've aliased the new column `AS` temperature_description`".
5. Categorizing temperatures
Based on each day's temperature, the corresponding `temperature_description` is added as a new column to the results. All of this with one `CASE` statement!
6. Combining conditional statements
In the last example, we checked the values of a single column.
But, we can take this one step further by using multiple columns in a `WHEN` statement. Here, we're checking both the temperature and the status outside to determine what to do. If the temperature is more than 70 and it's not raining or windy, we'll go to the beach.
If it's a bit more mild and sunny, it's just right for sports. And if it's snowing or freezing and below, skiing sounds great.
If none of these conditions are met, we'll be staying inside.
7. Combining conditional statements
When we evaluate both the temperature and status outside, our results become more interesting. Check it out!
You may have already guessed it, but we're not limited to columns in a single table; we can use `CASE` statements to evaluate columns in a `JOIN`'ed table. We'll take a look at that shortly.
8. Let's practice!
For now, let's keep mastering `CASE` statement with some exercises!