Subqueries in sql Part 59 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to priam technologies I
invent it this is part 59 of sequel
server video series in this video we'll
discuss about sub queries in sequel
server let's understand sub queries with
an example let's flip to sequel server
management studio first let's look at
the tables that we'll be using to
understand sub queries TBL products
table contains product related
information like the ID of the product
which is also going to act as the
primary key for this table the name of
the product and its description TBL
product sales table contain product
sales data every time they sell a
product and entry will be made into this
table this table contain information
like ID which is going to act as the
primary key for this table Product ID
the ID of the product that we sold at
what unit price did we sell and how much
quantity did we sell this insert script
inserts some sample data into these two
tables I'll have this entire script on
my blog so you can copy it from there
and practice sub queries if required
let's examine the sample data that we
already have in this tables so if you
look at the data in TBL products table
there are three products with ID 1 2 3
TV laptops and desktops and TBL product
sales table contains sales data pay
attention to this Product ID column look
at this we have sold product with ID is
equal to 3 3 times that is we sold
desktops three times and laptops only
once and if you look at TVs we haven't
sold it at least once
ok now let's say your manager is asking
you to write a query that returns the ID
name and a description of all the
products that we haven't sold at least
once now how can we tell if we have went
sold at least once a product if you look
at the product sales table if the
product ID is not present there then we
can say for sure
we haven't sold that product at least
once ok
so if we want to retrieve the ID name
and description you know of all the
products that are not sold at least once
we want to retrieve all the products
from this table where that ID does not
exist in this column and product ID
column in TBL products sales table it's
as simple as that but how do we put that
in a query so first
that's right the select what are the
columns we want we want ID name and
description but I'm going to do that
later
first I'm gonna specify the from clause
so TBL products and then I'll specify
the columns in the Select list because
I'll get intelligence if I specify the
from clause and we want these three
columns ID name and description look at
description appears as a keyword so I'm
gonna enclose that within a square block
within square brackets okay so we want
these three columns where the ID not in
so this ID that I am going to select the
products that I'm going to select the ID
should not be within this column in this
TBL products sales table okay so how do
i specify that I can simply say select
product ID from TBL products sales table
okay so we are selecting this product ID
from tbls product sales table now if I
execute this query look at that look at
what I get I get 3 2 3 3 so I get all
the product IDs so instead of that I can
simply say this tent so that will give
me the distinct products that we have
sold ok so now we are saying select all
the products where the ID not in 2 comma
3 which is going to give us the row with
ID is equal to 1 that's TV which we
haven't sold at least once look at that
how easy it was to use this sub query
here so the query that is present inside
this parentheses is called a sub query
sub queries are always enclosed within
parentheses and look at this sub query
it returns only one column ok and this
column is now used in your bare claws
and we are using it in this Norton ok
and sub queries are also called as inner
queries and the query this query that
contains this sub query is called as an
outer query ok now many times these sub
queries can actually be very easily
replaced with the joints we have
discussed about joins in the previous
sessions of the sequence of a video
series if you have
to watch them I would strongly encourage
you to do so we discussed about all the
different types of joints that are
available in sequel server we even
discussed about advanced joints so
please watch joints first so let's
rewrite this query you know which gives
us exactly the same output using joints
okay
so how do we do that let me copy this
instead of retyping so select all those
columns from TBL products I'm gonna do
an inner joint so when we do an inner
join what happens we get all the
matching rules between both the tables
so I'm gonna join TBL products with pbl
products sales table and how do we join
them in TBL products table we have the
ID column which matches with product ID
in TBL product sales table okay now if I
execute this query we get all the
matching rows between both the tables
I'm actually going to get an error
that's because in both the tables we
have this ID column in TBL products and
TBL products able sales tables that's
why it's saying ambiguous column name so
we have to tell it from which column
from which table we wanted so I'm going
to specify the table prefix so that the
query is not confused okay
so now if I execute this query we get
all the matching rows between both the
tables that's not what we want we want
to use a left to join here okay when I
use a left to join I get all the
matching rows between both the tables
plus the non matching row which is TVs
from the left table but that's not what
we want what we want is only the non
matching rows from the left table okay
so we don't want the matching rows plus
non matching rows we I mean we only want
the non matching rows how do we specify
that using a where clause in TBL
products sales that product ID is now
that means it's gonna return as the TV
row so we can easily you know replace
that with a join as well so we are using
a left outer join here with you know the
square class which is giving us the list
of all products that we haven't sold at
least once okay so in this example we
have seen how to use a sub-query
within the where clause and we have also
seen how to replace that sub query using
a join okay both of these queries give
us exactly the same output now let's
look at another example of using sub
queries so write a query to retrieve the
name and total quantity sold now let's
say I want the name of the product so if
you look at these two tables that we
have I want the name of the product and
the total quantity of each product that
we have sold so if you look at the
quantity sold how many laptops did we
sell seven laptops and how many desktops
did we sell nine plus four thirteen plus
five eighteen desktops we have sold ok
TVs we haven't sold any so I want to
retrieve the name of the products in
total quantity we sold and we want to do
that using a sub-query let's see how to
do that in this example we have seen how
to use a sub-query in the where clause
in this example we will see how to use a
sub-query in the Select list ok so what
do we want we want the name of the
product so first let me write from TBL
products table what we want we want the
name of the product and then what else
we want we want the total quantity that
we have sold ok so I'm going to use the
sum aggregate function okay but then I'm
going to use a sub-query so anytime we
want to use a SERP query the first thing
we do is we open up and close a
parentheses and within that we write the
sub-query so select what do I want I
want some off
let's read get rid of that sum sum of
what do I want
I want quantity sold so first let me
write the from clause so that I'll get
some intelligence there so quantity soul
so sum of quantity sold from TBL
products and then we have to use a ver
clause where product ID because we don't
want the sum of all
the products we want to specify for
which product you want okay for now I'm
going to use the question mark will fill
this what which is the product ID for
which we want the sum of the quantity
sold in just a bit okay and then give
this one an aleeah's so as qdy sold
quantity sold from TBL products okay now
what I'm gonna do here look at this I
have this TBL products from this table I
want the name and from this table I want
the total quantity sold okay so but then
when I select a product from this table
for example when I select TV I want to
immediately come to this table for all
those product which has got this TV you
know which is nothing but the ID ID is
one so for all the products you know for
all the products that has ID one some
the quantity sold and then retrieved
that I have to specify that in this you
know where clause so how do I do that
simply specify TBL products dot ID so
anytime you select a product you know
for example if I select this TV it's ID
is one it looks up that here and then if
there is a match it is going to add the
quantity sold and return status quantity
sold okay
so let's execute this query and see what
we get look at that seven laptops 18
desktops TVs we haven't sold any and
then if you want to order that by
product name we can simply say order by
name of the product okay so here we are
using a sub-query inside a select
statement in a select list okay
let's see how to replace this again
using the join again that's very easy to
do okay so we have to know how to use
grew by and joins here so I'm gonna copy
this query just to save some time in
typing so select what do we want we want
the name of the product and we want the
total quantity so sum of quantity
as total quantity or quantity soul from
TBL products and I want to do a left
join with TBL product sales table on
this you know the join condition is the
same and the important thing is we have
to use group by because we want to group
by name and then sum their quantities so
group by name okay so we discussed about
joins in the in the previous sessions of
this video series so I'm not going to go
into the details of joins now here so
all I want to show here is you know we
can replay sub queries with joins okay
look at that
I get the same output okay desktops 18
laptop 7 and TVs now similarly the same
output here 18 7 now so most of the time
the sub queries can be very easily
replaced with joins okay so today we
have seen how to use a sub-query in the
where clause and how to use a sub-query
in the Select clause sub queries are
always so what are sub queries from
these examples it should be very clear
that a sub query is simply a select
statement that returns a single value
and that can be nested inside a select
update insert or delete statement we
have seen how to nest a sub query inside
a select list and inside the where
clause similarly we can you know have
that in a delete or in an update
statement or even in an insert statement
it is also possible to nest sub query
inside another sub query so here we have
a sub query here and this is the sub
query okay now you have a where clause
here so here you can have another sub
query if you wish to okay
so a sub query can contain another sub
query that sub query can contain another
sub query so this way we can nest sub
queries up to 32 levels according to
MSDN and sub queries are always enclosed
in parenthesis and we have seen that and
are also called as inner queries and the
query that contained the sub query is
called as outer query the columns from a
table that is present only inside a sub
query cannot be
used in the select list of the outer
query this is important again so we have
the sub query here of for example if you
look at this one so this is sub query
now if you look at this TBL products
sales table this up this table appears
only within this sub query now if you
look at the product sales table it has
got other columns as well for example
unit price that comes from TBL product
sales table but this TBL products table
or sales table is present only inside
this sub query in within the opening and
closing of that parenthesis now can I
use the columns from this table in the
Select list of the outer query can I use
or can I try to retrieve unit price
column from this TBL product sales table
that appears only in a sub query no you
cannot if you try to do that you get an
error
okay invalid column name unit price so
the columns from a table that is present
only inside a sub query cannot be used
in the Select list of the auto query now
we have seen that we can achieve the
same result either by using a sub query
or a join okay so when we have these
choices then what to choose for
performance sub queries or joins okay
we'll talk about the performance aspect
in the next video session on this slide
you can find resources for HP dotnet
c-sharp and sequel server interview
questions that's it for today thank you
for listening
have a great day