Microsoft SQL Server Join 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'
