Correlated subquery in sql Part 60 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to prism technologies I am
venket this is part 60 of sequel server
video series in this video we'll discuss
about correlated sub-query sin sequel
server in part 59 of this video series
we have discussed the basics of sequel
server we also discussed about how sub
queries can be replaced using joins if
you haven't watched that I would
strongly encourage you to do so before
continuing with the session in this
session we'll discuss about correlated
subqueries
correlated subqueries are very simple
and easy to understand
now if the sub query depends on the
outer query for its values then that sub
query is called as a correlated
sub-query let's look at an example let's
flip to sequence of a management studio
now before we look at a correlated
sub-query let's look at a non correlated
sub-query we'll be using the same tables
that we have used in the previous
session of this video series TBL
products and TBL product sales TBL
products table contains the product data
and TBL product sales table contains
sales related data so every time we sell
a product and entry will be made into
this table now let's say I want to
retrieve all the products that has not
been sold at least once how do I write
that query obviously whenever we make a
sale an entry is made into TBL product
sales table now if you look at the
sample data that we have we have sold
product with ID 2 and 3 meaning laptops
and desktops we haven't sold a TV at
least once so I want to retrieve the
products that we have not sold at least
once so how do I do that
so select I want the ID name and
description we'll we'll write those
columns later first let's read it from
clause so TBL products select from TBL
products now I have written the from
clause first because I will get
intellisense for the column name so it'd
be easier to write type so select ID
name and description okay so we want
these three columns from this table
where the ID column in TBL products
table not n in this table ok product ID
not in this table so what I will do
basically select product ID from TBL
products
and since there are duplicate product
IDs I'm going to use the distinct
keyword okay this is the same query that
we have discussed about in the previous
video session now look at this you know
how does this query gets executed now
the sub query gets executed fast so if
you look at this query this sub query
can be executed independently so when I
execute that query I get 2 & 3 now we
are saying select ID name description
from TBL products where ID in north n 2
comma 3 which should give us the product
with ID is equal to 1 okay
this is an example of a non correlated
sub-query in a non correlated sub-query
this sub query is not a dependent on
this outer query for its values the sub
query can be executed independently on
its own
ok once the scepter is executed the
results of this sub query is then
substituted within those parentheses for
use by the outer query ok so in a
non-correlated sub queries sub queries
usually gets executed only once ok now
let's look at an example of a non
correlated sub-query again we'll be
using the same examples that we have
discussed about in the previous session
let's say for example I want to find out
you know all the products that we have
sold and the total quantity ok so
basically we have three products and I
want to find out all the three product
names and the total quantity ttle you
know quantity that we have sold so how
do we do that so basically I want the
name and then the total quantity now to
retrieve the total quantity I want to
use let's say sub query ok so I'm going
to open and close parentheses select now
we want you know the total quantity that
we have sold so I'm going to use some
aggregate function and some off I want
to aggregate this quantity sold column
so quantity soul from TBL product sales
table ok where product ID is equal to
now for now let's substitute question
mark there
from TBL products table so various name
column coming from the name column is
coming from TBL products table and I
want to sum up this quantity sold for
each product so the product ID is going
to come from TBL products table okay so
every rule that we select from the outer
query so first TV is selected let's say
for example then what is the sub-query
look at the sub-query that we have here
select sum of quantities sold some of
quantity is sold from TBL products where
product ID is equal to look at this we
are looking up the product ID in TBL
products table so every time we select a
row from the outer query now this up
query gets executed low so when I select
this row fast it then executes this sub
query when that row is being selected
okay where product ID is equal to T
build products dot ID here the ID is one
it looks up that here we don't have an
ID so some four TVs will be null and
then when I take when the query executes
the second row what happens it comes to
the sub query looks up in TBL product
sales table is there an entry is there a
product with ID - yes there is it tries
to sum up that there is only one row so
we only get seven and then finally when
it comes to three okay product ID is
three so there are three rows so it's
going to add five four and nine so 18
will be the sum so if you look at this
now can I execute this sub query
independently out of you know
independently of the outer query no I
can't look at this when I try to execute
that I get an error because the
multi-part identifier TBL products dot
ID cannot be bound now a correlated
sub-query will get executed for every
row you know for the outer query in the
outer query for every row
you know this sub query gets executed
okay so look at this when I execute this
query together I get TV laptop and
desktop and I get their sum as well okay
now I don't have a column name here but
let's go ahead and give a meaningful
column name using
oh yes so let's say quantities sold now
we get a column name okay so that's a
correlated sub-query because this sub
query is dependent on this outer query
for its value for the value for this
column product ID okay so that's a
correlated sub-query so if the sub query
depends on the outer query for its
values then the sub query is called as
correlated sub-query in a weight loss
look at this in the weight loss of the
sub query this product ID column gets
its value from TBL products table and
TBL products table is not present within
the sub query okay it's present only
within the outer query so this sub query
is dependent on the outer query for its
value in this case for product ID so
here the sub query is dependent on the
outer query for its value hence the sub
queries a correlated sub-query
correlated subqueries get executed once
for every row that is selected by the
outer query correlated sub-query cannot
be executed independently of of the
outer query but whereas a non correlated
sub-query can be executed independently
of the outer query on this slide you can
find resources for asp.net c-sharp and
sequel server interview questions that's
it for today thank you for listening
have a great day