EXISTS and NOT EXISTS: semi and anti joins

Sometimes you don't want the other table's columns, only to know whether a matching row exists. "Teams that won a final" (there is such a match), "teams that never lost at home" (there is no such match). The jargon: a semi join and an anti join.

EXISTS only cares whether the subquery returns a row, so SELECT 1 is the custom: the column list is never used.

The test case

Which teams didn't lose a single match as the home team in 2026?

Expected result

Chennai and Leeds won all three of their home matches. Auckland won its only one, which also counts.

Way 1: NOT EXISTS

select t.team_name
from   cr_teams t
where  not exists (select 1
                   from   cr_matches m
                   where  m.season = 2026
                   and    m.home_team_id = t.team_id
                   and    (m.winner_id <> t.team_id or m.winner_id is null))
order  by t.team_name;

"There is no 2026 home match that this team didn't win." A tie counts as not winning here, so the condition includes winner_id is null; without it, a home tie would slip through (null <> 6 is unknown).

Way 2: NOT IN

select t.team_name
from   cr_teams t
where  t.team_id not in (select m.home_team_id
                         from   cr_matches m
                         where  m.season = 2026
                         and    lnnvl(m.winner_id = m.home_team_id))
order  by t.team_name;

The list of home teams that didn't win. home_team_id is never null, so the NOT IN null trap (page 1.2) can't bite here, and LNNVL handles the tie: it's true when winner_id = home_team_id is false or unknown.

Way 3: LEFT JOIN ... IS NULL

select t.team_name
from   cr_teams t
left   join cr_matches m
       on  m.season = 2026
       and m.home_team_id = t.team_id
       and (m.winner_id <> t.team_id or m.winner_id is null)
where  m.match_id is null
order  by t.team_name;

Join each team to its non-winning home matches, and keep the teams where the join found nothing. An old favourite. The conditions must all be in the ON; any of them in WHERE would turn it back into an inner join.

Way 4: MINUS

select t.team_name
from   cr_teams t
where  t.team_id in (select team_id from cr_teams
                     minus
                     select home_team_id from cr_matches
                     where  season = 2026 and lnnvl(winner_id = home_team_id))
order  by t.team_name;

All teams, minus the home teams that didn't win.

19c and 26ai

The same on both. Oracle usually runs all four the same way (as an anti join); pick the one that reads best to you. NOT EXISTS is the safest habit.

Gotchas

• A team with no home matches at all would also count as "unbeaten at home": there's no home match it lost. Logically right, practically surprising. Add an EXISTS for "played at home".
• NOT IN with nulls in the list returns nothing (page 1.2). NOT EXISTS doesn't care.
• EXISTS never duplicates rows. A join to the same matches would repeat each team once per match.