Left and Right Joins

With different types of joins, we get different sizes of result sets. Inner joins are one of the most common joins you will see in SQL, however, with left and right outer joins, you can expand the result set to get more information if needed.

Left Outer Join

Between left and right outer joins, the left outer join is more common. Joining two tables with a left outer join gives us a result set which includes all values in the left table and any matching records from the right table with null values for unmatched rows.

If we use a left outer join to combine johnson_wedding and johnson_vow_renewal in a query, the result set includes all of the guests invited to the wedding and any guests who were also invited to the vow renewal.

1
2
3

SELECT last_name, first_name
FROM johnson_wedding
LEFT JOIN johnson_vow_renewal ON johnson_wedding.guest_id = johnson_vow_renewal.guest_id;

The Venn diagram above shows the result set highlighted in blue.

Right Outer Join

Joining two tables with a right outer join gives us a result set that includes all values in the right table and any matching records from the left table with null values for unmatched rows.

If we use a right inner join to combine johnson_wedding and johnson_vow_renewal in a query, the result set includes all of the guests that were invited to the vow renewal and any guests who were also invited to the wedding.

1
2
3

SELECT last_name, first_name
FROM johnson_wedding
RIGHT JOIN johnson_vow_renewal ON johnson_wedding.guest_id = johnson_vow_renewal.guest_id;

The Venn diagram above shows the result set highlighted in blue.

Check Your Understanding