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
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
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'