Joining a table to itself

Nothing stops a table (or a query result) from being joined to itself. You do it whenever two rows of the same table belong side by side: a match's two innings, an employee and their manager, this month and last month.

The test case

For the first five matches of 2026, show the home team's score and the away team's score side by side, as runs/wickets.

The scores come from the deliveries: one innings total per team per match. Each match needs two of those totals on one row.

Expected result

All five finished within ten runs of each other, two of them by a single run. The league's scriptwriters earn their money.

Way 1: join the innings totals twice

with innings as (
  select match_id, batting_team_id, sum(total_runs) as runs, sum(is_wicket) as wkts
  from   cr_balls_v
  group  by match_id, batting_team_id
)
select m.match_id,
       h.team_code                as home,
       hs.runs || '/' || hs.wkts  as home_score,
       a.team_code                as away,
       aw.runs || '/' || aw.wkts  as away_score
from   cr_matches m
join   innings hs on hs.match_id = m.match_id and hs.batting_team_id = m.home_team_id
join   innings aw on aw.match_id = m.match_id and aw.batting_team_id = m.away_team_id
join   cr_teams h on h.team_id = m.home_team_id
join   cr_teams a on a.team_id = m.away_team_id
where  m.season = 2026 and m.match_no <= 5
order  by m.match_id;

innings is joined twice under two aliases: hs is "the innings where the home team batted", aw "the one where the away team batted". Same table, two roles.

Way 2: conditional aggregation, no self-join

select m.match_id,
       h.team_code as home,
       sum(case when b.batting_team_id = m.home_team_id then b.total_runs end) || '/' ||
       sum(case when b.batting_team_id = m.home_team_id then b.is_wicket  end)  as home_score,
       a.team_code as away,
       sum(case when b.batting_team_id = m.away_team_id then b.total_runs end) || '/' ||
       sum(case when b.batting_team_id = m.away_team_id then b.is_wicket  end)  as away_score
from   cr_matches m
join   cr_balls_v b on b.match_id = m.match_id
join   cr_teams   h on h.team_id  = m.home_team_id
join   cr_teams   a on a.team_id  = m.away_team_id
where  m.season = 2026 and m.match_no <= 5
group  by m.match_id, h.team_code, a.team_code
order  by m.match_id;

Read every ball once and add each one to the home or the away total depending on who was batting. SUM(CASE ... END) skips the rows where the CASE is null. This "pivot by hand" is often faster than a self-join, because the data is read once (Part 7 shows PIVOT).

19c and 26ai

The same on both.

Gotchas

• Aliases are required when a table appears twice; otherwise Oracle can't tell which copy you mean.
• Self-joins on big tables can explode. Join on something selective (here match and team).
• Concatenating numbers with || converts them to text with the session's number format. Fine for whole numbers; use TO_CHAR with a format when decimals are involved.