Outer joins

1. Outer joins

Welcome back! In this video, we're going to learn about outer joins.

2. Three types of outer joins

There are three types of outer joins: left, right, and full outer joins.

3. Comparing inner and outer

Inner joins only return rows that match between the two tables. Left outer joins returns the matched rows and all of the left table's rows. Right outer joins does the same as the left outer join but with the right table. As the name suggest, the full outer join returns all matched and unmatched rows. This can be difficult to grasp so let's look at an example.

4. Track and invoice tables

Every invoice contains at least one track. However, not all tracks are in an invoice.

5. Left outer join

We can left outer join these two tables to get tracks and the invoiceIDs that they appear in. A snippet of the query output is shown here. The green represents columns from the track table and the purple represents columns from the invoiceline table. The first two rows share the same track, Overdose. That track appears twice because it's in two invoices with ids 214 and 3. We can see the effects of a left outer join in the two last rows. These two tracks don't appear in any invoices, however because we return all rows from the left table, their invoiceid and quantity are labeled as nulls.

6. Customer and employee tables

Here's another example. Every customer has a support representative that refers to an employee. However, not every employee is a support rep.

7. Right outer join

We can right outer join these tables to get employees and all the customers they support with this code. A snippet of the query output is shown here. The green represents columns from the customer table and the purple represents columns from the employee table. Only sales support agents have customers, so non-sales support agent employees like Nancy and Laura in the last two rows have null values for the first two columns.

8. Customer and employee tables

In this example, let's say that not every customer has a support representative.

9. Full outer join

We can use the full outer join to join these two tables with this output. Now all rows from each table are included, regardless if there is a match or not.

10. Let's practice!

It's now your turn to try outer joins!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.