Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,400 --> 00:00:03,290
In this lesson, we're
going to take a look
2
00:00:03,290 --> 00:00:05,900
at the concept of a join.
3
00:00:05,900 --> 00:00:09,440
The relational model
for relational databases
4
00:00:09,440 --> 00:00:14,840
says that table data can be
split into different tables.
5
00:00:14,840 --> 00:00:18,350
So we can have one table
with employee information
6
00:00:18,350 --> 00:00:21,500
and one table with
department information.
7
00:00:21,500 --> 00:00:24,800
So using a SELECT
statement, we can get data
8
00:00:24,800 --> 00:00:26,840
from either of those tables.
9
00:00:26,840 --> 00:00:31,040
But how do we make a
connection between the two?
10
00:00:31,040 --> 00:00:34,910
How do we leverage this
concept of a relationship
11
00:00:34,910 --> 00:00:37,380
between relational tables?
12
00:00:37,380 --> 00:00:38,990
And that's what the
join is all about,
13
00:00:38,990 --> 00:00:42,260
and there's a lot of different
ways that we can do joins.
14
00:00:42,260 --> 00:00:44,420
So let's jump in
here by connecting
15
00:00:44,420 --> 00:00:48,200
to our ORCL database
and our Scott user,
16
00:00:48,200 --> 00:00:50,340
and let's expand the table list.
17
00:00:50,340 --> 00:00:50,840
All right.
18
00:00:50,840 --> 00:00:53,000
So we have a table
with employee data
19
00:00:53,000 --> 00:00:55,610
and a table with
department data.
20
00:00:55,610 --> 00:00:59,750
Let's say rather than querying
each of these individually,
21
00:00:59,750 --> 00:01:02,090
we need to find a way
to take some information
22
00:01:02,090 --> 00:01:05,480
from the employee table
and some information
23
00:01:05,480 --> 00:01:09,900
from the department table
and display them together.
24
00:01:09,900 --> 00:01:13,840
Let's click the plus
by each one of these.
25
00:01:13,840 --> 00:01:16,720
The way that we're
going to leverage a join
26
00:01:16,720 --> 00:01:20,620
is by joining on
a common column.
27
00:01:20,620 --> 00:01:24,130
So let's see what we have
between these two tables.
28
00:01:24,130 --> 00:01:26,110
Do they have a relationship?
29
00:01:26,110 --> 00:01:30,290
If they have a relationship,
they'll have a common column.
30
00:01:30,290 --> 00:01:35,440
So notice that the Department
table has a DEPTNO column,
31
00:01:35,440 --> 00:01:38,900
and the EMP table has a
DEPTNO column, as well.
32
00:01:38,900 --> 00:01:41,650
So we could say they
have a common column.
33
00:01:41,650 --> 00:01:44,500
If they do, which in
this case they do,
34
00:01:44,500 --> 00:01:47,770
we can use that
common column to form
35
00:01:47,770 --> 00:01:51,940
a relationship between employee
data and department data.
36
00:01:51,940 --> 00:01:54,100
And we do this with
a SELECT statement.
37
00:01:54,100 --> 00:01:56,800
Before we leverage
the use of the join,
38
00:01:56,800 --> 00:01:59,560
let's take a look
at what happens when
39
00:01:59,560 --> 00:02:02,260
we don't leverage the join.
40
00:02:02,260 --> 00:02:10,150
So here we're going to say
select star from dept comma
41
00:02:10,150 --> 00:02:10,760
emp.
42
00:02:10,760 --> 00:02:11,260
All right.
43
00:02:11,260 --> 00:02:13,240
When we normally use
a SELECT statement,
44
00:02:13,240 --> 00:02:18,580
we will say select star or
column list from a table.
45
00:02:18,580 --> 00:02:20,050
But here we have two tables.
46
00:02:20,050 --> 00:02:24,160
So we're saying I want all
the data from the DEPT table
47
00:02:24,160 --> 00:02:25,630
and the EMP table.
48
00:02:25,630 --> 00:02:28,500
Let's click Execute.
49
00:02:28,500 --> 00:02:30,880
What do we have here?
50
00:02:30,880 --> 00:02:34,000
Well, the EMP table has
about 12 rows in it.
51
00:02:34,000 --> 00:02:37,570
The DEPT table has, I
believe, five or six.
52
00:02:37,570 --> 00:02:42,610
And yet, look at all
this that's come back.
53
00:02:42,610 --> 00:02:45,440
How has this helped us?
54
00:02:45,440 --> 00:02:48,680
This is what's known
as a Cartesian join,
55
00:02:48,680 --> 00:02:51,980
a Cartesian product,
or a cross product.
56
00:02:51,980 --> 00:02:55,400
It's referred to as all three
in different situations.
57
00:02:55,400 --> 00:02:59,150
And what has happened
here is that our command
58
00:02:59,150 --> 00:03:02,630
is taking every row
from the EMP table
59
00:03:02,630 --> 00:03:06,680
and joining it to every
row in the DEPT table.
60
00:03:06,680 --> 00:03:09,260
So there's DNAME, LOC.
61
00:03:09,260 --> 00:03:12,020
These are from the DEPT table.
62
00:03:12,020 --> 00:03:17,000
And it's joining it to
every row in the EMP table.
63
00:03:17,000 --> 00:03:21,470
And so what you get is far more
data, and really useless data
64
00:03:21,470 --> 00:03:22,820
in most cases.
65
00:03:22,820 --> 00:03:25,910
So a Cartesian join,
or a Cartesian product,
66
00:03:25,910 --> 00:03:29,810
is usually a mistake, and it
can be, with large tables, one
67
00:03:29,810 --> 00:03:32,480
of the biggest performance
problems, or performance
68
00:03:32,480 --> 00:03:35,540
mistakes, in a
relational database
69
00:03:35,540 --> 00:03:39,800
because it brings back orders
of magnitude, more data,
70
00:03:39,800 --> 00:03:41,600
than you actually want.
71
00:03:41,600 --> 00:03:46,010
So we need to do a join, and
we need to do it the right way.
72
00:03:46,010 --> 00:03:48,520
So let's start over.
73
00:03:48,520 --> 00:03:50,920
And let's pick the
columns we want.
74
00:03:50,920 --> 00:03:54,440
So I want to know
the employee name.
75
00:03:54,440 --> 00:03:57,660
I want to know
their job, manager.
76
00:04:00,420 --> 00:04:07,460
And from the DEPT table,
we'll put deptno and dname
77
00:04:07,460 --> 00:04:13,310
from dept table comma emp table.
78
00:04:13,310 --> 00:04:16,190
Now, this would give
us just fewer columns
79
00:04:16,190 --> 00:04:18,110
and the Cartesian product.
80
00:04:18,110 --> 00:04:22,370
So what we need is some sort
of way to do the join actually
81
00:04:22,370 --> 00:04:24,380
syntactically in the code.
82
00:04:24,380 --> 00:04:26,750
Now, there's two
ways to do this.
83
00:04:26,750 --> 00:04:30,410
There's what's kind of referred
to as a deprecated way and then
84
00:04:30,410 --> 00:04:32,600
the ANSI standard way.
85
00:04:32,600 --> 00:04:35,450
And it's interesting because
the deprecated way is actually
86
00:04:35,450 --> 00:04:39,810
far more common than
the ANSI standard way.
87
00:04:39,810 --> 00:04:42,590
The deprecated method would
add a WHERE clause, some kind
88
00:04:42,590 --> 00:04:44,690
of limiting condition.
89
00:04:44,690 --> 00:04:48,170
And it would basically be this--
90
00:04:48,170 --> 00:04:50,720
where deptno equals deptno.
91
00:04:50,720 --> 00:04:51,800
So right?
92
00:04:51,800 --> 00:04:53,840
These are the columns
that are common, right?
93
00:04:53,840 --> 00:04:56,750
So we want to set those equal.
94
00:04:56,750 --> 00:04:59,720
But this isn't going to work
for us because it's not going
95
00:04:59,720 --> 00:05:02,570
to know which column is which.
96
00:05:02,570 --> 00:05:06,290
And so we need to set up a
way here to identify these.
97
00:05:06,290 --> 00:05:11,690
And the way we can do this is
through table dot notation.
98
00:05:11,690 --> 00:05:13,900
So we preface the
column with the name
99
00:05:13,900 --> 00:05:16,280
of its corresponding table.
100
00:05:16,280 --> 00:05:20,190
So we have a DEPTNO column
in the DEPT table and the EMP
101
00:05:20,190 --> 00:05:21,300
table.
102
00:05:21,300 --> 00:05:24,100
So we're going to
set those equal.
103
00:05:24,100 --> 00:05:26,350
Now, what we also
might want to do
104
00:05:26,350 --> 00:05:33,040
is preface these columns up
here with the DEPTNO table dot
105
00:05:33,040 --> 00:05:35,260
notation for each column.
106
00:05:35,260 --> 00:05:39,400
For instance, this DEPTNO
column is not identified, right?
107
00:05:39,400 --> 00:05:40,960
So which one is it?
108
00:05:40,960 --> 00:05:45,110
So we'll say this is EMP DEPTNO.
109
00:05:45,110 --> 00:05:46,340
Now let's execute.
110
00:05:49,030 --> 00:05:52,360
So this is much more
what we might expect.
111
00:05:52,360 --> 00:05:56,420
There are 14 rows
in the EMP table.
112
00:05:56,420 --> 00:05:59,050
And notice we have
14 total rows here.
113
00:05:59,050 --> 00:06:02,830
And here is our EMP
information, and here
114
00:06:02,830 --> 00:06:05,140
is our DEPT information.
115
00:06:05,140 --> 00:06:08,680
So it shows Clark as a
manager and his department
116
00:06:08,680 --> 00:06:12,100
and his department name, so
on and so forth through here.
117
00:06:12,100 --> 00:06:17,230
So we've used table dot notation
in order to specify this.
118
00:06:17,230 --> 00:06:19,360
We could also do
it a different way.
119
00:06:19,360 --> 00:06:22,030
And actually, that
way is pretty common.
120
00:06:22,030 --> 00:06:25,220
And that's using alias notation.
121
00:06:25,220 --> 00:06:27,490
So what I've done
here is I've created
122
00:06:27,490 --> 00:06:31,270
an alias for the DEPT
table called d and an alias
123
00:06:31,270 --> 00:06:33,400
for the EMP table called e.
124
00:06:33,400 --> 00:06:37,510
And so now, instead of putting
the full table name in front,
125
00:06:37,510 --> 00:06:41,670
I'm just going to
put this alias.
126
00:06:41,670 --> 00:06:46,390
And we need to do
it here, as well.
127
00:06:46,390 --> 00:06:50,460
So now, the e is going to
refer to the EMP table,
128
00:06:50,460 --> 00:06:55,080
and the d is going to
refer to the DEPT table.
129
00:06:55,080 --> 00:06:59,860
And notice that it brings
back the same data.
130
00:06:59,860 --> 00:07:04,190
What about the ANSI
standard notation?
131
00:07:04,190 --> 00:07:06,880
Well, let's take a look at that.
132
00:07:06,880 --> 00:07:08,320
There's a couple
of different ways
133
00:07:08,320 --> 00:07:12,110
we can use the ANSI
standard notation.
134
00:07:12,110 --> 00:07:12,800
Take this out.
135
00:07:12,800 --> 00:07:15,080
We're going to get back
to just the columns
136
00:07:15,080 --> 00:07:17,460
that we actually want.
137
00:07:17,460 --> 00:07:21,980
We could use what's called
the natural join syntax.
138
00:07:21,980 --> 00:07:28,270
We could say from
emp natural join
139
00:07:28,270 --> 00:07:34,450
dept. Let's click Execute here.
140
00:07:34,450 --> 00:07:37,150
Notice that it brought
back the same information.
141
00:07:37,150 --> 00:07:40,240
But we didn't tell it how
to join the two tables.
142
00:07:40,240 --> 00:07:42,190
We didn't say, set
this column value
143
00:07:42,190 --> 00:07:44,980
equal to this column value
in a different table.
144
00:07:44,980 --> 00:07:48,460
And we didn't even clarify which
column we were referring to
145
00:07:48,460 --> 00:07:49,840
in this case.
146
00:07:49,840 --> 00:07:52,990
Well, the natural join syntax
is a little bit smarter,
147
00:07:52,990 --> 00:07:54,250
if you will.
148
00:07:54,250 --> 00:07:57,430
And so it looks at the
two tables and says,
149
00:07:57,430 --> 00:08:01,930
there are two columns, one in
one table and one in another,
150
00:08:01,930 --> 00:08:04,210
and those columns
are named the same,
151
00:08:04,210 --> 00:08:07,570
and it relates those two
together sort of naturally.
152
00:08:07,570 --> 00:08:09,700
That's why it's
called natural join.
153
00:08:09,700 --> 00:08:12,310
It makes a natural
join between the two
154
00:08:12,310 --> 00:08:14,120
and then presents the data.
155
00:08:14,120 --> 00:08:17,940
So that's the
natural join syntax.
156
00:08:17,940 --> 00:08:22,620
Another way we can do this
using the ANSI standard notation
157
00:08:22,620 --> 00:08:25,670
is join using.
158
00:08:25,670 --> 00:08:32,190
So let's say from
emp join dept using,
159
00:08:32,190 --> 00:08:37,380
and in parentheses we'll
specify the column.
160
00:08:37,380 --> 00:08:41,430
So what we've done here, using
the ANSI standard notation,
161
00:08:41,430 --> 00:08:45,130
is we're going to use
clauses join and using.
162
00:08:45,130 --> 00:08:49,650
So we're saying join the EMP
table to the DEPT table using
163
00:08:49,650 --> 00:08:51,420
this column, DEPTNO.
164
00:08:51,420 --> 00:08:53,580
And notice the data
that it brings back
165
00:08:53,580 --> 00:08:57,920
is exactly what we've seen
in each one of these joins.
13098
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.