Preview

Start a free Courses trial
to watch this video

Outer Joins

Name: Outer Joins
Uploaded: 2016-09-12T17:08:53Z
Duration: 254 s
Description: Outer Joins are less common than Inner Joins, but still highly useful.

4:14 with Andrew Chalkley and Kevin Nahm

Outer Joins are less common than Inner Joins, but still highly useful.

Teacher's Notes
Video Transcript
Downloads

Three Types of Outer Joins:

Left
Right
Full

In this video, we introduced three types of Outer Joins, but only showed examples of the Left Outer Join. This is because a few database engines, like SQLite, only support the Left Outer Join. Most other database systems support all three types.

Right Outer Joins are essentially the same thing as Left Outer Joins, but just specified in the opposite direction.

Full Outer Joins are functionally different in that you are guaranteed to get all rows from both tables. Full Outer Joins aren’t used as much in application or report coding as Inner and Left/Right Outer Joins, but can come in very handy when doing things like comparing data between two tables that are structurally similar.

Resources:
W3Schools: Left Joins
W3Schools: Right Joins
W3Schools: Full Joins
Wikipedia: Outer Joins

Cheat Sheet

See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.

In this video, we're going to take a look at the outer join. 0:00

Outer joins are quite as common as inner joins. 0:04

But they come in quite handy for more complex queries. 0:07

>> Remember that an inner join will return rows from two tables 0:11

where the data matches on both sides of the relationship. 0:14

An outer join also brings back data from two tables. 0:18

But instead of just returning the results where the data matches, 0:22

the center of this venn diagram. 0:26

Outer joins return records that don't necessarily have to match, too. 0:28

There are three types of Outer Join. 0:32

Left, right, and full. 0:35

The Left Outer Join returns all data from the table on the left. 0:37

And only the records that much in the table on the right. 0:41

The right Outer Join is just like the left Outer Join. 0:45

But the tables are flipped. 0:48

We'll get all records from the right table. 0:50

And then match records from the left table. 0:53

Then we have a full outer join. 0:57

This will match up all records that exist in both tables. 0:59

And then return the remaining unmatched data from both left and right tables. 1:03

Most, but not all, databases support all three outer join types. 1:09

We're going to focus on the left outer join. 1:14

But be sure to check the teacher's notes for 1:16

more information on all other types of outer joins. 1:18

Now, let's see it in action. 1:22

Let's look at our make and model tables again. 1:25

Let's say we want to answer the question. 1:28

How many models of each make are there? 1:31

Let's first take a look at our Make Table. 1:34

Pay attention to one make in particular, BMW. 1:37

If we inner join the make and model table, like we did in a last video. 1:41

We'll see that BMW disappears. 1:46

This is because BMW is only in our Make table. 1:51

But we have no BMW models yet. 1:55

BMW doesn't show when we use the INNER JOIN. 1:59

Let's change that to aid LEFT OUTER JOIN. 2:03

This will get all rows from the Make table, 2:06

regardless of whether it has a match in the model table. 2:09

Now we see one row with a make of BMW in the results, 2:13

with null in the value for the model. 2:17

Note that when doing a left outer join, 2:20

the order in which you specify the tables is important. 2:22

The database engine will look for rows from the left table. 2:26

Which is the one you specify first, immediately after the keyword from. 2:30

Finally, let's count our models, 2:36

and alias it as number of models. 2:41

Not forgetting to group by their common attribute. 2:51

Which is then MakeName. 2:58

As you can see, B.M.W. has a zero count. 3:01

That's because the count function does not count no values. 3:08

The left outer join includes B.M.W. since it's in the table on the left make. 3:13

However, if we did an inner join, B.M.W. would vanish. 3:21

And this report would be incorrect. 3:26

Knowing the difference between an inner and 3:29

outer join can help you produce better and more accurate reports. 3:33

To write a left outer join statement, first, we need to select 3:40

the columns from both the tables you want to return in the result set. 3:45

Then the from keyword with the first table on the left. 3:50

Then, the keywords left outer join. 3:55

And the second table name. 3:57

With the on keyword and equality criteria. 3:59

Then, any additional clauses like a work clause or a group by clause. 4:02

Like an inner join query, left outer joins can be combined with many of the joins. 4:07

Including inner joins. 4:12

You need to sign up for Treehouse in order to download course files.