SQL Tutorial: In CASE things get more complex

Key Takeaways

now that you understand the basics of case statements let's set up some more complex logical tests previously we covered case statements with one logical test in a when statement returning outcomes based on whether that test is true or false this example tests whether home or away goals were higher and identifies them as wins for the team that had a higher score everything else is categorized as a type the resulting table has one column identifying matches as one of three possible outcomes if you want to test multiple logical conditions in a case statement you can use and inside your win Clause for example let's see if each match was played and won by the team Chelsea let's see the case statement in this query each when Clause contains two logical tests the first tests if a home team ID identifies Chelsea and then it tests if the home team scored higher than the away team if both conditions are true the new column output returns the phrase Chelsea home win the opposite set set of conditions are included in a second when statement if the away team ID belongs to Chelsea and scored higher then the output returns Chelsea away win all other matches are categorized as a loss or a tie here's the resulting table when testing logical conditions it's important to carefully consider which rows of your data are part of your else Clause and if they're categorized correctly here's the same case statement from the previous slide but the where filter has been removed without this filter your else Klaus will categorize all matches played by anyone who don't meet these first two conditions as loss or tie here are the results of this query a quick look at it shows that the first few matches are all categorized as loss or tie but neither the home team ID or away team ID belong to Chelsea the easiest way to correct for this is to ensure you add specific filters in the where clause that exclude all teams where Chelsea did not play here we specify this by using an or statement in where which retrieves only the results where ID 8 4 5 5 is present in the home team ID or away team ID columns the resulting table from earlier with the team IDs in bold here clearly specifies whether Chelsea was home or away team it's also important to consider what your else Clause is doing these two queries here are identical except for the else no statement specified in the second they both return identical results a table with quite a few null results but what if you want to exclude them let's say we're only interested in viewing the results of games or Chelsea won and we don't care if they lose or tie just like in the previous example simply removing the else clause will still retrieve those results and a lot of null values to correct for this you can treat the entire case statement as a column to filter by in your where clause just like any other column in order to filter a query by a case statement you include the entire case statement except it's alias in where then specify what you want to include or exclude for this query I want to keep all rows where the case statement is not know my resulting table now only includes Chelsea's home-and-away wins and I don't need to filter by their team ID anymore okay let's practice some more complex case statements