NULL: the value that isn't there

Null means "no value": a tie has no winner, a ball with no wicket has no wicket_kind. Null isn't zero and isn't an empty string. It's unknown, and almost everything you do with an unknown gives an unknown back:

Helpers that turn null into something: NVL(x, y) (y if x is null), COALESCE(a, b, c) (the first non-null), NVL2(x, if_not_null, if_null), and the other way round, NULLIF(a, b) (null if a = b).

The test case: the NOT IN trap

Which batters batted in 2026 and were never dismissed all season?

The natural answer is "batters NOT IN the list of players who got out". Here's the catch: the list of player_out_id values includes null, on every ball where nobody was out.

Expected result

Mostly tail-enders who barely faced a ball. The opposite of a duck: an unbeaten season, mostly from not being asked to bat.

Way 1: NOT EXISTS

select p.player_name, t.team_code
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  exists     (select 1 from cr_balls_v b where b.season = 2026 and b.batter_id     = p.player_id)
and    not exists (select 1 from cr_balls_v o where o.season = 2026 and o.player_out_id = p.player_id)
order  by t.team_code, p.player_name;

NOT EXISTS asks "is there any row where this player got out?". Rows with a null player_out_id simply never match, so they can't spoil the answer. This is the safe default.

Way 2: NOT IN, with the nulls filtered out

select p.player_name, t.team_code
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  p.player_id in     (select batter_id     from cr_balls_v where season = 2026)
and    p.player_id not in (select player_out_id from cr_balls_v where season = 2026
                           and    player_out_id is not null)
order  by t.team_code, p.player_name;

Remove the and player_out_id is not null and this returns no rows at all. x NOT IN (1, 2, null) means x <> 1 AND x <> 2 AND x <> null, and the last part is unknown, so the whole thing is never true. One null in the list empties your result, silently.

Way 3: MINUS

select p.player_name, t.team_code
from   (select batter_id     as player_id from cr_balls_v where season = 2026
        minus
        select player_out_id             from cr_balls_v where season = 2026) x
join   cr_players p on p.player_id = x.player_id
join   cr_teams   t on t.team_id   = p.team_id
order  by t.team_code, p.player_name;

Set subtraction: everyone who batted, minus everyone who got out. MINUS treats nulls as equal to each other and doesn't fall into the trap.

19c and 26ai

The same on both. 26ai's BOOLEAN makes the three values visible: TRUE, FALSE and null (page 12.1).

Gotchas

• NOT IN + a null in the list = no rows. Prefer NOT EXISTS, or filter nulls out of the list.
• count(column) skips nulls; count(*) doesn't. Page 1.6.
• Oracle treats '' as null. where nickname = '' never matches; use is null.