1
00:00:00,000 --> 00:00:04,520
Hello and welcome to working with right outer join. In

2
00:00:04,520 --> 00:00:11,150
This video we will learn about creating a result set by joining rows from two tables. At the end of this lesson,

3
00:00:11,150 --> 00:00:17,240
you will be able to explain the syntax of the right outer join operator and interpret the result set. A

4
00:00:17,240 --> 00:00:24,050
Join combines the rows from two or more tables based on a relationship between certain columns in these tables.

5
00:00:24,050 --> 00:00:32,689
There are two types of table joins: inner join and outer join. An outer join is a specialized form of join and

6
00:00:32,689 --> 00:00:39,440
there are three types of outer join: left outer join, right outer join and full outer join.

7
00:00:39,440 --> 00:00:44,480
This video explains right outer join or simply called right join.

8
00:00:44,480 --> 00:00:45,680
Looking at the diagram,

9
00:00:45,680 --> 00:00:52,730
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.

10
00:00:52,730 --> 00:01:00,739
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

11
00:01:00,739 --> 00:01:07,130
combines the information with rows from the left table, that match the criteria specified in the query. In

12
00:01:07,130 --> 00:01:14,960
this diagram, the result set of a right join is all rows from both tables matching the criteria specified in the query

13
00:01:14,960 --> 00:01:18,770
plus all non matching rows from the right table.

14
00:01:18,770 --> 00:01:24,619
Based on our simplified library database model, if we want to check the status of all books out on loan.

15
00:01:24,619 --> 00:01:25,280
This

16
00:01:25,280 --> 00:01:33,100
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

17
00:01:33,100 --> 00:01:37,840
borrower's ID first name and last name.

18
00:01:37,840 --> 00:01:39,530
In an outer join,

19
00:01:39,530 --> 00:01:40,759
the first table

20
00:01:40,759 --> 00:01:48,039
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

21
00:01:48,039 --> 00:01:53,330
Right table. This is the syntax at the Select statement for a right joint. In

22
00:01:53,330 --> 00:01:59,630
this example, the borrower table is the first table specified in the from clause of the Select statement.

23
00:01:59,630 --> 00:02:05,390
So the borrower table is the left table and the loan table is the right table.

24
00:02:05,390 --> 00:02:12,200
Notice that in the from clause, we identify the borrower table as B and the loan table as L.

25
00:02:12,200 --> 00:02:17,360
This use of the letter B and the letter L is referred to as an alias. In

26
00:02:17,360 --> 00:02:22,610
this join, each column name is prefixed with either the letter B or L.

27
00:02:22,610 --> 00:02:26,540
This helps indicate, which table each column is associated with.

28
00:02:26,540 --> 00:02:31,849
Using an alias is much easier than prefixing each column name with the whole table name. In

29
00:02:31,849 --> 00:02:36,890
the from clause, the loan table is listed on the right side of the joint operator.

30
00:02:36,890 --> 00:02:44,000
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

31
00:02:44,000 --> 00:02:51,010
in the query. In this example, the criteria is the borrower ID column for a right join.

32
00:02:51,010 --> 00:02:54,170
We will select the following columns from the loan table,

33
00:02:54,170 --> 00:03:00,590
Borrower ID and Loan date and we will also select the following columns from the borrower table,

34
00:03:00,590 --> 00:03:03,410
borrower ID, last name and country

35
00:03:03,410 --> 00:03:10,100
where the borrower ID in the loan table matches the borrower ID in the borrower table and

36
00:03:10,100 --> 00:03:17,959
here's the result set. Rhe result set shows each borrower ID from the loan table and the loan date for that borrower

37
00:03:17,959 --> 00:03:26,299
where the borrower ID in the loan table also exists in the borrower table. There are five rows in the result set

38
00:03:26,299 --> 00:03:32,660
Now you can explain the syntax of the right outer join operator and interpret the result set.

39
00:03:32,660 --> 00:03:35,810
Thanks for watching this video.

40
00:03:35,810 --> 00:03:37,810
You