Filtering your CASE statement
Let's generate a list of matches won by Italy's Bologna team! There are quite a few additional teams in the two tables, so a key part of generating a usable query will be using your CASE
statement as a filter in the WHERE
clause.
CASE
statements allow you to categorize data that you're interested in -- and exclude data you're not interested in. In order to do this, you can use a CASE
statement as a filter in the WHERE
statement to remove output you don't want to see.
Here is how you might set that up:
SELECT *
FROM table
WHERE
CASE WHEN a > 5 THEN 'Keep'
WHEN a <= 5 THEN 'Exclude' END = 'Keep';
In essence, you can use the CASE
statement as a filtering column like any other column in your database. The only difference is that you don't alias the statement in WHERE
.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Use the
CASE
statement in theWHERE
clause to filter wherehometeam_id
is9857
and home goals exceed away goals. - Use the
CASE
statement in theWHERE
clause to filter whereawayteam_id
is9857
and away goals exceed home goals. - End the
CASE
statement by excluding games that Bologna did not win.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
season,
date,
home_goal,
away_goal
FROM matches_italy
WHERE
-- Find games where home_goal is more than away_goal
___ ___ hometeam_id = 9857 ___ home_goal ___ away_goal ___ 'Bologna Win'
-- Find games where away_goal is more than home_goal
___ awayteam_id = 9857 ___ away_goal ___ home_goal ___ 'Bologna Win'
-- Exclude games not won by Bologna
___ IS NOT ___;