Joins: putting tables side by side

A match row says home_team_id = 6. To show "KHI" you look up team 6 in CR_TEAMS. A join does that lookup for every row at once: it pairs rows from two tables wherever a condition is true.

INNER JOIN (or just JOIN) keeps only the pairs that match. A match with no winner has nothing to pair with in CR_TEAMS, so an inner join to the winner would drop it. That's what outer joins are for (page 2.1).

The test case

The first five matches of 2026: date, home team code, away team code, ground, and the winner's code ("tie" if nobody won).

Expected result

Way 1: ANSI joins

select m.match_id,
       to_char(m.match_date, 'YYYY-MM-DD') as match_date,
       h.team_code                         as home,
       a.team_code                         as away,
       v.venue_name,
       nvl(w.team_code, 'tie')             as winner
from   cr_matches m
join   cr_teams   h on h.team_id  = m.home_team_id
join   cr_teams   a on a.team_id  = m.away_team_id
join   cr_venues  v on v.venue_id = m.venue_id
left   join cr_teams w on w.team_id = m.winner_id
where  m.season = 2026 and m.match_no <= 5
order  by m.match_id;

CR_TEAMS appears three times, with three aliases (h, a, w), because each match refers to three teams for three different reasons. Each alias is its own copy for the join.

The winner is a LEFT JOIN so that a tie would survive (none of these five was a tie, but the query shouldn't care).

Way 2: Oracle's old comma syntax

select m.match_id,
       to_char(m.match_date, 'YYYY-MM-DD') as match_date,
       h.team_code                         as home,
       a.team_code                         as away,
       v.venue_name,
       nvl(w.team_code, 'tie')             as winner
from   cr_matches m, cr_teams h, cr_teams a, cr_venues v, cr_teams w
where  h.team_id  = m.home_team_id
and    a.team_id  = m.away_team_id
and    v.venue_id = m.venue_id
and    w.team_id (+) = m.winner_id
and    m.season = 2026 and m.match_no <= 5
order  by m.match_id;

Tables listed with commas, join conditions in WHERE, and (+) on the side that may be missing (the "optional" side). You'll see this in a lot of older Oracle code. It works, but join conditions get mixed up with filters, and forgetting one condition silently multiplies your rows. Prefer ANSI joins in new code.

Way 3: USING

select m.match_id,
       to_char(m.match_date, 'YYYY-MM-DD') as match_date,
       h.team_code                         as home,
       a.team_code                         as away,
       venue_name,
       nvl(w.team_code, 'tie')             as winner
from   cr_matches m
join   cr_venues  using (venue_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
left   join cr_teams w on w.team_id = m.winner_id
where  m.season = 2026 and m.match_no <= 5
order  by m.match_id;

JOIN cr_venues USING (venue_id) joins on the column that has the same name in both tables. Neat when names match; after it, refer to venue_id without a table alias. It can't help with the team joins, where the names differ (home_team_id vs team_id).

19c and 26ai

The same on both.

Gotchas

• A missing join condition multiplies rows. Forget a.team_id = m.away_team_id and every match pairs with all six teams.
• An inner join drops unmatched rows silently. If a count looks low after a join, an inner join that should be an outer join is the usual suspect.
• NATURAL JOIN joins on every same-named column. Add a created_by column to both tables one day and the join changes meaning. Avoid it.