SQL Outer Join
This page covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them.
There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.
Left Outer Join
Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.
Example SQL statement
Source Tables
Left Table
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 1 | Fred | Flinstone | freddo |
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 4 | Ozzy | Ozzbourne | sabbath |
| 5 | Homer | Gain | noplacelike |
Right Table
| IndividualId | AccessLevel |
|---|---|
| 1 | Administrator |
| 2 | Contributor |
| 3 | Contributor |
| 4 | Contributor |
| 10 | Administrator |
Result
| IndividualId | FirstName | LastName | UserName | IndividualId | AccessLevel |
|---|---|---|---|---|---|
| 1 | Fred | Flinstone | freddo | 1 | Administrator |
| 2 | Homer | Simpson | homey | 2 | Contributor |
| 3 | Homer | Brown | notsofamous | 3 | Contributor |
| 4 | Ozzy | Osbourne | sabbath | 4 | Contributor |
| 5 | Homer | Gain | noplacelike | NULL | NULL |
Note the following:
- The right table has a row with an IndividualId of 10. The left join doesn't return that row, because there's no corresponding value in the left table.
- The left table has a row with an IndividualId of 5. The left join does return that row, even though there is no corresponding value in the right table.
Right Outer Join
Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.
Example SQL statement
Source Tables
Left Table
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 1 | Fred | Flinstone | freddo |
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 4 | Ozzy | Ozzbourne | sabbath |
| 5 | Homer | Gain | noplacelike |
Right Table
| IndividualId | AccessLevel |
|---|---|
| 1 | Administrator |
| 2 | Contributor |
| 3 | Contributor |
| 4 | Contributor |
| 10 | Administrator |
Result
| IndividualId | FirstName | LastName | UserName | IndividualId | AccessLevel |
|---|---|---|---|---|---|
| 1 | Fred | Flinstone | freddo | 1 | Administrator |
| 2 | Homer | Simpson | homey | 2 | Contributor |
| 3 | Homer | Brown | notsofamous | 3 | Contributor |
| 4 | Ozzy | Osbourne | sabbath | 4 | Contributor |
| NULL | NULL | NULL | NULL | 10 | Administrator |
This time we get a row for the IndividualId of 10, but not for the IndividualId of 5.
Full Outer Join
Use this when you want to all rows, even if there's no matching rows in the right table.
Example SQL statement
Source Tables
Left Table
| IndividualId | FirstName | LastName | UserName |
|---|---|---|---|
| 1 | Fred | Flinstone | freddo |
| 2 | Homer | Simpson | homey |
| 3 | Homer | Brown | notsofamous |
| 4 | Ozzy | Ozzbourne | sabbath |
| 5 | Homer | Gain | noplacelike |
Right Table
| IndividualId | AccessLevel |
|---|---|
| 1 | Administrator |
| 2 | Contributor |
| 3 | Contributor |
| 4 | Contributor |
| 10 | Administrator |
Result
| IndividualId | FirstName | LastName | UserName | IndividualId | AccessLevel |
|---|---|---|---|---|---|
| 1 | Fred | Flinstone | freddo | 1 | Administrator |
| 2 | Homer | Simpson | homey | 2 | Contributor |
| 3 | Homer | Brown | notsofamous | 3 | Contributor |
| 4 | Ozzy | Osbourne | sabbath | 4 | Contributor |
| 5 | Homer | Gain | noplacelike | NULL | NULL |
| NULL | NULL | NULL | NULL | 10 | Administrator |
This time we get a row for the IndividualId of 10 and another row for the IndividualId of 5.