1
00:00:00,000 --> 00:00:03,485
Hello, and welcome to working with inner joins.

2
00:00:03,485 --> 00:00:07,910
In this video, we will learn about joining two tables to create a result set.

3
00:00:07,910 --> 00:00:09,510
At the end of this lesson,

4
00:00:09,510 --> 00:00:12,300
you will be able to explain the sequel syntax to

5
00:00:12,300 --> 00:00:15,535
join two or more tables and interpret the result set.

6
00:00:15,535 --> 00:00:20,540
A simple select statement retrieves data from one or more columns from a single table.

7
00:00:20,540 --> 00:00:24,905
The next level of complexity is retrieving data from two or more tables.

8
00:00:24,905 --> 00:00:27,800
This is referred to as joining tables.

9
00:00:27,800 --> 00:00:31,160
A join combines the rows from two or more tables based

10
00:00:31,160 --> 00:00:34,550
on a relationship between certain columns in these tables.

11
00:00:34,550 --> 00:00:38,840
There are two types of table joins: inner join and outer join.

12
00:00:38,840 --> 00:00:42,000
The most common type of join is the inner join.

13
00:00:42,000 --> 00:00:45,540
An inner join matches the results from two tables and displays

14
00:00:45,540 --> 00:00:49,665
only the result set that matches the criteria specified in the query.

15
00:00:49,665 --> 00:00:52,810
An inner join returns only the rows that match.

16
00:00:52,810 --> 00:00:55,960
Based on our simplified library database model,

17
00:00:55,960 --> 00:00:58,905
if we want to check the names of people who have borrowed a book,

18
00:00:58,905 --> 00:01:01,595
this information is split between two tables.

19
00:01:01,595 --> 00:01:04,845
We need to find the names of people from the borrower table,

20
00:01:04,845 --> 00:01:06,630
who are listed in the loan table.

21
00:01:06,630 --> 00:01:10,410
So we need to identify the relationship between the two tables.

22
00:01:10,410 --> 00:01:15,200
We do this by identifying a column in each table to link the tables.

23
00:01:15,200 --> 00:01:19,244
In this example, we do this by matching the borrower ID,

24
00:01:19,244 --> 00:01:25,450
notice that the column borrower ID exists in both the borrower table as the primary key,

25
00:01:25,450 --> 00:01:28,225
and the loan table as the foreign key.

26
00:01:28,225 --> 00:01:32,275
A primary key uniquely identifies each row in a table.

27
00:01:32,275 --> 00:01:36,950
A foreign key is a set of columns referring to a primary key of another table.

28
00:01:36,950 --> 00:01:39,920
We use the inner join operator to match

29
00:01:39,920 --> 00:01:43,305
occurrences of the borrower ID in the borrower table,

30
00:01:43,305 --> 00:01:47,200
with occurrences of the borrower ID in the loan table.

31
00:01:47,200 --> 00:01:51,310
This is the syntax of the select statement for an inner join.

32
00:01:51,310 --> 00:01:52,840
In the from clause,

33
00:01:52,840 --> 00:01:55,565
we identify the borrower table as B,

34
00:01:55,565 --> 00:01:58,570
and the loan table as L. For this join,

35
00:01:58,570 --> 00:02:01,685
we select borrower ID, last name,

36
00:02:01,685 --> 00:02:04,145
and country from the borrower table,

37
00:02:04,145 --> 00:02:08,085
and borrower ID, and the loan date from the loan table-

38
00:02:08,085 --> 00:02:11,240
on the condition that the borrower ID in

39
00:02:11,240 --> 00:02:16,180
the borrower table is equal to the borrower ID in the loan table.

40
00:02:16,180 --> 00:02:18,010
Notice that in this join

41
00:02:18,010 --> 00:02:22,890
each column name is prefixed with either the letter B or L. In sequel,

42
00:02:22,890 --> 00:02:25,775
this prefect is referred to as an alias.

43
00:02:25,775 --> 00:02:29,885
Using an alias is much easier than rewriting the whole table name.

44
00:02:29,885 --> 00:02:31,730
And here is the result set.

45
00:02:31,730 --> 00:02:36,965
The result set shows the rows from both tables that have the same borrower ID.

46
00:02:36,965 --> 00:02:41,085
So far, we have seen an example of combining two tables.

47
00:02:41,085 --> 00:02:45,245
But what if you need to combine data from three or more different tables?

48
00:02:45,245 --> 00:02:48,545
You simply add new joins to the sequel statement.

49
00:02:48,545 --> 00:02:52,795
For example, we want to know which borrowers have a book on loan,

50
00:02:52,795 --> 00:02:56,030
but we also want to know which copy of the book they have.

51
00:02:56,030 --> 00:02:58,680
This is how the three tables relate to each other.

52
00:02:58,680 --> 00:03:02,090
In this case, we join the tables two by two.

53
00:03:02,090 --> 00:03:05,210
First we join the information from the borrower table and

54
00:03:05,210 --> 00:03:08,425
the loan table where the borrower IDs match.

55
00:03:08,425 --> 00:03:10,970
Then, we join the information from

56
00:03:10,970 --> 00:03:15,545
the loan table and the copy table with a copy IDs match.

57
00:03:15,545 --> 00:03:18,650
We select the last name from the borrower table,

58
00:03:18,650 --> 00:03:21,240
the copy ID from the loan table,

59
00:03:21,240 --> 00:03:23,565
and the status from the copy table.

60
00:03:23,565 --> 00:03:25,690
And here is the result set.

61
00:03:25,690 --> 00:03:27,685
We see the name of the borrower,

62
00:03:27,685 --> 00:03:29,310
the ID of each book,

63
00:03:29,310 --> 00:03:31,255
and the status they have.

64
00:03:31,255 --> 00:03:33,255
There are five books on loan.

65
00:03:33,255 --> 00:03:35,275
If you wanted to add more tables,

66
00:03:35,275 --> 00:03:38,555
you simply add new joins to the sequel statement.

67
00:03:38,555 --> 00:03:41,490
Now you can explain the sequel syntax to join

68
00:03:41,490 --> 00:03:44,575
two or more tables and interpret the result set.

69
00:03:44,575 --> 00:03:47,200
Thanks for watching this video.

70
00:03:47,200 --> 00:03:52,000
(Music)