1
00:00:00,000 --> 00:00:03,290
Hello and welcome to working with multiple tables.

2
00:00:03,290 --> 00:00:08,075
In this video, we will learn about JOINing two tables to create a result set.

3
00:00:08,075 --> 00:00:12,340
At the end of this lesson, you will be able to define the JOIN operator,

4
00:00:12,340 --> 00:00:16,610
explain the role of primary keys and foreign keys in a JOIN operation,

5
00:00:16,610 --> 00:00:19,720
and list different types of JOIN operators.

6
00:00:19,720 --> 00:00:24,825
A simple select statement retrieves data from one or more columns from a single table.

7
00:00:24,825 --> 00:00:29,180
The next level of complexity is retrieving data from two or more tables.

8
00:00:29,180 --> 00:00:34,120
This leads to multiple possibilities of how the ResultSet is generated.

9
00:00:34,120 --> 00:00:36,720
To combine data from two tables,

10
00:00:36,720 --> 00:00:39,010
we use the JOIN operator.

11
00:00:39,010 --> 00:00:42,390
A JOIN combines the rows from two or more tables

12
00:00:42,390 --> 00:00:46,460
based on a relationship between certain columns in these tables.

13
00:00:46,460 --> 00:00:49,805
Based on the book and author entity examples,

14
00:00:49,805 --> 00:00:53,490
these are part of a simplified library database model.

15
00:00:53,490 --> 00:00:58,590
This entity relationship diagram represents the relational data model for

16
00:00:58,590 --> 00:01:03,650
the author and book entity as well as other entities such as borrower,

17
00:01:03,650 --> 00:01:06,925
loan, copy, and author list.

18
00:01:06,925 --> 00:01:10,825
In this model, the information is split into different tables.

19
00:01:10,825 --> 00:01:15,110
If you wanted to know which borrower has which copy of a book out on loan,

20
00:01:15,110 --> 00:01:18,050
you need to gather data from three tables:

21
00:01:18,050 --> 00:01:21,765
the borrower, loan, and copy tables.

22
00:01:21,765 --> 00:01:24,830
This is when you need to use the JOIN operator.

23
00:01:24,830 --> 00:01:26,615
With the JOIN operator,

24
00:01:26,615 --> 00:01:29,480
you are combining data from more than one table based on

25
00:01:29,480 --> 00:01:32,885
a relationship between certain columns in these tables.

26
00:01:32,885 --> 00:01:37,630
So, the first thing you need to do is identify the relationship between those tables.

27
00:01:37,630 --> 00:01:40,610
That is, the column or columns in

28
00:01:40,610 --> 00:01:44,270
each table that will be used as a link between the tables.

29
00:01:44,270 --> 00:01:49,910
In this entity relationship diagram, notice the author_ID,

30
00:01:49,910 --> 00:01:57,600
book_ID, borrower_ID, and copy_ID had the primary key icon.

31
00:01:57,600 --> 00:02:01,560
A primary key uniquely identifies each row in a table.

32
00:02:01,560 --> 00:02:05,000
Notice also the entities on the lower half of the screen-

33
00:02:05,000 --> 00:02:08,675
some attributes have FK in brackets next to them.

34
00:02:08,675 --> 00:02:16,340
For example, the copy entity has attribute book_ID with the FK in brackets.

35
00:02:16,340 --> 00:02:18,995
This identifies the foreign key.

36
00:02:18,995 --> 00:02:23,810
A foreign key is a set of columns referring to a primary key of another table.

37
00:02:23,810 --> 00:02:27,525
So, if you wanted to know which borrower has a book out on loan,

38
00:02:27,525 --> 00:02:31,315
you need to gather data from the borrower and loan tables.

39
00:02:31,315 --> 00:02:34,990
You will need the borrower ID from both tables.

40
00:02:34,990 --> 00:02:38,240
SQL offers you several different types of JOINs.

41
00:02:38,240 --> 00:02:40,755
It all depends on what you are looking for.

42
00:02:40,755 --> 00:02:44,730
For example, you can extract a data set corresponding to

43
00:02:44,730 --> 00:02:50,005
the intersection of the two tables involved or you can choose a bigger data set.

44
00:02:50,005 --> 00:02:52,180
You can go up to the point of selecting

45
00:02:52,180 --> 00:02:55,935
the combination of all the data from those two tables.

46
00:02:55,935 --> 00:02:58,580
Now, you can define the JOIN operator,

47
00:02:58,580 --> 00:03:03,175
explain the role of primary keys and foreign keys in a JOIN operation,

48
00:03:03,175 --> 00:03:06,300
and list different types of JOIN operators.

49
00:03:06,300 --> 00:03:09,000
Thanks for watching this video.