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 result 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 joins: 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 one 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 match 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 borrower's ID and loan date and the borrower table, which includes the
borrower's ID first name and last name.
In an outer join,
the first table
specified in the from clause of the sequel statement is referred to as the Left table and the remaining table is referred to as the
Right table. This is the syntax at the Select statement for a right joint. 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 joint 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. Rhe 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.
You