Anti Joins (Left, Right, Full)

Some of the most valuable questions are about what's missing: "Which players point to a team that doesn't exist?" or "Which franchises have no players yet?" SQL Server has no ANTI JOIN keyword — you build one from an outer join plus a WHERE ... IS NULL filter.

Definition

An ANTI JOIN returns only the rows of one table that have no matching row in the other table. In SQL Server it is written as an outer join followed by WHERE <other table's key> IS NULL.

Why IS NULL works: an outer join fills the other side with NULLs exactly when no match was found. Filtering for those NULLs keeps only the unmatched rows.

The data for these examples

In the seed data every player's team exists, so a left anti join would return nothing. The playground setup therefore adds two "orphan" players:

INSERT INTO players (id, name, team, country, runs) VALUES
  (7, 'Shubman Gill', 'GT2', 'India', 600),   -- typo: should be GT
  (8, 'Ishan Kishan', NULL,  'India', 450);   -- team not assigned

On the other side, KKR and GT exist in teams but have no players.

LEFT ANTI JOIN — players without a team

SELECT p.name, p.team, t.team AS t_team, t.team_name
FROM players AS p
LEFT JOIN teams AS t
    ON p.team = t.team
WHERE t.team IS NULL;

How it works: the LEFT JOIN keeps all 8 players; only Gill and Kishan got NULL team columns. WHERE t.team IS NULL keeps just those two. Great for data-quality checks — "GT2" is clearly a typo to fix.

RIGHT ANTI JOIN — teams without players

SELECT p.name, p.team, t.team AS t_team, t.team_name
FROM players AS p
RIGHT JOIN teams AS t
    ON p.team = t.team
WHERE p.team IS NULL;

Alternative — swap the tables and use LEFT JOIN (same result):

SELECT t.team, t.team_name, t.city
FROM teams AS t
LEFT JOIN players AS p
    ON p.team = t.team
WHERE p.id IS NULL;

FULL ANTI JOIN — only the unmatched rows from both sides

SELECT p.name, p.team, t.team AS t_team, t.team_name
FROM players AS p
FULL JOIN teams AS t
    ON p.team = t.team
WHERE p.id IS NULL
   OR t.team IS NULL;

These are the leftovers — every row that did not find a partner on either side.

Which column should you test for NULL?

Test a column of the other table that can never be NULL in a real row — ideally its primary key (t.team, p.id). Then NULL can only mean "no match".

Watch what happens if you test the wrong column in a left anti join:

-- ❌ tests the LEFT table's own column
SELECT p.name, p.team, t.team AS t_team
FROM players AS p
LEFT JOIN teams AS t
    ON p.team = t.team
WHERE p.team IS NULL;

Shubman Gill (team 'GT2') is missed, because his own team is not NULL. Always check the other table's key.

Other ways to write an anti join

Common mistakes

• ❌ Putting IS NULL in the ON clause instead of WHERE. ✅ The NULLs only exist after the join, so filter in WHERE.
• ❌ Testing a nullable column (p.team) instead of the other table's key. ✅ Test t.team / p.id.
• ❌ Using an INNER JOIN and expecting unmatched rows. ✅ Anti joins always start from an outer join.

Interview tip

Q: Find all teams that have no players. A: SELECT t.* FROM teams t LEFT JOIN players p ON p.team = t.team WHERE p.id IS NULL; — or WHERE NOT EXISTS (SELECT 1 FROM players p WHERE p.team = t.team). Mention that NOT IN is risky when the subquery column can contain NULLs.