UNION, INTERSECT and MINUS

Set operators combine the rows of two queries with the same number of columns:

(26ai adds INTERSECT ALL, MINUS ALL and the standard name EXCEPT: page 12.5.)

The test case

How many players batted in 2025, how many in 2026, how many in both seasons, and how many in at least one?

Expected result

61 + 65 − 60 = 66. Set arithmetic, the way you did it at school with circles.

Way 1: set operators

with y25 as (select batter_id from cr_balls_v where season = 2025),
     y26 as (select batter_id from cr_balls_v where season = 2026)
select (select count(distinct batter_id) from y25)                                   as batted_2025,
       (select count(distinct batter_id) from y26)                                   as batted_2026,
       (select count(*) from (select batter_id from y25 intersect select batter_id from y26)) as in_both,
       (select count(*) from (select batter_id from y25 union     select batter_id from y26)) as either
from   dual;

INTERSECT and UNION both remove duplicates, so each counts players, not balls. UNION ALL here would count every delivery from both seasons.

Way 2: one pass with conditional aggregation

select count(case when in_25 = 1 then 1 end)                 as batted_2025,
       count(case when in_26 = 1 then 1 end)                 as batted_2026,
       count(case when in_25 = 1 and in_26 = 1 then 1 end)   as in_both,
       count(*)                                              as either
from   (select batter_id,
               max(case when season = 2025 then 1 else 0 end) as in_25,
               max(case when season = 2026 then 1 else 0 end) as in_26
        from   cr_balls_v
        group  by batter_id);

One row per batter with two flags, then count the flags. Reads the data once instead of four times; on big tables, that's the one to use.

Way 3: EXISTS

select (select count(*) from cr_players p
        where exists (select 1 from cr_balls_v b where b.batter_id = p.player_id and b.season = 2025)) as batted_2025,
       (select count(*) from cr_players p
        where exists (select 1 from cr_balls_v b where b.batter_id = p.player_id and b.season = 2026)) as batted_2026,
       (select count(*) from cr_players p
        where exists (select 1 from cr_balls_v b where b.batter_id = p.player_id and b.season = 2025)
        and   exists (select 1 from cr_balls_v b where b.batter_id = p.player_id and b.season = 2026)) as in_both,
       (select count(*) from cr_players p
        where exists (select 1 from cr_balls_v b where b.batter_id = p.player_id))                    as either
from   dual;

Start from the players and ask questions about each. Wordy, but each line reads exactly like the question.

19c and 26ai

The same on both.

Gotchas

• UNION sorts or hashes to remove duplicates. If you know there are none (or want them), use UNION ALL: it's cheaper.
• Columns match by position, not name. The column names come from the first query.
• One ORDER BY, at the very end, for the whole combined result.