馃攳
Lec-37: Natural Join operation with Example | Database Management System - YouTube
Channel: Gate Smashers
[0]
Hello friends, welcome to Gate Smashers
[3]
the topic is natural join in DBMS.
[5]
In this video we are going
[7]
talk about Natural Join.
[9]
before natural join let me tell you
[10]
that I have also made the last video.
[13]
In which I talked Types of Join.
[15]
and I did a scenario
[17]
discuss with the table.
[18]
that, where we use join
[21]
Meaning in which scenario
[23]
do we use joints?
[24]
So I am expecting that
[26]
you must watch my video.
[27]
so that you can know
[29]
how we use joins?
[32]
in which scenario do we use joins
[33]
and what are their types
[35]
so in those types
[37]
one type is natural joins
[39]
natural joins
[41]
and this natural joins is
[43]
a very important topic
[45]
because out of all type of joins
[47]
the most important is natural join
[49]
because a large No. of questions
[52]
come on natural joins
[54]
and second thing I can say in short
[56]
one who does not understand
[57]
Natural Join,
[58]
cannot understand about Joins.
[61]
So the first priority is to
[63]
understand about Natural Join.
[65]
Now in this video, we are
[67]
going to discuss Natural Join.
[69]
will discuss its syntax,
[71]
with the example.
[72]
Means with real life
[73]
example, with the
[74]
help of tables we will
[76]
solve this question.
[78]
By which you will get
[79]
complete knowledge
[80]
about the concept of Natural join
[83]
let me tell here first.
[85]
before natural join,
[87]
if I only talk about join
[89]
join always remember you.
[91]
Join is what?
[94]
It is cross product plus some condition.
[101]
any condition can happen.
[103]
any condition is there,
[104]
but you have to remember this always.
[107]
Join is always cross
[108]
product plus some condition
[110]
meaning whenever you come to Join,
[113]
so by default assuming that
[115]
there will be a cross product.
[117]
and how is the cross product, what
[119]
happens, we will
[121]
discuss with the example
[122]
First let me tell you the scenario.
[125]
Here I have taken two tables,
[127]
one Employee, one Department.
[129]
In the Employee table, Employee
[131]
Number, Employee Name, Address
[133]
Department Number,
[135]
Department Name, Emp no.
[138]
and here some data I have
[139]
inserted three or four rows here.
[142]
Now if I want to ask a simple question
[144]
here. There is a simple question.
[146]
that if i want to
[149]
print the name of employee
[150]
To display the
[152]
name of the employee or print
[154]
who let's suppose belongs from Delhi
[157]
From Delhi
[158]
so how do we write?
[160]
First of all whenever
[161]
you come across a query
[162]
You will get two types of questions.
[164]
either you will come to query
[166]
based on that you have
[167]
to find the result
[168]
Or you will have been
[169]
given the result, and
[170]
based on that you have
[172]
to write the query.
[173]
so you don't need to write query
[174]
Queries will have been given in objective
[177]
type questions. you can use them.
[179]
But objective type
[180]
question is for later.
[181]
First I also want to learn
[183]
how to frame queries.
[186]
So we're going to do the same thing over
[187]
here. That if you are getting question
[189]
You have to print the employee name
[192]
who belongs to Delhi.
[194]
so whenever you get query
[195]
first of all you have to check
[198]
that from which table we
[199]
can find answer to the query
[201]
means you have to find first
[203]
the table name
[207]
find the name of the table
[208]
from where you will get the output
[211]
so if I have to print
[212]
name of the employee
[213]
who belongs to Delhi
[215]
so Delhi means
[216]
address of the employee is Delhi
[218]
and their name means employee name
[220]
means only with the
[221]
help of employee table
[223]
I will get the output
[224]
and how do we write this
[226]
simply by using the select command
[229]
what do we write with select
[231]
that which I want to get
[233]
printed in output, display
[234]
so here what do I want to be printed?
[236]
employee's name
[238]
because what is my question?
[239]
To print employee's name
[240]
who belongs to Delhi
[242]
so select employee name
[244]
from
[245]
employee table
[246]
table name, where
[250]
address
[252]
is equal to
[254]
Delhi
[256]
where address = Delhi
[258]
so its a simple command
[259]
the purpose to write
[261]
this command is
[262]
to tell you whenever a question comes
[265]
first of all find the table
[267]
from where you will get the output
[269]
but
[270]
this is a very simple question
[272]
now we will talk about this question
[274]
this is a important question
[276]
find the employee name
[278]
find the employee name
[282]
who is working in a department
[284]
what you have to do simply
[287]
you have to find the names of employee
[289]
who are working in any department
[291]
and this in the same scenario
[293]
means you are given two tables
[296]
and on the basis of those tables
[298]
you have to
[299]
write its output or queries
[302]
so from here I
[305]
rub this
[306]
as there is no further
[308]
use of this query
[310]
Find the employee name who
[312]
is working in a department
[313]
so what should come in output
[314]
employee's name
[316]
who is working in any department
[319]
now employee's name
[320]
first of all you find out that
[321]
what all tables are required?
[323]
employee table is needed
[325]
obviously because in which
[326]
table employee's name is there
[328]
in employee, but
[330]
that employee is working
[332]
further in any department or not
[334]
who will tell that
[335]
department table
[337]
how? see here
[338]
employee 1
[340]
is working in
[342]
department 1
[344]
what this 1 shows here
[346]
this 1 tells that employee 1 is working
[348]
in department 1
[350]
employee 2 in department 2
[352]
employee 4 in department 3
[354]
and corresponding to them
[356]
what are the three names
[357]
corresponding to 3 employees
[358]
Ram
[359]
Varun & Amrit
[361]
means Ravi is a employee
[363]
who is not allocated
[364]
any department till now
[366]
so here we got the output
[368]
by looking at the tables
[369]
that what output I should get finally
[371]
Ram, Varun and
[372]
Amrit
[373]
but
[374]
how we will write this query?
[376]
how we have to solve this query
[378]
with the help of natural join
[380]
and by default only natural
[381]
join can be applied here
[382]
although there are so many
[383]
joins, we can also do with sub query
[385]
but we will do this with natural joins
[387]
First of all what is natural join?
[390]
Whenever between two tables you
[392]
have to equal some common attributes
[394]
that is, what is the common attribute
[396]
between these two tables
[398]
employee number
[399]
look both tables have
[401]
the same common attribute
[402]
employee number
[404]
their name should be the same,
[406]
employee number there
[407]
also employee number
[409]
Now the values of these two employee
[412]
numbers have to be kept equal,
[413]
meaning they should be equal
[415]
to whose corresponding equal is coming
[417]
you have to print the
[419]
value of those in final
[420]
So whenever you want to equalize
[423]
the value of common attributes
[426]
so in that case we always
[428]
use natural join
[430]
So we start by writing its query
[433]
select
[435]
because in the final which
[436]
output I want to show
[437]
I write that select,
[439]
what is the output to be shown?
[440]
Find the Employee Name
[441]
So where is the Employee name
[443]
here it is, the Employee name
[446]
or Employee name
[447]
Select Employee Name
[449]
From
[453]
Join I have already told
[455]
you what is joining?
[456]
Cross product plus some condition
[458]
So first here it will
[459]
be cross product, how?
[460]
from
[461]
name of the tables
[462]
which tables do you need?
[464]
of Employee
[465]
and of department
[466]
you have requirement
[467]
of both the tables
[469]
so what you have to write
[470]
select employee name
[471]
from employee, department.
[473]
Now first of all come to this point
[475]
what is the meaning of this
[477]
employee, department?
[478]
employee, department means
[481]
cross product
[482]
means you by default
[483]
cross multiplied employee and department,
[487]
cross product their rows
[490]
Obviously I already told
[492]
that join is cross product
[493]
plus some condition,
[494]
whenever you apply condition
[496]
there will always be cross product first,
[498]
without that join is not possible
[500]
So we did cross product
[502]
of employee and department
[504]
so what was the output?
[506]
so to save time
[507]
I have already printed their output
[510]
Let me tell you
[511]
what to do, what does
[512]
cross product mean?
[514]
Pick one row and multiply it with
[516]
all the rows of second table,
[518]
that is, the first row with these three,
[520]
the second row with these three
[521]
the third with the three
[522]
the fourth with the three,
[524]
means if a table has
[526]
m number of rows
[528]
and other has n number of rows
[529]
total number of rows will be
[531]
m x n
[532]
in the cross product
[533]
that I have already written here
[535]
so here I have employee
[536]
No. employee name,
[538]
department No.
[539]
these two I have
[541]
picked up from the employee table
[543]
and both of these I
[545]
picked up from the department table
[547]
You can also write all the
[548]
attributes, there is no problem
[551]
but because of space I have
[552]
picked up two attributes
[553]
you can write 3 attributes also
[555]
you can write 6 attributes also
[557]
that does not matter
[558]
what matters? No. of rows.
[561]
What will be the number of rows?
[562]
4 x 3
[564]
12, so I
[566]
have written proper
[568]
12 number of the rows there
[570]
now,
[571]
What is a natural join?
[573]
I have already told you
[574]
the common attributes
[576]
between the two tables
[577]
that you have to keep equal
[578]
have to keep equal
[580]
make equal
[580]
so I,
[582]
of both the tables
[585]
select employee name from
[587]
employee, department
[588]
where
[590]
employee.
[592]
employee No.
[596]
this one
[597]
employee.employee No.
[599]
What is the meaning of
[601]
employee.employee No.?
[602]
Employee number inside employee table
[605]
equals to
[607]
department table.employee number
[612]
this is the condition
[613]
look what you did
[615]
employee number inside employee table
[618]
kept equal to
[620]
employee number inside department table
[622]
and this I am telling again and again
[624]
whatever common attributes are there
[626]
you have to keep it equal
[628]
So I did the same, which is
[630]
the common attribute in both
[631]
Employee number
[632]
and must be common
[634]
At least one attribute should be common
[636]
if it is not common
[637]
then you cannot join two tables
[640]
because what is the need to join,
[642]
if one table is giving
[643]
me some other information
[644]
the other is giving some other information,
[646]
both of them have nothing in common,
[648]
then why should I
[649]
join these two tables?
[650]
So I will join both of them only
[651]
when there is something in common
[653]
and we have to keep
[655]
that common equal
[657]
just put equal, in the
[658]
middle what I have put
[659]
I've put equal
[660]
This condition are also
[662]
the condition of Equi join
[664]
we write Equi join like this
[665]
but we don't have to put equal there
[667]
but we are talking about
[669]
natural join right now
[670]
so I will tell about that only
[671]
if I tell this query
[673]
when you did from emp, dept
[676]
what were the no. of rows?
[677]
12
[679]
now what will happen
[680]
over these 12 rows?
[681]
this condition will apply
[682]
where emp. emp No. should be
[685]
equal to dept.emp No.
[688]
So you first come to the
[690]
emp. emp No.
[692]
is equal to the deptt. emp No.
[694]
are both equal
[696]
yes both are equal
[698]
If both are equal then
[699]
we will select this row.
[702]
Next
[703]
emp.emp No is equal to
[705]
deptt. emp No.
[707]
are both equal
[708]
No, so we will reject this row
[711]
you just, 1 is equal to 4
[713]
No,
[714]
2 is equal to 1, No
[716]
2 is equal to 2, Yes
[719]
2 is equal to 4, No
[721]
3 is equal to 1, No
[723]
3 is equal to 2, No
[725]
You should check for every row
[727]
because whenever this condition comes
[729]
it will be for every row,
[730]
for the whole table,
[732]
this condition will be for all rows of
[733]
table with cross product
[735]
3 is equal to 4, No.
[737]
4 is equal to 1, No
[739]
4 is equal to 2, No
[741]
4 is equal to 4, Yes
[743]
So how many rows
[744]
got selected 1, 2, 3
[746]
the rest of the rows
[748]
which got rejected
[749]
we will neglect them from here
[751]
let's say this row
[753]
is removed from here
[756]
the rows which are deleted
[759]
we delete them from here.
[764]
So finally what output do I have?
[766]
this
[767]
3 rows are here
[768]
you can
[770]
I write it here in total,
[772]
2, Varun
[775]
D2, 2
[777]
4
[778]
Amrit
[780]
D3, 4
[782]
so finally these 3 rows are left
[785]
the rest of the rows are rejected
[787]
so when these three rows are left finally
[789]
means from here
[792]
upto here, what will be
[794]
the output of all this,
[795]
this
[797]
what is the output of this whole?
[798]
this
[799]
Now from the output of this whole
[801]
what do you want to print?
[803]
employee name
[805]
so pick up employee name
[806]
Ram, Varun, Amrit
[809]
So what is the output
[810]
Ram, Varun
[812]
and Amrit
[814]
and if you look carefully
[816]
this output should only come
[817]
because Ram whose employee No. is 1
[820]
he is also working in some department,
[822]
Varun whose employee number is two
[824]
is also working in one department
[825]
Amrit whose employee number is four,
[827]
he is also working
[828]
in any department,
[829]
who is rejected?
[830]
C, Ravi
[832]
because Ravi has not been
[834]
allocated any department yet
[836]
so I discussed a very simple example
[839]
but this example will help you a lot.
[842]
in any situation
[847]
common attribute means
[848]
which is same in both
[849]
that attribute are to be kept same
[851]
that we call Natural Join
[853]
Now the last point I tell you here
[856]
now this query, how
[858]
do we write actually
[860]
in this way, select
[863]
employee name
[866]
from
[868]
from
[869]
first table name, employee
[872]
straight
[873]
natural
[875]
join
[877]
department
[878]
that's it
[880]
this is the actual way of
[882]
writing
[883]
but if I had written this before
[885]
then you would not have been
[886]
clear what natural join is doing
[888]
what natural join is doing
[889]
doing this
[890]
by default if you are written
[892]
from employee
[894]
table 1 natural join table 2
[896]
you understand this
[897]
but, but
[899]
the common attribute
[900]
inside these two tables
[902]
their name should be same
[903]
it should not be like
[904]
that here it is employee No.
[906]
it is E No.
[907]
there is some employee number
[909]
or the column name is different,
[911]
column name should be same
[913]
then only natural join can be applied
[914]
otherwise you will have to write like this
[917]
If you want to write a natural join
[918]
then you can write directly, but
[920]
if you look at both of these,
[922]
then what are the two
[923]
what are the two queries
[925]
equivalent, means
[927]
if I open this query and explain
[929]
then I will do in this way
[930]
I have explained it first
[932]
so that you can get to know
[933]
how Natural Join works
[935]
So natural join can be applied
[937]
in two common, in two tables there should
[939]
be common attributes,
[940]
their name should be same
[941]
whenever we keep the
[942]
common attribute equal
[943]
keep them equal
[945]
it is called natural join.
[948]
this is all about the
[950]
what is the function of the natural join
[951]
and how we use in a
[953]
real life scenario
[955]
this was in the term of sequel
[956]
when we write this syntax in the
[958]
term of structure query language
[960]
in this way
[962]
Thank you
[963]
and if you like this video
[964]
please like this
[966]
please share this
[967]
and please subscribe my channel
[969]
Thank you.
Most Recent Videos:
You can go back to the homepage right here: Homepage