SQL Anti-joins

Rows with no match

Both find rows in TableA with no TableB counterpart:

1
2
3
4
5
6
7
8

SELECT a.*
FROM TableA a
LEFT JOIN TableB b ON b.AId = a.Id
WHERE b.AId IS NULL;

SELECT a.*
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.AId = a.Id);

NOT EXISTS is the better default:

Correctness with NULLs. LEFT JOIN breaks when the join condition is not exactly equivalent, with nullable columns, or when a joined-table filter lands in WHERE instead of ON — silently an inner join. Keep every joined-table filter inside ON:

1
2
3
4

LEFT JOIN TableB b
  ON b.AId = a.Id
 AND b.IsActive = 1
WHERE b.AId IS NULL;

Optimizer intent. SQL Server recognizes NOT EXISTS as an anti-semi join, stopping at the first match and using indexes better; modern versions optimize both, but the subquery states intent more clearly and large-table plans follow.

NOT IN is to be avoided unless the subquery provably cannot return NULLs:

1

WHERE a.Id NOT IN (SELECT b.AId FROM TableB b)

One NULL there and the query returns no rows at all — looks like data loss, not a bug.