1
00:00:00,000 --> 00:00:04,040
Hello. And welcome to working with Left Outer Join.

2
00:00:04,040 --> 00:00:09,585
In this video, we will learn about creating a result set by joining rows from two tables.

3
00:00:09,585 --> 00:00:11,145
At the end of this lesson,

4
00:00:11,145 --> 00:00:13,300
you will be able to explain the syntax of

5
00:00:13,300 --> 00:00:16,945
the Left Outer Join Operator and interpret the result set.

6
00:00:16,945 --> 00:00:20,410
A join combines the rows from two or more tables based on

7
00:00:20,410 --> 00:00:23,705
a relationship between certain columns in these tables.

8
00:00:23,705 --> 00:00:26,010
There are two types of table joins,

9
00:00:26,010 --> 00:00:28,370
Inner Join and Outer Join.

10
00:00:28,370 --> 00:00:31,415
And Outer Join is a specialized form of Join.

11
00:00:31,415 --> 00:00:33,900
And there are three types of Outer Join,

12
00:00:33,900 --> 00:00:36,755
Left Outer Join, Right Outer Join,

13
00:00:36,755 --> 00:00:38,555
and Full Outer Join.

14
00:00:38,555 --> 00:00:43,595
This video explains Left Outer Join or simply called Left Join.

15
00:00:43,595 --> 00:00:45,525
Looking at the diagram,

16
00:00:45,525 --> 00:00:47,920
the terms left and right refer to the table on

17
00:00:47,920 --> 00:00:51,130
the left hand side and the right hand side of the diagram.

18
00:00:51,130 --> 00:00:54,670
In this diagram table 1 is the left table.

19
00:00:54,670 --> 00:00:56,950
A left join matches the results from

20
00:00:56,950 --> 00:01:00,230
two tables and displays all the rows from the left table,

21
00:01:00,230 --> 00:01:03,275
and combines the information with rows from the right table

22
00:01:03,275 --> 00:01:06,750
that match the criteria specified in the query.

23
00:01:06,750 --> 00:01:11,620
In this diagram, a Left Join matches all the rows from table 1

24
00:01:11,620 --> 00:01:14,405
and combines the information with rows from table

25
00:01:14,405 --> 00:01:18,450
2 that match the criteria specified in the query.

26
00:01:18,450 --> 00:01:21,930
Based on our simplified library database model,

27
00:01:21,930 --> 00:01:26,660
if we want to check the status of all people to see what books they have borrowed,

28
00:01:26,660 --> 00:01:29,560
this information is split between two tables:

29
00:01:29,560 --> 00:01:32,595
the Borrower table and the Loan table.

30
00:01:32,595 --> 00:01:36,720
So we need to identify the relationship between the two tables.

31
00:01:36,720 --> 00:01:41,610
In this example, we do this by matching the Borrower ID.

32
00:01:41,610 --> 00:01:47,520
Notice that the column Borrower ID exists in the borrower table as the primary key,

33
00:01:47,520 --> 00:01:50,115
and the loan table as the foreign key.

34
00:01:50,115 --> 00:01:53,700
A primary key uniquely identifies each row in a table.

35
00:01:53,700 --> 00:01:58,845
A foreign key is a set of columns referring to a primary key of another table.

36
00:01:58,845 --> 00:02:00,830
In an Outer Join,

37
00:02:00,830 --> 00:02:03,410
the first table specified in the FROM clause with

38
00:02:03,410 --> 00:02:06,310
the sequel statement is referred to as the left table,

39
00:02:06,310 --> 00:02:10,065
and the remaining table is referred to as the right table.

40
00:02:10,065 --> 00:02:13,915
This is the syntax of the select statement for a Left Join.

41
00:02:13,915 --> 00:02:16,610
In this example, the borrower table is

42
00:02:16,610 --> 00:02:20,485
the first table specified in the FROM clause of the select statement.

43
00:02:20,485 --> 00:02:23,245
So the borrower table is the left table,

44
00:02:23,245 --> 00:02:25,975
and the loan table is the right table.

45
00:02:25,975 --> 00:02:28,330
Notice that in the FROM clause,

46
00:02:28,330 --> 00:02:31,015
we identify the borrower table as B,

47
00:02:31,015 --> 00:02:34,540
and the loan table as L. This use of the letter

48
00:02:34,540 --> 00:02:38,465
B and the letter L is referred to as an alias.

49
00:02:38,465 --> 00:02:42,520
In this Join, each column name is prefixed with either the letter

50
00:02:42,520 --> 00:02:47,860
B or L. This helps indicate which table each column is associated with.

51
00:02:47,860 --> 00:02:53,640
Using an alias is much easier than prefixing each column name with the whole table name.

52
00:02:53,640 --> 00:02:55,415
In the FROM clause,

53
00:02:55,415 --> 00:02:58,995
borrower is listed on the left hand side of the Join operator.

54
00:02:58,995 --> 00:03:02,645
Therefore, we will select all rows from the borrower table,

55
00:03:02,645 --> 00:03:04,850
and combine this with the contents of

56
00:03:04,850 --> 00:03:09,250
the loan table based on the criteria specified in the query.

57
00:03:09,250 --> 00:03:14,430
In this example, the criteria is the Borrower ID column.

58
00:03:14,430 --> 00:03:15,975
For a Left Join,

59
00:03:15,975 --> 00:03:21,050
we will select the following columns from the borrower table: Borrower ID,

60
00:03:21,050 --> 00:03:23,185
last name, and country.

61
00:03:23,185 --> 00:03:26,180
And we will also select the following columns from

62
00:03:26,180 --> 00:03:30,025
the loan table: Borrower ID and loan date.

63
00:03:30,025 --> 00:03:33,150
The Left Join selects each borrower ID in

64
00:03:33,150 --> 00:03:37,885
the borrower table and displays the loan date from the loan table.

65
00:03:37,885 --> 00:03:40,210
And here is the result set.

66
00:03:40,210 --> 00:03:44,610
The result set shows each Borrower ID from the borrower table,

67
00:03:44,610 --> 00:03:47,130
and the loan date for that borrower.

68
00:03:47,130 --> 00:03:52,370
Notice there is a loan date for the first five rows: D1-D5.

69
00:03:52,370 --> 00:03:54,720
However, for the last three rows,

70
00:03:54,720 --> 00:03:58,590
the borrow ID and loan date show null values.

71
00:03:58,590 --> 00:04:01,460
Let's take a closer look at this.

72
00:04:01,460 --> 00:04:04,900
A null value indicates an unknown value.

73
00:04:04,900 --> 00:04:06,495
When using a Left Join,

74
00:04:06,495 --> 00:04:09,220
if the right table does not have a corresponding value,

75
00:04:09,220 --> 00:04:11,265
a null value is returned.

76
00:04:11,265 --> 00:04:15,815
In this example, borrowers Peters, Li, and Wong,

77
00:04:15,815 --> 00:04:19,040
whose borrower IDs are D8, D6,

78
00:04:19,040 --> 00:04:22,595
and D7 have never taken a book out on loan.

79
00:04:22,595 --> 00:04:27,585
Therefore, there is no corresponding Borrower ID value in the loan table.

80
00:04:27,585 --> 00:04:32,635
In this example, the Left Join displayed all rows from the left table,

81
00:04:32,635 --> 00:04:35,780
the borrower table, and combine the information

82
00:04:35,780 --> 00:04:39,240
with rows from the right table, the loan table.

83
00:04:39,240 --> 00:04:46,145
The borrower table contained rows for borrower ID D8, D6, and D7.

84
00:04:46,145 --> 00:04:51,255
However, the loan table did not contain rows for these Borrower IDs.

85
00:04:51,255 --> 00:04:56,765
Therefore, the result set displayed null values for these Borrower IDs.

86
00:04:56,765 --> 00:05:01,060
Now you can explain the syntax of the Left Outer Join Operator,

87
00:05:01,060 --> 00:05:04,885
and interpret the result set including a null value.

88
00:05:04,885 --> 00:05:07,530
Thanks for watching this video.

89
00:05:07,530 --> 00:05:12,000
(Music)