In this video will
talk about what is a subquery
what are co-related
queries and what’s a difference between
subquery and co
related queries so
first let start the subquery
subquery also termed as
nested queries subquery
is nothing but it’s a query inside a query
many times you know you would like to
you have series of SQL chained
you know where output of one
query is sent as the input to the other
query for filtering and manipulation
for example you can see in the figure
you know we have two tables one table has the
employee salary and the
second table has phone numbers now
let say that we want to find phone numbers of
employees you know who have
salaries you know which are greater than
150 now this can be achieved by
using subquery so what we can do is
we have two queries one is the
inner query which will go and fetch
you know records you know which are greater than
150 and the output
of this will be fielded to outer
query who will go fetch the phone numbers
so first thing is first will go and
select so let’s go and
query the
EmpSal table so will say okay select
id from EmpSal
where
salary is greater
than 150 so
this will be our inner query
now the output of this inner query
will be fetch to a query which is outer query
which will actually go and extract phone numbers
so what I will do is I will say okay
this is my inner query
okay
and
the output of this
inner query will now go will be
now sent to outer query
so this a outer query selects
* from Emp details
details
so
expand this bit
where Id
in okay
just let me go and indent this
so that everybody see properly
also there is a red sign here
so this is actually idfk
okay so id
fk right so now you can
see that you know what will happen is first
the inner query will go will evaluate
himself he will extracts records you know
whose salary is greater than 150
those id is will then we freed
to this outer query who
will say okay ids you know
which are been supplied by this inner query
you know I will go and extract
Emp details out of it okay
so let me just go and comment this
so you can see now this is the outery
query and this is a inner query
so if I go and execute this
you can see now he
showing me phone numbers
of phone numbers as well as address
you know of employees whose salaries are
greater than 150 so if you just go
and recheck this if this proper or not
if I go and execute this
you can see that we have two employees here who
salaries are greater than 150 one is
xyz and the other one is sreeni
so three and four so if I go
and fire this SQL again
you can see that he has
selected those third and the
fourth record and he has taken the phone
numbers and address out of it
so this is what a subqueries
subquery is nothing but a it’s a query
inside query we have a inner query
so first the inner query gets
evaluated the data
from the inner queries then supplyied to the outer
query and the complete
data is then display or the complete output
is then displayed this was
about subquery now there is one more thing
which we discuss which we said will discuss in this video
is co-related queries
so let me just go and
delete all this now let’s take a
situation
where you want to go and
find the second
highest from this
table EmpSal
so how will go about doing that
now this can be achieved by using co
related query so what I will do is
first let me go and write down the co-related
query here and then I will explain how
exactly it operate so I want to go and
paste this co-related query here
and let me first explain me this SQL
and then we will go about
execute in this now
the first thing which will notices is
that there is a inner query
and that is a outer
query
so
you know you can think about
that you know co-related queries are a
type of variation of subquery
or nested queries so one
similarity between both them you know both have
inner query and both have a outer query
but you know there is a big
difference in the way the data is pass between
inner query and outer query let me explain that
so you can see here that
you know your inner query is also
referencing the outer query table
so you can see that I have the
EmpSal table right I have defined two
aliases here you can see I am saying EmpSal
e1 which is reference
in the outer query and then we have Emp
Sal e2 which is again reference
in the inner query and you can
see here that I am saying that e2
.salary should be greater that e1.
salary in other words you know my inner
query is reference in the outer query
if you remember in subquery right
they will no references so
the you can think that in
subquery right the inner
query was completely independent and
standalone but here you can see that
there is a relation between the inner query and outer
query actually the inner queries the
referencing the outer query table
that’s why the name is co-related queries
so here's how thing will work
so what happens here is
the outer query
record is pass to the inner query
the inner query you know
evaluates that record and passes the data
again back to the outer query so
here’s how it works so first thing
it goes record by record
so first shiv will be taken
okay from the outer query the shiv record will be
pass to the inner query in the
inner query we say that how many salaries
are greater than and equal to shiv
that is hundred so we have
one two
three okay
and also equal to hundred so
again you know we have even shiv
included in that so four okay
so we say okay we want to find this second highest
here now we are trying to find the second highest
okay second
highest
so
when the shiv record is pass to the inner query
he will say okay it will actually
give out the count of four and this is not equal to
two okay so that record will be
readdicted then it will take the
second one raju now
raju 150 again pass to the inner
query the output will be
one two three again it is not the
second highest after that
xyz will be taken again
it will be pass to the inner query now will say okay
how many records are greater than
200 or equal to 200 so we have
one 300 and he himself
so that is equal to two and this is the
second highest so in other
words every record
from the outer query is pass
to the inner query the inner
query then again evaluates it
and then again it is pass to the
outer query so if I go and
executes this now you
you can see that he displaying me xyz which
is actually the second highest
salary so now let’s summarise
you know what exactly is a
subquery and what exactly is a co-related query
and also let’s try to point out the important
differences between them in
subquery right the inner query is independent
it is self content
and it’s gets evaluated first and
then the complete record is passed you know to the
outer query and the data is displayed
after that while in co-related query
is right the inner query he needs values from the
outer query and then
you know these results are pass back to the
outer query so you can
think about the subquery it’s more of unidirectional
thing where the data just most the inner query
to the outer query while in co
related queries right the data
moves to and fro and finally the
final output displayed after it matches the
where clause so
I hope that you enjoy this video in this video we will
trying understand what exactly is a
suquery what exactly is co-related query
and what are the importance differences between them
thank you so much now whatever
video you have seen right is
just gleams of what we have done
so in case you are interested in our video
package you can go to
our site that is www.questpon.com
you can call on number and
you can ask
the complete DVD package what we have
so in this DVD package what we have done is basically we have
covered almost everything
what a .NET developer wants
so right from basics of ASP
.NET objected programming SQL Server to new
technologies like WCF
Silverlight Linq Azure entity framework
we also have
UML architecture estimation
project management that it is complete
invoicing package invoicing project end to end which is covered
so that you can better feel of
how to actually create projects in a
systematic manner we have
cover server products you know both for sharepoint
2007 as well as 2010
we have lot of best practices videos on
SQL Server etc so this complete
package you know you can get from www
questpond.com if you are interested
and you can call on this number and
you can ask for the rates it’s
very decent rate what we have
in the same way
you know as
compare to the videos we also have one more
products with us that is our interview question
books so we have different kinds of interview
questions books you know right from .NET
interview questions to SQL Servers interview questions
sharepoint interview questions
BizTalk interview questions etc
so in case you are interested in the books part
you can call on these numbers
as per your location so
you can see this numbers on bored at this moment
so I hope that
you keep enjoying the videos you keep
seeing our
site and I hope that you gain more
knowledge thank you very much