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

Outer joins are quite as common as inner joins.

But they come in quite handy for more complex queries.

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

where the data matches on both sides of the relationship.

An outer join also brings back data from two tables.

But instead of just returning the results where the data matches,

the center of this venn diagram.

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

There are three types of Outer Join.

Left, right, and full.

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

And only the records that much in the table on the right.

The right Outer Join is just like the left Outer Join.

But the tables are flipped.

We'll get all records from the right table.

And then match records from the left table.

Then we have a full outer join.

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

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

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

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

But be sure to check the teacher's notes for

more information on all other types of outer joins.

Now, let's see it in action.

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

Let's say we want to answer the question.

How many models of each make are there?

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

Pay attention to one make in particular, BMW.

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

We'll see that BMW disappears.

This is because BMW is only in our Make table.

But we have no BMW models yet.

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

Let's change that to aid LEFT OUTER JOIN.

This will get all rows from the Make table,

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

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

with null in the value for the model.

Note that when doing a left outer join,

the order in which you specify the tables is important.

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

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

Finally, let's count our models,

and alias it as number of models.

Not forgetting to group by their common attribute.

Which is then MakeName.

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

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

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

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

And this report would be incorrect.

Knowing the difference between an inner and

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

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

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

Then the from keyword with the first table on the left.

Then, the keywords left outer join.

And the second table name.

With the on keyword and equality criteria.

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

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

Including inner joins.