Relational division: against every opponent

"Customers who bought every product in the range", "students who passed all the modules", "suppliers who supply every part". The for all question is called relational division, and SQL has no FOR ALL keyword, so there are a few classic ways to spell it.

The test case

2026 batters who hit a boundary (a four or a six) against every one of the five other teams.

Expected result

Eight batters found the rope against everyone. Nobody managed it with sixes alone: four batters, Siddharth Iyer among them, cleared the rope against four of the five. Always one stubborn opponent.

Way 1: count them

select p.player_name
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
where  b.season = 2026 and b.runs_batter in (4, 6)
group  by p.player_name
having count(distinct b.bowling_team_id) = 5
order  by p.player_name;

Five opponents, so "every opponent" is "five different opponents". Simple, and correct as long as the 5 is right. With a changing set (products in a range, modules on a course), compare to a COUNT(*) subquery instead of a literal.

Way 2: a double NOT EXISTS

select p.player_name
from   cr_players p
where  exists (select 1 from cr_balls_v b where b.batter_id = p.player_id and b.season = 2026)
and    not exists (
         select 1
         from   cr_teams t
         where  t.team_id <> p.team_id
         and    not exists (select 1
                            from   cr_balls_v b
                            where  b.batter_id = p.player_id
                            and    b.bowling_team_id = t.team_id
                            and    b.season = 2026
                            and    b.runs_batter in (4, 6)))
order  by p.player_name;

Read it as: "there is no opposing team that he has no boundary against". Two negatives make the for all. It's the textbook form, and it doesn't need to know how many opponents there are.

Way 3: MINUS

select p.player_name
from   cr_players p
where  exists (select 1 from cr_balls_v b where b.batter_id = p.player_id and b.season = 2026)
and    not exists (
         select t.team_id from cr_teams t where t.team_id <> p.team_id
         minus
         select b.bowling_team_id from cr_balls_v b
         where  b.batter_id = p.player_id and b.season = 2026 and b.runs_batter in (4, 6))
order  by p.player_name;

"All opponents, minus the ones he hit a boundary against": if nothing is left, he got them all.

19c and 26ai

The same on both.

Gotchas

• The empty case. A player who never batted has no opponents left to fail against, so Ways 2 and 3 would include him ("vacuously true"). The EXISTS ... season = 2026 line rules that out. Way 1 can't fall into it.
• COUNT(DISTINCT ...), not COUNT(*): a batter with 30 boundaries all against Auckland isn't what we're after.