CROSS JOIN for every combination, LATERAL for a query per row

Two joins with no ordinary ON condition:

• CROSS JOIN pairs every row of one table with every row of the other: 6 teams × 5 dismissal kinds = 30 rows. Useful for building a complete grid that you then fill in.
• LATERAL (or CROSS APPLY / OUTER APPLY) runs a subquery once for each row on its left, and the subquery can refer to that row. Useful for "the top one for each".

Part 1: a complete grid with CROSS JOIN

The test case

For Leeds and Tampa, how many dismissals of each kind did they take while fielding in 2026? Show every kind for both teams, with 0 where there were none.

Leeds didn't stump anyone all season. The 0 only shows up because the grid has a row for it.

Way 1: CROSS JOIN, then LEFT JOIN the facts

with kinds as (
  select 'bowled' as kind from dual union all select 'caught' from dual union all
  select 'lbw' from dual union all select 'run out' from dual union all select 'stumped' from dual
)
select t.team_code, k.kind, count(b.wicket_kind) as n
from   cr_teams t
cross  join kinds k
left   join cr_balls_v b
       on  b.bowling_team_id = t.team_id
       and b.wicket_kind     = k.kind
       and b.season          = 2026
where  t.team_code in ('LDS', 'TPA')
group  by t.team_code, k.kind
order  by t.team_code, k.kind;

The cross join makes all ten team-and-kind combinations first; the LEFT JOIN then attaches the dismissals, and count(b.wicket_kind) counts only real ones, so empty combinations show 0.

Way 2: PARTITION BY outer join

with kinds as (
  select 'bowled' as kind from dual union all select 'caught' from dual union all
  select 'lbw' from dual union all select 'run out' from dual union all select 'stumped' from dual
),
facts as (
  select bowling_team_id as team_id, wicket_kind as kind, count(*) as n
  from   cr_balls_v
  where  season = 2026 and wicket_kind is not null
  group  by bowling_team_id, wicket_kind
)
select t.team_code, k.kind, nvl(f.n, 0) as n
from   facts f partition by (f.team_id)
right  join kinds k on k.kind = f.kind
join   cr_teams t on t.team_id = f.team_id
where  t.team_code in ('LDS', 'TPA')
order  by t.team_code, k.kind;

An Oracle speciality for filling gaps: facts PARTITION BY (team_id) RIGHT JOIN kinds means "for each team separately, outer-join to every kind". The partition column is filled in on the padded rows too, which is why the join to cr_teams still works for Leeds' missing stumping. Built for "every day of the month, for every product" reports.

Part 2: one subquery per row with LATERAL

The test case

Each team's highest individual score in a single 2026 match: the batter, the match and the runs.

Harry Whitcombe's 132 in match 24 is the only century of 2026.

Way 1: CROSS APPLY with FETCH FIRST

select t.team_code, best.player_name, best.match_id, best.runs
from   cr_teams t
cross  apply (select p.player_name, b.match_id, sum(b.runs_batter) as runs
              from   cr_balls_v b
              join   cr_players p on p.player_id = b.batter_id
              where  b.season = 2026
              and    p.team_id = t.team_id                 -- refers to the outer row
              group  by p.player_name, b.match_id
              order  by runs desc, b.match_id, p.player_name
              fetch  first 1 row only) best
order  by t.team_code;

The subquery runs once per team and can use t.team_id, which an ordinary inline view can't. FETCH FIRST 1 ROW ONLY inside it picks that team's best innings. CROSS APPLY drops a team whose subquery returns nothing; OUTER APPLY would keep it with nulls. LATERAL (...) is the SQL-standard spelling of the same thing.

Way 2: ROW_NUMBER

select team_code, player_name, match_id, runs
from   (select t.team_code, p.player_name, b.match_id, sum(b.runs_batter) as runs,
               row_number() over (partition by t.team_code
                                  order by sum(b.runs_batter) desc, b.match_id, p.player_name) as rn
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.batter_id
        join   cr_teams   t on t.team_id   = p.team_id
        where  b.season = 2026
        group  by t.team_code, p.player_name, b.match_id)
where  rn = 1
order  by team_code;

Top-n per group the window-function way (page 5.7). Usually the faster choice when you want it for every group; APPLY shines when the outer list is short or the inner query is complicated.

19c and 26ai

The same on both. CROSS APPLY, OUTER APPLY and LATERAL arrived in 12c.

Gotchas

• An accidental cross join (a forgotten join condition) is the most common way to get millions of rows from two small tables.
• CROSS APPLY vs OUTER APPLY is inner vs outer: decide whether rows with no result should stay.
• The ORDER BY inside the lateral subquery matters. Without it, "first 1 row" is any row.