Hello, and welcome to working with Full Join.
In this video, we will learn about creating a result
set by joining all rows from two tables.
At the end of this lesson,
you'll be able to explain the syntax of
the Full 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.
There are three types of outer join: left outer join,
right outer join, and full outer join.
This video explains full outer join or simply called full join.
The full join keyword returns all rows from both tables.
That is all rows from the left table and all rows from the right table.
So, the full join could return a very large result set.
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.
Based on our simplified library database model,
we want to select all rows from the borrower table,
and all rows from the loan table.
We do this by identifying the column in each table to link the tables.
In this example, we do this by matching the borrower ID.
The column borrower ID exists in both tables.
This is the syntax of the select statement for a full join.
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 this example, the criteria is the borrower ID column.
For a full join we select all rows from
the borrower table and all rows from the loan table.
And here is the result set.
The result set shows all rows from the borrower table and all rows from the loan table.
There are eight rows in the result set.
All eight records from the borrower table are
listed with the corresponding data from the loan table.
However, in the last three rows the columns borrower ID and
loan date from the loan table do not have a corresponding borrower ID.
So a null value is returned.
A null value is returned because borrowers Peters,
Li, and Wong have never taken a book out on loan.
Therefore, there is no corresponding borrower ID value in the loan table.
Now you can explain the syntax of the full join operator and interpret the result set,
including a null value.
Thanks for watching this video.
(Music)