Practice: three join problems

select b.match_id, b.over_ball, b.wicket_kind, bw.player_name as bowler
from   cr_balls_v b
join   cr_players po on po.player_id = b.player_out_id
left   join cr_players bw on bw.player_id = b.bowler_id and b.is_bowler_wicket = 1
where  po.player_name = 'Karthik Raman'
order  by b.match_id;

select b.match_id, b.over_ball, b.wicket_kind,
       case when b.is_bowler_wicket = 1
            then (select bw.player_name from cr_players bw where bw.player_id = b.bowler_id)
       end as bowler
from   cr_balls_v b
where  b.player_out_id = (select player_id from cr_players where player_name = 'Karthik Raman')
order  by b.match_id;

The trick in the first answer: and b.is_bowler_wicket = 1 sits in the ON of the LEFT JOIN, so for a run out the join finds no bowler and leaves null, while the row itself survives.

select p.player_name, t.team_code, p.bowling_style
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  p.bowling_style is not null
and    not exists (select 1 from cr_deliveries d where d.bowler_id = p.player_id)
order  by t.team_code, p.player_name;

select p.player_name, t.team_code, p.bowling_style
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
left   join (select distinct bowler_id from cr_deliveries) d on d.bowler_id = p.player_id
where  p.bowling_style is not null
and    d.bowler_id is null
order  by t.team_code, p.player_name;

Each team uses five front-line bowlers, so the other bowlers in the squad carry their bowling boots around all season for nothing.

select m.match_id, o.team_name as opponent, m.win_type, m.win_margin
from   cr_matches m
join   cr_teams o on o.team_id = case when m.home_team_id = 2 then m.away_team_id
                                      else m.home_team_id end
where  m.season = 2026
and    m.winner_id = 2
order  by m.match_id;

select m.match_id, o.team_name as opponent, m.win_type, m.win_margin
from   cr_matches m
join   cr_teams o on o.team_id in (m.home_team_id, m.away_team_id) and o.team_id <> 2
where  m.season = 2026
and    m.winner_id = 2
order  by m.match_id;

A join condition can be any expression: a CASE that picks "the other team", or an IN list plus "not Chennai". Both pick exactly one opponent per match.