0:02
[Music]
0:11
hello friends welcome back to our
0:13
channel so in today's session we'll
0:15
discuss about one more concept in
0:17
dbms that is joints
0:25
so first let us see how many types of
0:28
joints and then we'll see one by one
0:31
with an example
0:33
so these joints are classified into two
0:36
categories
0:37
one is a inner join
0:41
inner join
0:42
and another one is a
0:44
outer join
0:46
outer join
0:48
right so in this inner join again it was
0:51
classified into two categories
0:54
one is
0:56
theta join
0:59
another one is
1:01
natural join
1:03
natural join
1:06
and this outer join is classified into
1:08
three categories
1:12
left outer join
1:16
right outer join
1:21
full outer join
1:24
so three categories
1:26
so this is usually the join is
1:28
represented as
1:30
this symbol
1:34
this is a joint representation
1:36
and coming to the theta join
1:39
so the representation will be
1:41
like this and here we have to specify
1:44
the
1:45
condition
1:46
condition and without condition if you
1:49
apply the join that join we call it as a
1:52
natural join
1:53
natural joint right and left of the
1:56
joint
1:56
this is also similar
2:01
so this is the left outer join
2:03
representation
2:05
next
2:12
this is a right outer join
2:15
and
2:22
this is a full outer join this is a full
2:25
outer join right so these are the
2:28
representation these are the
2:30
representation of
2:31
this join operations
2:35
right so
2:37
coming to the venn diagrams if you
2:39
consider the venn diagram so
2:41
this is a natural join actually theta
2:44
join will be represented as a cartesian
2:46
product okay so in the previous session
2:48
we have studied about the cartesian
2:50
product so the same cartesian product we
2:53
can consider as a theta joint right
2:55
and see left or join
2:57
if you consider
3:03
so this will be the left outer join
3:06
this will be the left outer join
3:16
this will be the right outer join
3:27
this will be the full outer join
3:29
right so that means
3:30
see coming to the natural join based
3:32
upon the attribute
3:35
attributes equal attributes the two
3:37
relations will be get joined right so
3:40
coming to the left or join so it will
3:43
consider all the records of a left
3:45
relation and a common relation from the
3:48
right relation common tuples from the
3:50
right relation similarly right outer
3:53
join it will consider all the tuples of
3:56
a
3:56
right relation and common tuples from
3:59
left relation and full of joint it will
4:02
be having a both the tuples of both the
4:05
relations
4:06
okay both the relations
4:08
so
4:09
this is the
4:10
overview of join operations in
4:14
relational algebra of dbms now we will
4:17
see one by one by taking an example
4:20
starting with the theta join so here the
4:23
two relations will be joined based upon
4:26
the condition
4:28
right so
4:29
based upon the condition condition means
4:32
so any operator any any relational
4:34
operator we can give any relational
4:36
operator and we can
4:39
relate or we can compare two different
4:41
attributes or one attribute with a value
4:44
right any type of condition based upon
4:46
any type of condition if you want to
4:48
join two relations that will comes under
4:50
this theta join
4:52
and
4:53
the two relations joined with respect to
4:56
the common attribute so both the
4:57
relation should have the common
4:58
attribute so with the help of with
5:00
respect to that common attribute if two
5:03
tables or two relations is being joined
5:06
that type of join we call it as a
5:08
natural join or sometimes we can also
5:11
called as
5:12
equi join
5:15
equi joint
5:16
equi joint
5:18
right
5:19
so we will see
5:21
one by one with a small example
5:26
see let us take these two tables a car a
5:28
model and a price and jeep model and a
5:31
price g price and a car price
5:33
okay so here
5:36
you can see
5:37
let us join so let it be the relation
5:40
name is c and relation name is j
5:43
relation name is j so we have to find
5:46
c
5:48
theta join
5:51
theta join
5:52
so c dot price
5:57
let us take
6:00
j dot price
6:02
greater than c dot price of
6:06
j so we have to apply the theta join
6:10
with this condition j dot price greater
6:13
than c dot price
6:14
so
6:15
let us take this one first let us
6:18
consider the j dot price greater than
6:21
c dot price consider the first one 6 000
6:24
which is greater than
6:26
sorry 6 lakhs which is greater than 4
6:28
lakhs so there will be join with these
6:31
terms
6:32
see
6:37
jeep
6:38
one
6:40
six lakhs
6:42
car one
6:44
four legs
6:47
right
6:48
so next six lakhs greater than five
6:51
lakhs so j dot price greater than c dot
6:54
price so six lakhs is greater than five
6:56
lakhs so again there will be an
6:59
uh join
7:00
between g1 and car 2
7:03
so jeep one
7:05
with
7:06
six lakhs
7:08
associated with a car two
7:10
with
7:12
five x
7:13
and six lakhs greater than eight lakhs
7:17
no it's not true so these two
7:20
tuples will not be joined these two
7:22
tuples will not be joined coming to the
7:24
second one
7:25
two
7:26
ten lakhs greater than four legs yes so
7:31
give to
7:34
10 lakhs
7:36
associated with a car one
7:38
with the 4 lakhs
7:40
next 10 lakhs greater than 5 lakhs yes
7:44
so jeep two
7:47
ten lakhs
7:50
car two
7:52
with the five lakhs
7:54
ten lakhs greater than eight lakhs two
7:58
jeep two
8:02
ten lakhs
8:04
car three with eight legs
8:08
so
8:09
this is the relation
8:13
the resultant relation
8:16
after
8:17
doing this
8:19
inner join that is also a theta join
8:22
right so
8:23
this is
8:25
jeep model
8:28
and this is a price
8:30
this one is a car model
8:36
and this one is a price
8:38
so
8:39
this is the relation
8:42
this one is a
8:43
resultant relation after applying the
8:47
theta joint so that means
8:49
here the two relations will be get
8:52
joined with respect to the condition
8:54
not with respect to the attributes
8:57
right so here the two attributes
9:00
of differ i mean the attribute from
9:02
different tables may not be equal
9:04
but the data type must be
9:06
equal okay so this is the
9:10
theta joint
9:12
theta
9:13
joint
9:15
so this is similar to our cartesian
9:18
product or cross product
9:20
okay so hope you understood this theta
9:22
joint the next one is
9:25
natural join
9:29
natural join
9:33
so for this natural join
9:36
we will consider one example
9:38
and will apply the natural join
9:43
so
9:44
let us take these two
9:45
tables as an example a student table
9:49
having the attributes the student id
9:51
student name and the department id and
9:54
coming to the second one second table
9:55
there is a department table which
9:57
consists of department id and the
9:58
department name
10:00
so
10:01
let it be
10:02
yes is a relation here and d is the name
10:05
of the relation here student and
10:07
department
10:08
now
10:11
natural join
10:14
natural join so here we are not supposed
10:17
to give any condition so based upon the
10:20
similar attribute from the two tables
10:23
the join will be appeared
10:25
so
10:26
relations
10:28
relations
10:31
will get joined
10:36
with respect to
10:40
with respect to
10:42
common attribute
10:44
common
10:46
attribute so from this common attribute
10:49
we'll get the
10:51
join right so
10:53
here yes
10:57
join
10:58
d a natural join
11:01
is
11:01
see let us take all the
11:04
tables so s id
11:07
s name
11:11
department id
11:14
dna so we need not
11:16
write the common attribute multiple
11:18
times
11:19
okay the same attribute we need not
11:21
write the multiple times because in
11:23
student table there is a department id
11:26
and in the department table there is a
11:27
department id so we can combine both
11:29
with respect to this department area so
11:31
coming to this one zero one
11:34
with the department one
11:35
right so one zero one
11:38
the name is
11:42
raju
11:43
and department id is one
11:45
okay so
11:47
let us take here
11:52
okay let us take one more
11:58
four okay
11:59
so one not one some raju with the
12:02
department id one
12:03
which is
12:04
csu
12:06
okay next one zero two so 2 there is a 2
12:10
here
12:11
so 1 0 2
12:15
ravi
12:16
department id is 2 version 8 e c
12:20
then 1 0 3 so 1 zero three with the
12:23
department id one yes there is a
12:25
department id one
12:27
so one zero three
12:31
some ramu
12:33
department id one
12:36
one
12:37
and
12:38
cse
12:40
and 104
12:43
hurry with the department id 3
12:46
the branch is
12:48
tripling
12:49
so
12:50
this is the
12:52
join this is the natural join so
12:57
the two relations will get joined
13:00
with respect to the common attribute
13:02
with respect to the common attribute
13:05
right so here the common attributes with
13:07
department id so with respect to this
13:09
common attribute
13:11
the relations or the relations will be
13:14
get joined
13:16
right so hope you understood this one
13:18
so that is called a
13:20
natural join
13:22
the next one
13:24
the next one
13:26
outer joins
13:27
will see the outer joins
13:39
outer join so let us take an example
13:43
yes consider the same
13:45
examples so outer join and in that first
13:48
we'll go with the
13:50
left outer join
13:52
left
13:53
outer
13:56
join
13:57
so
13:57
left outer join means
14:00
yes
14:04
this is the
14:05
relation for left outer join this is the
14:08
notation rotation for left outer join
14:10
that means
14:11
in the resultant we have to consider all
14:15
the tuples of left relation that means
14:18
yes and common tuples of d
14:22
so
14:24
if the result
14:28
results
14:33
relation
14:38
will have
14:41
all the tuples
14:43
all the tuples or records or entities
14:46
of yes
14:48
and
14:52
common tuples
14:54
of
14:55
d
14:57
so first let us write here
14:59
yes id
15:01
yes name
15:04
d
15:06
department id
15:09
and then
15:13
department
15:15
name
15:17
right so
15:18
write down all the values 1 0 1
15:21
1 0 2
15:23
1 0 3
15:24
1 104
15:27
so let us take a raju
15:31
ramu
15:35
sorry
15:44
so department id
15:46
so 1.1 1
15:50
2
15:51
1
15:52
3
15:53
and department name
15:55
see one
15:56
it's a csc
15:58
two
16:00
it is a ece
16:02
three
16:04
sorry again one it's a csc
16:07
and three
16:08
it's a
16:10
tripoli
16:11
so if there is a one more
16:14
name
16:19
some suresh with the department five
16:23
let us think okay
16:25
let us take this one
16:26
so you have to consider one more one
16:29
zero five
16:30
some suresh
16:31
with the department file but there is no
16:34
details in the department table so
16:37
here we have to give it as a
16:40
num
16:41
here we have to give it as a null so we
16:44
have to consider all the tuples of
16:47
relation yes
16:49
and
16:50
what are the common attributes so there
16:52
are one two one three so one two three
16:56
details are there so we have to consider
16:57
only this one and we should not consider
17:00
this four
17:01
the last one right and
17:03
for 105
17:05
it is associated with the department id
17:07
5 which is not available in department
17:09
name so just pad it with null
17:12
pad it with null
17:14
so we are giving
17:16
all the tuples of yes
17:19
and a common tuples of d
17:22
right so common tuples are 1 2 3 because
17:24
there is no 4
17:26
and there is no 5 also so we have given
17:29
as a null big but 5 is available in yes
17:31
so we have to write all the tuples of
17:33
yes so we have written one out five also
17:38
right
17:39
so this is called a left outer join so
17:41
we have to consider all the tuples of
17:44
the left hand side relation
17:46
and we have to consider only the common
17:49
tuples of right hand side relation
17:52
and the next one
17:55
the next one
17:58
right outer join
18:00
so left is completed
18:02
right right outer join so for this we
18:06
have to represent yes
18:12
this one
18:13
so for this we have to consider all the
18:16
tuples of right
18:18
right relation and common tuples of left
18:21
relation
18:24
so
18:25
the resultant
18:30
relation
18:33
we have
18:37
all the tuples of
18:42
d
18:43
and
18:46
common tuples of
18:51
yes
18:52
common to pulls off yes so consider
18:55
see yes
18:57
right outer join d
18:59
so you can consider s id
19:02
yes name
19:05
d dpt id
19:08
and
19:09
d name so consider all the values of d
19:12
all the tuples of d so first write down
19:15
one
19:16
c s e
19:18
two
19:19
e c e
19:21
three
19:23
triple e
19:25
four
19:27
neck
19:29
now consider department one
19:31
so
19:32
one zero one
19:36
raju
19:37
2 1 0 2
19:42
ravi
19:44
and again 1
19:45
right again 1 so consider 1
19:50
1 0 3
19:53
ramu
19:56
right
19:57
ramu and this is also csc
20:00
next
20:01
3
20:02
1 0 4 3
20:04
1 zero
20:06
sorry one zero four
20:09
honey
20:10
right what about a four
20:12
there is no association with the four so
20:16
there are no values for
20:19
department id 4 in student id and
20:22
student name
20:24
right hope you understood so first write
20:26
down the all the tuples of department
20:28
table
20:29
all the tuples of department table
20:31
now write on the common tuples of yes
20:34
right common tuples of yes so there is
20:37
association with department one so write
20:39
down this tuple there is association
20:40
with the department two right on this
20:42
tuple and there is again one more
20:44
association with department one so add
20:46
this one
20:47
and there is association with department
20:49
three
20:50
right on
20:51
so
20:52
there is no association with file in
20:54
department id so you need not consider
20:57
this one because this is yes we have to
20:59
write only the common tuples what are
21:00
the common thing one two and three
21:03
so here
21:05
we are not having
21:06
any association with the department for
21:09
any students who associated with the
21:10
department for so that's why
21:13
student id and student name for
21:15
department id 4 will be kept null will
21:18
be padded with null
21:20
so similarly we which we have done in
21:22
the
21:23
left outer join also left outer join
21:26
right so here we have to consider all
21:28
the tuples of
21:30
right side relation and common tuples of
21:33
left side relation
21:35
right so this is how
21:37
we have to apply the right outer join
21:42
so hope you understood this one the next
21:43
one is full outer join
21:47
full outer join
21:54
full outer join so this is represented
21:56
in this way so we have to consider all
21:58
the tuples of s and all the tuples of d
22:01
so let us write here
22:03
yes id
22:05
yes name
22:08
dapt id
22:11
the
22:12
name
22:13
so item
22:14
101
22:18
raju
22:19
1
22:22
102
22:26
ravi 2
22:28
103
22:32
ramu
22:33
1
22:35
104
22:38
hurry three
22:41
one zero five
22:45
suresh
22:46
five so write down this one d name one
22:49
means c s c
22:51
two means e c
22:54
3 means
22:56
sorry again 1 so c s c
23:00
3 means repeat
23:02
so is there any relation with the 5
23:04
no
23:05
so kept it as a null kept it as a number
23:09
right and there is no
23:11
department id 4 so insert 4
23:14
with
23:16
mechanical so there is no association
23:18
with the students any one of the student
23:20
so
23:21
kept it as
23:22
null here
23:25
kept it as null here
23:28
so this is this is the
23:31
full outer join of these two relations s
23:34
and d so this is nothing but the union
23:36
operation of
23:38
left outer join and the right outer join
23:41
so up to here it is a left outer join
23:43
and this one is a right outer join so we
23:45
need to apply the union so we have seen
23:48
the union operation in the previous
23:50
session right so once you
23:52
refer that one so the link will be in
23:54
the description section so the union
23:56
operation if you apply the union
23:57
operation it will combine all the tuples
23:59
of both the relation and automatically
24:01
the duplicates will be get deleted right
24:04
so we'll get the same thing
24:06
so full outer join means all the tuples
24:09
of relation yes and all the tuples of
24:12
relation d and if there is no
24:14
association simply pad with
24:16
null values paired with
24:18
null values
24:19
so this is the
24:21
full outer join
24:24
right so hope you understood this join
24:26
operations so two categories of joints
24:29
inner join and outer join and in the
24:32
inner join the theta join based upon the
24:34
condition
24:36
so uh the tuples will be joined with
24:39
with respect to the condition given
24:41
and the next one is a natural join so
24:43
two relations will be joined with
24:45
respect to the common attribute and the
24:47
leftover
24:48
the i mean the outer joins the left
24:51
outer join we have to consider all the
24:53
tuples of left left left side relation
24:56
and common to pulls off right side
24:57
relation and right out to the outer join
25:00
we have to consider all the tuples of
25:02
right or right relation and common to
25:05
pull soft left side relation and coming
25:07
to the full outer join we have to
25:08
consider all the tuples of left side
25:11
relation and and right side relation and
25:14
also simply we can say the full outer
25:17
join is a union operation between left
25:20
outer join and right outer join
25:23
right so hope you understood this
25:26
joint concept in relational algebra of
25:28
dbms so let's stop here and if you are
25:31
having any doubts regarding these joints
25:33
so feel free to post your doubts in the
25:35
comment section definitely i will try to
25:37
clarify all your doubts if you really
25:39
enjoyed my session like my session share
25:41
my session with your friends and don't
25:42
forget to subscribe to our channel
25:45
thanks for watching thank you very much