Outer joins: LEFT, RIGHT and FULL

An outer join keeps rows that have no partner, and fills the partner's columns with null:

Part 1: everyone, including those with nothing

The test case

Every Auckland player and their runs in 2026, including anyone who scored none. Highest first.

Isaac Ruane faced one ball all season and didn't score. With an inner join he'd still appear (he did bat); a player who never batted at all would only appear with an outer join.

Way 1: LEFT JOIN to a summary

with runs as (
  select batter_id, sum(runs_batter) as runs
  from   cr_balls_v
  where  season = 2026
  group  by batter_id
)
select p.player_name, nvl(r.runs, 0) as runs_2026
from   cr_players p
left   join runs r on r.batter_id = p.player_id
where  p.team_id = 5
order  by runs_2026 desc, p.player_name;

Summarise first, then join. Players with no batting row get null from the LEFT JOIN, and NVL turns it into 0.

Way 2: a scalar subquery

select p.player_name,
       nvl((select sum(b.runs_batter) from cr_balls_v b
            where  b.season = 2026 and b.batter_id = p.player_id), 0) as runs_2026
from   cr_players p
where  p.team_id = 5
order  by runs_2026 desc, p.player_name;

A subquery in the SELECT list returns one value per row: here, that player's total. No join, so no way to lose a row. Fine for a few rows; for thousands, the join is usually faster.

Way 3: (+), the old way

select p.player_name, nvl(r.runs, 0) as runs_2026
from   cr_players p,
       (select batter_id, sum(runs_batter) as runs
        from   cr_balls_v where season = 2026 group by batter_id) r
where  r.batter_id (+) = p.player_id
and    p.team_id = 5
order  by runs_2026 desc, p.player_name;

(+) goes on the column of the table that may have no matching row. Easy to put on the wrong side, and impossible to use for a FULL join.

Part 2: FULL JOIN, rows from either side

The test case

Batters who batted in only one of the two seasons, with their runs in each (null where they didn't bat).

Way 1: FULL JOIN

with y25 as (select batter_id, sum(runs_batter) as runs from cr_balls_v where season = 2025 group by batter_id),
     y26 as (select batter_id, sum(runs_batter) as runs from cr_balls_v where season = 2026 group by batter_id)
select p.player_name, a.runs as runs_2025, b.runs as runs_2026
from   y25 a
full   join y26 b on b.batter_id = a.batter_id
join   cr_players p on p.player_id = coalesce(a.batter_id, b.batter_id)
where  a.batter_id is null or b.batter_id is null
order  by p.player_name;

A FULL JOIN keeps every 2025 batter and every 2026 batter, matched where possible. The WHERE keeps the unmatched ones. COALESCE(a.batter_id, b.batter_id) takes whichever side exists, for the join to the player's name.

Way 2: two LEFT JOINs and UNION ALL

with y25 as (select batter_id, sum(runs_batter) as runs from cr_balls_v where season = 2025 group by batter_id),
     y26 as (select batter_id, sum(runs_batter) as runs from cr_balls_v where season = 2026 group by batter_id)
select p.player_name, runs_2025, runs_2026
from   (select a.batter_id, a.runs as runs_2025, cast(null as number) as runs_2026
        from   y25 a left join y26 b on b.batter_id = a.batter_id where b.batter_id is null
        union all
        select b.batter_id, null, b.runs
        from   y26 b left join y25 a on a.batter_id = b.batter_id where a.batter_id is null) x
join   cr_players p on p.player_id = x.batter_id
order  by p.player_name;

What FULL JOIN does for you: the left-only rows plus the right-only rows. Longer, but it works in databases without FULL JOIN (and in Oracle's (+) style, which has no full join).

19c and 26ai

The same on both.

Gotchas

• A WHERE on the outer table undoes the outer join. left join runs r ... where r.runs > 0 throws away the null rows you joined to keep. Put such conditions in the ON (page 0.3).
• NVL after the join, not before: the null comes from the missing row, not from the data.
• COUNT(*) counts the null-padded rows too. Count a column from the outer side to get 0.