Microsoft SQL Server Accidentally turning an outer join into an inner join


Example

Outer joins return all the rows from one or both tables, plus matching rows.

Table People
PersonID FirstName
       1 Alice
       2 Bob
       3 Eve

Table Scores
PersonID Subject Score
       1 Math    100
       2 Math     54
       2 Science  98

Left joining the tables:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID

Returns:

PersonID FirstName PersonID Subject Score
       1 Alice            1 Math    100
       2 Bob              2 Math     54
       2 Bob              2 Science  98
       3 Eve           NULL NULL   NULL

If you wanted to return all the people, with any applicable math scores, a common mistake is to write:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'

This would remove Eve from your results, in addition to removing Bob's science score, as Subject is NULL for her.

The correct syntax to remove non-Math records while retaining all individuals in the People table would be:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'