馃攳
Lec-38: Self Join operation with Example | Database Management System - YouTube
Channel: Gate Smashers
[0]
Hello friends,
[1]
Welcome to Gate Smashers
[2]
the topic is Self join
[4]
in self join the table is joined
[7]
with itself
[8]
means we join a table
[11]
with itself
[13]
so actually where do we
[15]
need the use of self join
[17]
for that I have taken a scenario
[19]
where I am talking about study table
[22]
all the online material available with me
[26]
either we search somewhere on google
[29]
or we search on youtube
[31]
maximum time you will
[33]
find one example only
[34]
there is a employee there is a manager
[36]
so find employee and manager id
[39]
one who is employee
[40]
and find his manager
[41]
so that example
[43]
because although I have also
[44]
read that example during my time
[47]
the example here I am going to
[49]
explain you with a fresh example
[51]
in that example first
[53]
of all let me tell you
[54]
what is the scenario
[56]
scenario means which
[57]
table we are taking
[58]
and how do we apply join
[60]
self join on it
[61]
so the table is study
[64]
in study table there are three columns
[66]
student ID that is S_ID
[68]
course ID, C_ID
[71]
and since
[73]
means this is giving me information
[75]
about students ID
[77]
it is giving about course ID
[79]
and this on since, since means
[80]
which student
[82]
is enrolled in which course or
[84]
which student is studying which course
[87]
and from when
[89]
but in actual
[91]
from where does this study
[92]
table is taking information
[93]
from where data is coming to it
[95]
here what the S_ID is doing?
[98]
as a foreign key and
[99]
and it is taking reference
[101]
from student table
[102]
now you assume there is additional
[104]
there is a SID
[106]
and this is giving it reference
[108]
there is one more table course
[110]
in that course, this is the course id
[112]
and that is giving reference to it
[114]
means this is a relationship
[116]
table intermediate table
[118]
which is giving information
[119]
that which student
[121]
is studying which course and when
[125]
this is the actual view
[126]
the scenario is this
[128]
there are 3 tables but now
[130]
we don't need these tables
[132]
only on this table we are applying join
[135]
and on that we have written a query
[137]
and we will solve that query
[138]
therefore you concentrate on this table
[141]
where student id, course id and this
[144]
student id and course id
[146]
are of composite primary
[148]
here we have taken
[149]
composite primary key
[150]
composite means
[151]
such a primary key
[153]
which is made up of two attributes
[155]
so here the two attributes
[157]
student id and course id
[159]
are making the primary
[160]
key in combined way
[162]
now what is the second
[163]
thing we are talking about
[165]
there is one query we will solve
[167]
query is
[169]
find the student id
[171]
who is enrolled
[172]
in atleast two courses
[175]
means we have to find student id
[177]
S1, S2, S1 lots of id are there
[181]
and what courses are they studying
[184]
but what information
[186]
actually we have to find out
[188]
id of that student
[190]
who is enrolled in
[191]
atleast two courses
[193]
what is the meaning of atleast two?
[194]
minimum two
[196]
it can be more than two
[198]
but enrolled in minimum two courses
[202]
so here I have put the
[203]
data in such a way that
[204]
you will know that S1 is enrolled in C1
[207]
S2 is enrolled in C2
[209]
S1 is enrolled in C2
[211]
means
[212]
S1 student
[214]
is enrolled in two courses
[217]
so what should come in output?
[219]
S1
[220]
S2 should not come because
[222]
S2 is enrolled only in C2
[224]
we have taken the content of
[225]
this table in such a way that
[227]
so that we can easily solve
[229]
value and the question
[231]
now we will frame this query
[234]
and we will solve this query with
[236]
self join
[237]
although we can solve this
[239]
query with sub query also
[240]
we can also do it with nested query
[243]
but now we are concentrating on self join.
[247]
So what is self join?
[249]
We are joining one table with itself
[252]
so we are using study table here
[255]
so here we have to join the
[257]
study table with itself again
[259]
so for that the query
[261]
if I write the query here
[264]
let's say select
[265]
what we have to select
[267]
we will look afterward
[268]
because always the query
[270]
from where the sequel
[272]
query start to execute
[273]
with from
[274]
so first of all I have to see the table
[276]
which tables I need
[279]
what are the tables do I need here
[284]
So first of all
[285]
you know that
[286]
whenever we solve any query
[289]
first of all we find what tables do we need
[292]
so obviously here there is only one table
[294]
you will have to take
[295]
the help of that table only
[296]
so we have to use the study table
[299]
but
[301]
now this from study
[303]
now I have to use this table two times
[306]
how will you use this table two times
[308]
because whenever join occurs
[311]
join is always cross product
[314]
plus some conditions
[318]
cross product plus some conditions
[322]
so without cross product
[324]
join cannot happen
[325]
even I have made a
[327]
video of natural join
[329]
you can check in that also
[330]
I've made a video on equi
[332]
join you can check there also
[333]
that whenever we join
[335]
first there will be cross product
[336]
means two tables will get
[337]
multiplied with themselves
[339]
but here there is only one table
[340]
and from that table I
[342]
have to bring output
[343]
so how will it happen?
[344]
means we have to multiply
[346]
study table with study table
[349]
means you imagine
[350]
study table is written two times
[353]
let's say one more time it is written S1C1
[357]
S2 C2
[359]
S1 C2
[360]
and 2016
[362]
2017
[364]
2017
[366]
and how do we
[368]
how we implement?
[370]
from study table
[372]
and we will make its alife, alife
[375]
alife means we generate a temporary name
[378]
so what the compiler will feel
[379]
What have we changed the
[380]
name of the study table?
[382]
T1 it is not permanent
[383]
we are not renaming it
[385]
but just creating a alife
[386]
study as T1,
[389]
study as T2
[392]
so what meaning it has
[394]
the name of this study table became T1
[397]
and the name of this study table
[399]
became T2
[400]
it is the same table
[402]
but there are two copies
[403]
means there is as it is one copy
[406]
name of 1 copy we temporarily kept T1
[408]
and we kept another
[409]
name temporarily as T2
[411]
so that we can write the query
[412]
because in query we have
[414]
to obviously do join
[415]
and join will be done after cross product
[418]
so for cross product I need two table
[420]
but we can write table
[421]
name only one time
[423]
can you write from study, study
[426]
if you write like this
[428]
write from study, study
[429]
it will show error
[430]
that study table is one
[433]
so therefore we have to
[434]
we are fooling the compiler, how?
[437]
the same we have made T1 and the same
[438]
table as T2, we
[440]
have made two alife
[441]
now by default you understand
[443]
whenever we write this after from
[445]
what does comma mean? Cross product
[447]
So what will happen in this two tables?
[449]
cross product, means
[450]
means this table and this table
[452]
there will be cross product in between
[454]
and whenever cross product happens
[455]
always
[456]
if there are m number of rows in one table
[459]
and in other n No. so total will be m x n
[462]
so here it is 3 and here 3 so total
[464]
will be 9
[465]
so here I am writing
[466]
what will be the output
[468]
when we do cross product
[470]
S1 C1
[472]
means 1st row will be multiplied with
[474]
first
[476]
see S1 C1
[477]
S2 C2
[479]
S1 C2
[481]
and we copy this as it is
[484]
then second row is S2 C2
[487]
this we will multiply with 3 rows
[490]
S2 C2
[491]
S1 C2
[493]
S2 C2
[495]
S2 C2
[497]
and the third row is
[499]
S1 C2
[501]
and this also will be multiplied with all 3
[503]
S1 C1
[505]
S2 C2
[507]
S1 C2
[509]
S1 C2
[511]
S1 C2
[516]
right, so this is the
[518]
value from table 1
[520]
and this is the value from table 2
[522]
nothing to do
[523]
take one row
[525]
took one row from here
[526]
and multiplied with these three
[528]
took 2nd row and multiplied with all three
[530]
picked 3rd row and
[531]
multiplied with all three
[532]
I didn't took the since column here
[537]
if you want to take you can take as it is
[539]
but we don't need this,
[542]
it is not asked here
[543]
so therefore I had not written
[545]
that column here
[546]
if you want you can write it
[548]
now this 1, 2, 3,
[551]
4, 5, 6,
[552]
7, 8, 9
[554]
so these 9 rows have come, after what
[556]
we have made two
[558]
alife from study table
[559]
and we did its cross product
[561]
but cross product
[563]
plus some condition
[564]
now we have to put some condition
[566]
what we have to find in the condition.
[568]
Find the information of that student
[571]
find the S_ID of the student
[573]
who is studying two minimum two courses
[576]
atleast two courses
[577]
means
[577]
student is one and
[580]
courses are two
[582]
student is one
[584]
course are two
[585]
means student ID should be same
[587]
and course ID should be different
[590]
so then only we can know
[591]
so what query we will write?
[593]
we will complete it select from T1, T2
[596]
where
[597]
this query is in the whole row
[599]
in complete one row
[600]
where
[601]
T1 because now study has become T1
[604]
T1.Sid
[607]
is equal to
[608]
T2.Sid
[611]
and
[613]
T1.Cid
[616]
take column name as it is otherwise
[618]
you will have confusion
[620]
take the name of column as it is
[623]
T1.Cid
[624]
is not equal to
[627]
T2.Cid
[631]
means
[632]
in both the tables student id
[634]
is same but
[635]
but course id should be different
[638]
this is not equal to, not equal to
[640]
if both ids same of students
[643]
means if we talk about same students
[645]
if we talk about one student
[646]
but corresponding courses
[648]
are different
[649]
so atleast my condition
[651]
number two will be fulfilled
[654]
so how we will do here,
[655]
so this condition we have to
[657]
these two conditions here
[659]
after cross product
[660]
we will apply,
[661]
because here cross product is done
[663]
This is the cross product
[665]
but after cross product condition
[667]
so this is cross product
[668]
plus some condition
[670]
so this is the condition
[671]
so now we will check this condition
[674]
T1.Sid
[675]
is equal to T2.Sid
[677]
true
[679]
T1.Cid is not
[681]
equal to T2.Cid
[682]
false
[683]
so true and
[684]
false
[685]
will be false
[686]
so this row will be neglected
[689]
this row will not be selected
[691]
next
[692]
S1.this
[695]
Sid is equal to Sid
[696]
are they both equal? No
[698]
so first condition became false
[700]
so obviously false and
[703]
so you do anything answer
[705]
will come false
[706]
so this row is also rejected
[709]
S1 = S1, yes
[711]
C1 is not equal to C2, Yes,
[715]
so this row will be selected
[717]
see S1 and S1 are equal?
[719]
Sid and Sid are equal
[721]
and C1 and C2 are not equal
[723]
and the same thing we are
[724]
talking, Cid is not equal to Cid
[726]
so this row will be selected
[728]
Sid is equal to Sid, No,
[731]
Sid is equal to Sid, Yes
[733]
but no Cid is not equal Cid,
[735]
Cid is equal to Cid, rejected
[738]
Sid is equal to Sid, No
[740]
Sid is equal to Sid, Yes
[742]
Course id is not equal,
[744]
yes, course id is also not equal
[746]
selected
[747]
Sid is equal to Sid
[749]
No,
[750]
Sid is equal to Sid, Yes
[753]
course id is not equal
[754]
No, course id's are equal
[756]
so this row will be rejected
[758]
so what I got finally in output
[761]
only these two rows came
[762]
I'll highlight them here
[765]
first this row came
[767]
second is this
[770]
these rows will be in the final output
[773]
means whose output are these
[776]
this one
[778]
this one
[779]
and this one
[780]
whose output came?
[782]
of this total,
[783]
from to
[785]
this whole condition
[787]
therefore I was given
[788]
these rows in output
[790]
now from these rows what
[792]
output I have to find?
[794]
it is student,
[795]
obviously student 1 has to come
[797]
because student 1 is that
[799]
who is studying two courses
[801]
so atleast 2nd condition is here
[803]
for 3, 4 we can write condition further
[806]
but atleast for 2
[809]
now my condition is being fulfilled
[811]
so what we have to find in final
[813]
Sid
[815]
in final we have to print Sid so
[817]
so I will print Sid,
[820]
but here keep in mind
[822]
if you write Sid alone
[824]
the compiler will give error
[826]
because compiler will be confused
[828]
because here there are two Sid
[830]
one Sid is in T1 and one Sid is in T2 also
[833]
so from where Sid has to be taken
[835]
so you mention there, let's say
[836]
T1.Sid
[839]
you can use T2 also no problem
[841]
T1.Sid
[843]
so final output will be
[844]
S1
[845]
so this is how
[847]
the self join actually works
[849]
means we multiply a table with itself
[852]
and how do we write? by making alife
[854]
and then applying conditions
[856]
So self join I have told you already
[859]
mostly you will get one example
[862]
but this example is best and
[864]
a new query
[865]
we have solved this query
[867]
with the help of self join
[869]
and if you liked my video
[871]
then please like this
[872]
share this and
[873]
please subscribe my channel
[875]
Thank You.
Most Recent Videos:
You can go back to the homepage right here: Homepage