MINUS ALL and INTERSECT ALL

UNION removes duplicates and UNION ALL keeps them. 19c had the same choice for UNION only: MINUS and INTERSECT always removed duplicates. Since 21c you can also write MINUS ALL and INTERSECT ALL, which treat each side as a bag of values where duplicates count.

A little example first. Left side: caught, caught, caught, bowled. Right side: caught, bowled, bowled.

MINUS ALL is subtraction with counts, which is exactly what "how many more?" questions need. It's the scorer's favourite operator.

The test case

Every dismissal kind happened more often in 2026 than in 2025. How many more of each?

Way 1: MINUS ALL

26ai only

select wicket_kind, count(*) as how_many_more
from   (select wicket_kind from cr_balls_v where season = 2026 and wicket_kind is not null
        minus all
        select wicket_kind from cr_balls_v where season = 2025 and wicket_kind is not null)
group  by wicket_kind
order  by how_many_more desc, wicket_kind;

Each 2026 dismissal is matched off against one 2025 dismissal of the same kind. What's left over is the extra ones, which we count.

Way 2: count both sides, subtract (19c)

select wicket_kind, how_many_more
from   (select wicket_kind,
               count(case when season = 2026 then 1 end)
             - count(case when season = 2025 then 1 end) as how_many_more
        from   cr_balls_v
        where  wicket_kind is not null
        group  by wicket_kind)
where  how_many_more > 0
order  by how_many_more desc, wicket_kind;

Conditional counting in one pass. It's how you'd write it on 19c, and on a big table it reads the data once instead of twice. The > 0 matters: MINUS ALL can't return a negative count, so a kind that happened less in 2026 would simply not appear.

Way 3: INTERSECT ALL, then subtract

26ai only

with y26 as (select wicket_kind from cr_balls_v where season = 2026 and wicket_kind is not null),
     y25 as (select wicket_kind from cr_balls_v where season = 2025 and wicket_kind is not null),
     both_years as (select wicket_kind from y26 intersect all select wicket_kind from y25)
select k.wicket_kind, k.n - nvl(b.n, 0) as how_many_more
from   (select wicket_kind, count(*) as n from y26 group by wicket_kind) k
left   join (select wicket_kind, count(*) as n from both_years group by wicket_kind) b
       on b.wicket_kind = k.wicket_kind
where  k.n - nvl(b.n, 0) > 0
order  by how_many_more desc, k.wicket_kind;

The long way round, to show what INTERSECT ALL gives you: the part both years have in common (the smaller count of each kind). 2026 minus the common part is the same answer as Way 1.

19c and 26ai

Gotchas

• Plain MINUS removes duplicates first, so it answers "which kinds", never "how many".
• Order doesn't matter for INTERSECT ALL but very much does for MINUS ALL: 2025 minus 2026 here is empty.
• EXCEPT and EXCEPT ALL are the standard names; Oracle 21c and later accept them too.