# Using CASE in the WHERE statement of SQL

## Problem statement

I was working on a baseball query when I discovered that I could not use a simple WHERE statement to select Padres at-bats. However, I knew the information was there. The teams were under the “away_team” and “home_team” columns. Here’s what I was working with:

batter	game_date	events	inning_topbot	away_team	home_team
0	544725.0	2019-07-19	double	Top	CWS	TB
1	641553.0	2019-07-19	single	Top	CWS	TB
2	570560.0	2019-07-19	field_out	Top	CWS	TB
3	445055.0	2019-07-19	single	Top	CWS	TB
4	602922.0	2019-07-19	strikeout	Top	CWS	TB


I could use the “inning_topbot” as a conditional for my selection. I had used CASE in a SELECT statement before, but luckily I realized CASE can be used in a variety of statements including with WHERE. Here is the query that gave me what I wanted. (I’m doing this in a Jupyter notebook hence some of the pandas syntax.)

## Working query

sql_query = """
SELECT
batter,
game_date,
events,
inning_topbot,
away_team,
home_team
FROM statcast

--I'm doing multiple filtering statements
WHERE "events" IS NOT NULL
AND "game_date" > '2019-03-03'
AND

--Here is the CASE line
CASE WHEN "inning_topbot"='Top' THEN away_team='SD'
ELSE home_team='SD' END

--I ordered by random so I can see different examples and confirm my query
ORDER BY RANDOM();
"""
df_query


Here are the heads and tails of the output:

batter	game_date	events	inning_topbot	away_team	home_team
0	614177.0	2019-05-02	single	Top	SD	ATL
1	595978.0	2019-07-26	strikeout	Bot	SF	SD
2	571976.0	2019-03-31	strikeout	Bot	SF	SD
3	595978.0	2019-05-31	strikeout	Bot	MIA	SD
4	664119.0	2019-08-26	double	Bot	LAD	SD
...	...	...	...	...	...	...
6006	641778.0	2019-04-14	strikeout	Top	SD	ARI
6007	595978.0	2019-05-25	strikeout	Top	SD	TOR
6008	665487.0	2019-04-11	sac_fly	Top	SD	ARI
6009	614177.0	2019-05-04	strikeout	Bot	LAD	SD
6010	642336.0	2019-08-19	field_out	Top	SD	CIN


Categories:

Updated: