1
00:00:00,000 --> 00:00:03,860
Hello, and welcome to working with Full Join.

2
00:00:03,860 --> 00:00:06,490
In this video, we will learn about creating a result

3
00:00:06,490 --> 00:00:09,530
set by joining all rows from two tables.

4
00:00:09,530 --> 00:00:11,150
At the end of this lesson,

5
00:00:11,150 --> 00:00:13,210
you'll be able to explain the syntax of

6
00:00:13,210 --> 00:00:16,590
the Full Join Operator and interpret the result set.

7
00:00:16,590 --> 00:00:19,760
A join combines the rows from two or more tables based

8
00:00:19,760 --> 00:00:23,030
on a relationship between certain columns in these tables.

9
00:00:23,030 --> 00:00:27,660
There are two types of table joins: inner join and outer join.

10
00:00:27,660 --> 00:00:31,340
There are three types of outer join: left outer join,

11
00:00:31,340 --> 00:00:34,200
right outer join, and full outer join.

12
00:00:34,200 --> 00:00:38,680
This video explains full outer join or simply called full join.

13
00:00:38,680 --> 00:00:42,750
The full join keyword returns all rows from both tables.

14
00:00:42,750 --> 00:00:46,530
That is all rows from the left table and all rows from the right table.

15
00:00:46,530 --> 00:00:50,340
So, the full join could return a very large result set.

16
00:00:50,340 --> 00:00:53,180
The terms left and right refer to the table on

17
00:00:53,180 --> 00:00:56,480
the left-hand side and the right-hand side of the diagram.

18
00:00:56,480 --> 00:00:59,750
In this diagram table one is the left table.

19
00:00:59,750 --> 00:01:02,960
Based on our simplified library database model,

20
00:01:02,960 --> 00:01:05,940
we want to select all rows from the borrower table,

21
00:01:05,940 --> 00:01:08,020
and all rows from the loan table.

22
00:01:08,020 --> 00:01:12,140
We do this by identifying the column in each table to link the tables.

23
00:01:12,140 --> 00:01:16,110
In this example, we do this by matching the borrower ID.

24
00:01:16,110 --> 00:01:20,300
The column borrower ID exists in both tables.

25
00:01:20,300 --> 00:01:24,770
This is the syntax of the select statement for a full join.

26
00:01:24,770 --> 00:01:26,400
In the from clause,

27
00:01:26,400 --> 00:01:30,480
we identify the borrower table as B and the loan table as

28
00:01:30,480 --> 00:01:35,920
L. This use of the letter B and the letter L is referred to as an alias.

29
00:01:35,920 --> 00:01:40,010
In this join, each column name is prefixed with either the letter B

30
00:01:40,010 --> 00:01:44,560
or L. This helps indicate which table each column is associated with.

31
00:01:44,560 --> 00:01:50,400
Using an alias is much easier than prefixing each column name with the whole table name.

32
00:01:50,400 --> 00:01:54,980
In this example, the criteria is the borrower ID column.

33
00:01:54,980 --> 00:01:57,990
For a full join we select all rows from

34
00:01:57,990 --> 00:02:01,560
the borrower table and all rows from the loan table.

35
00:02:01,560 --> 00:02:03,760
And here is the result set.

36
00:02:03,760 --> 00:02:09,100
The result set shows all rows from the borrower table and all rows from the loan table.

37
00:02:09,100 --> 00:02:11,670
There are eight rows in the result set.

38
00:02:11,670 --> 00:02:14,150
All eight records from the borrower table are

39
00:02:14,150 --> 00:02:17,120
listed with the corresponding data from the loan table.

40
00:02:17,120 --> 00:02:21,670
However, in the last three rows the columns borrower ID and

41
00:02:21,670 --> 00:02:26,680
loan date from the loan table do not have a corresponding borrower ID.

42
00:02:26,680 --> 00:02:29,030
So a null value is returned.

43
00:02:29,030 --> 00:02:32,270
A null value is returned because borrowers Peters,

44
00:02:32,270 --> 00:02:36,110
Li, and Wong have never taken a book out on loan.

45
00:02:36,110 --> 00:02:41,780
Therefore, there is no corresponding borrower ID value in the loan table.

46
00:02:41,780 --> 00:02:46,610
Now you can explain the syntax of the full join operator and interpret the result set,

47
00:02:46,610 --> 00:02:48,540
including a null value.

48
00:02:48,540 --> 00:02:51,060
Thanks for watching this video.

49
00:02:51,060 --> 00:02:56,000
(Music)