Hello and welcome to working with right outer join.
In this video, we will learn about creating a result set by joining rows from two tables.
At the end of this lesson,
you will be able to explain the syntax of
the right outer join operator and interpret the results set.
A join combines the rows from two or more tables based on
a relationship between certain columns in these tables.
There are two types of table joints;
inner join and outer join.
An outer join is a specialized form of join and there are three types of outer join;
left outer join, right outer join,
and full outer join.
This video explains right outer join or simply called right join.
Looking at the diagram,
the terms left and right refer to the table on
the left hand side and the right hand side of the diagram.
In this diagram, table 1 is the left table.
A right join matches the results from
two tables and displays all the rows from the right table
and combines the information with rows from
the left table that matched the criteria specified in the query.
In this diagram, the result set of a right join is all rows from both tables matching
the criteria specified in the query plus all non-matching rows from the right table.
Based on our simplified library database model,
if we want to check the status of all books out on loan,
this information is split between two tables.
The loan table which includes
the borrowers ID and loan date and the borrower table which includes the borrowers ID,
first name, and last name.
So, we need to identify the relationship between the two tables.
We do this by identifying the column at each table to link the tables.
In this example, we do this by matching the borrower ID.
Notice that the column borrower ID exists in
both the borrower table as the primary key and the loan table as the foreign key.
A primary key uniquely identifies each row in a table.
A foreign key is a set of columns referring to a primary key of another table.
In an outer join,
the first table specified in the from clause of the SQL statement is referred to
as the left table and the remaining table is referred to as the right table.
This is the syntax that the select statement for our right join.
In this example, the borrower table is the first table
specified in the from clause of the select statement.
So, the borrower table is the left table and the loan table is the right table.
Notice that in the from clause,
we identify the borrower table as B and the loan table as
L. This use of the letter B and the letter L is referred to as an alias.
In this join, each column name is prefixed with either the letter B
or L. This helps indicate which table each column is associated with.
Using an alias is much easier than prefixing each column name with the whole table name.
In the from clause, the loan table is listed on the right side of the join operator.
Therefore, we will select all rows from the loan table and combine this with
the contents of the borrower table based on the criteria specified in the query.
In this example, the criteria is the borrower ID column.
For a right join, we will select the following columns from the loan table,
borrower ID and loan date,
and we will also select the following columns from the borrower table;
borrower ID, last name, and country,
where the borrower ID in the loan table matches the borrower ID in the borrower table,
and here's the Result Set.
The Result Set shows each borrower ID
from the loan table and the loan date for that borrower,
where the borrower ID in the loan table also exists in the borrower table.
There are five rows in the Result Set.
Now, you can explain the syntax of
the right outer join operator and interpret the result set.
Thanks for watching this video.