Join type output examples
- Updated: 2020/05/07
Select a join type option based on your desired output. Use the examples below to guide your selection.
Inner join
Returns only the records that have matching values in the selected columns in both tables.
For example, if you have a table of employees and their departments, and a table of employees and their pay rates, this option will return a table of the employees that exist in both tables, and their departments and pay rates.
Employee | Department |
---|---|
John | 101 |
Jill | 102 |
Mike | 103 |
Betty | 104 |
Cindy | 105 |
Employee | Pay rate |
---|---|
John | 50 |
Betty | 50 |
Mike | 40 |
Jill | 35 |
Dan | 45 |
Employee | Department | Pay rate |
---|---|---|
John | 101 | 50 |
Jill | 102 | 35 |
Mike | 103 | 40 |
Betty | 104 | 50 |
Left outer join
Returns all records from the first table, and the matched records from the second table.
Using the example Tables 1 and 2, this option returns the following table:
Employee | Department | Pay rate |
---|---|---|
John | 101 | 50 |
Jill | 102 | 35 |
Mike | 103 | 40 |
Betty | 104 | 50 |
Cindy | 105 |
Right outer join
Returns all records from the second table, and the matched records from the first table.
This option returns the following table:
Employee | Department | Pay rate |
---|---|---|
John | 101 | 50 |
Betty | 104 | 50 |
Mike | 103 | 40 |
Jill | 102 | 35 |
Dan | 45 |
Full outer join
Returns all records when there is a match in either left or right table.
This option returns the following table:
Employee | Department | Pay rate |
---|---|---|
John | 101 | 50 |
Jill | 102 | 35 |
Mike | 103 | 40 |
Betty | 104 | 50 |
Cindy | 105 | |
Dan | 45 |