YT YouTube2Text

YouTube2Text → Transcripts → Subqueries in sql Part 59

Subqueries in sql Part 59 — Transcript

by kudvenkat · 2,529 words · 347 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I am
0:02wenet this is part 59 of SQL Server
0:04video series in this video we'll discuss
0:07about subqueries in SQL Server let's
0:09understand subqueries with an example
0:11let's flip to SQL Server management
0:12Studio first let's look at the tables
0:15that we'll be using to understand
0:17subqueries TBL products table contains
0:19product related information like the ID
0:22of the product which is also going to
0:23act as the primary key for this table
0:26the name of the product and its
0:28description TBL products sales table
0:31contain product sales data every time we
0:33sell a product an entry will be made
0:35into this table this table contain
0:38information like ID which is going to
0:40act as the primary key for this table
0:42product ID the ID of the product that we
0:44sold at what unit price did we sell and
0:48how much quantity did we sell this
0:50insert script inserts some sample data
0:52into these two tables I'll have this
0:54entire script on my blog so you can copy
0:56it from there and practice subqueries if
0:59required let's examine the sample data
1:01that we already have in the tables so if
1:04you look at the data in TBL products
1:05table there are three products with ID
1:08one to 3 TV laptops and desktops and TBL
1:11product sales table contains sales data
1:14pay attention to this product ID column
1:16look at this we have sold product with
1:18ID is equal to three three times that is
1:20we sold desktops three times and laptops
1:23only once and if you look at TVs we
1:26haven't sold it at least once okay now
1:30let's say your manager is asking you to
1:32write a query that Returns the ID name
1:35and description of all the products that
1:38we haven't sold at least once now how
1:41can we tell if we haven't sold at least
1:45once a product if you look at the
1:47product sales table if the product ID is
1:49not present there then we can say for
1:51sure we haven't sold that product at
1:53least once okay so if we want to
1:56retrieve the ID name and description um
1:58you know of all the product that are not
2:00sold at least once we want to retrieve
2:02all the products from this table where
2:04that ID does not exist in this column in
2:07product ID column in TBL product sales
2:09table it's as simple as that but how do
2:11we put that in a query so first let's
2:13write the select what are the columns we
2:16want we want ID name and description but
2:18I'm going to do that later first I'm
2:20going to specify the from Clause so TBL
2:23products and then I'll specify the
2:26columns in the select list because I'll
2:28get intellisense if I I specify the from
2:31clause and we want these three columns
2:33ID name and description look at
2:35description appears as a keyword so I'm
2:37going to enclose that within a square
2:39within square
2:40brackets okay so we want these three
2:42columns
2:44where the
2:47ID not
2:49in so this ID that I'm going to select
2:53the product that I'm going to select the
2:55ID should not be within this column in
2:59this TBL product product sales table
3:01okay so how do I specify that I can
3:03simply say select product ID from TBL
3:08product sales
3:10table okay so we are selecting this
3:12product ID from TBL product sales table
3:15now if I execute this query look at that
3:17look at what I get I get 3 2 3 3 so I
3:20get all the product IDs so instead of
3:23that I can simply say distinct so that
3:26will give me the distinct products that
3:28we have sold
3:30okay so now we are saying select all the
3:33products where the ID not in 2 comma 3
3:37which is going to give us uh the row
3:39with ID is equal to one that's TV which
3:41we haven't sold at least one so look at
3:43that how easy it it was to use this
3:46subquery here so the query that is
3:49present inside this parenthesis is
3:51called the subquery subqueries are
3:53always enclosed within parenthesis and
3:56look at this subquery it returns only
3:58one column
4:00okay and this column is now used in your
4:02wear
4:03clause and we are using it in this not
4:0610 okay and subqueries are also called
4:10as inner queries and the query this
4:12query that contains this subquery is
4:14called as an outer
4:16query okay now many times these
4:19subqueries can actually be very easily
4:21replaced with the joints we have
4:23discussed about joints in the previous
4:24sessions of This squl Server video
4:26series if you haven't watched them I
4:28would strongly encourage you to do so we
4:30discussed about all the different types
4:31of joints that are available in SQL
4:33Server we even discussed about Advanced
4:35joints so please watch joints first so
4:38let's rewrite this query you know which
4:40gives us exactly the same output using
4:44joints okay so how do we do that let me
4:47copy this instead of retyping so select
4:49all those columns from TBL products and
4:53I'm going to do an inner join so when we
4:55do an inner join what happens we get all
4:58the matching rows between both the
5:00tables so I'm going to join TBL products
5:03with TBL product sales table and how do
5:05we join them in TBL products table we
5:08have the ID column which matches with
5:11product ID in TBL product sales
5:14table okay now if I execute this query
5:17we get all the matching rows between
5:18both the tables now I'm actually going
5:20to get an error that's because in both
5:23the tables we have this ID column in TBL
5:26products and TBL product sales tables
5:28that's why it's saying amb column name
5:31so we have to tell it from which column
5:32uh from which table we want it so I'm
5:34going to specify the table prefix so
5:37that the query is not confused okay so
5:40now if I execute this query we get all
5:42the matching rows between both the
5:43tables that's not what we want we want
5:45to use a left join
5:50here okay when I use a left join I get
5:53all the matching rows between both the
5:54tables plus the non-matching row which
5:57is TVs from the left table but that's
5:59not not what we want what we want is
6:03only the non-matching rows from the left
6:06table okay so we don't want the matching
6:09rows plus non-matching rows we I mean we
6:11only want the non-matching rows how do
6:13we specify that using a we Closs in TBL
6:17product
6:20sales that product
6:24ID is
6:25null that means it's going to return us
6:29the TV Z so we can easily you know
6:31replace that with a join as well so we
6:33are using a left outer join here with
6:36you know this wear clause which is
6:38giving us the list of all products that
6:40we haven't sold at least once okay so in
6:43this example we have seen how to use a
6:46subquery within the wear clause and we
6:48have also seen how to replace that
6:49subquery using a join okay both of these
6:53queries give us exactly the same output
6:55now let's look at another example of
6:57using subqueries so write a query to
7:00retrieve the name and total quantity
7:02sold now let's say I want the name of
7:04the product so if you look at these two
7:06tables that we have I want the name of
7:08the product and the total quantity of
7:11each product that we have sold so if you
7:13look at the quantity sold how many
7:15laptops did we sell seven laptops and
7:18how many desktops did we sell uh 9 + 4
7:2113 + 5 18 uh desktops we have sold okay
7:26TVs we haven't sold any so I want to
7:28retrieve the name of of the products and
7:30total quantity we sold and we want to do
7:32that using a subquery let's see how to
7:34do that in this example we have seen how
7:36to use a subquery in the W clause in
7:38this example we'll see how to use a
7:40subquery in the select list okay so what
7:43do we want we want the name of the
7:44product so first let me write
7:46from um TBL products table what we want
7:50we want the name of the product and then
7:53what else we want we want the total
7:55quantity that we have sold okay so I'm
7:58going to use the SU aggregate function
8:01okay but then I'm going to use a
8:03subquery so anytime we want to use a
8:05subquery the first thing we do is we
8:07open up and close a parenthesis and
8:09within that we write the subquery so
8:11select what do I want I want some of
8:14let's write gets rid of that sum sum of
8:18what do I want I want quantity sold so
8:21first let me write the from Clause so
8:23that I'll get some intelligence
8:25there so quantity sold so sum of
8:29quantity sold from TBL products and then
8:33we have to use a we Clause where product
8:37ID because we don't want the sum of all
8:40the products we want to specify for
8:42which product you want okay for now I'm
8:44going to use the question mark we'll
8:46fill this what which is the product ID
8:48for which we want the sum of the
8:50quantity sold in just a bit okay and
8:53then give this one an alas so as qty
8:58sold quantity sold from TBL products
9:02okay now what I'm going to do here look
9:03at this I have this TBL products from
9:05this table I want the name and from this
9:08table I want the total quantity sold
9:11okay so but then when I select a product
9:14from this table for example when I
9:15select TV I want to immediately come to
9:17this table for all those product which
9:20has got this TV uh you know which is
9:23nothing but the id id is one so for all
9:26the products you know for all the
9:28products that has ID one sum the
9:30quantity sold and then retrieve that I
9:33have to specify that in this you know
9:35wear Clause so how do I do that simply
9:38specify TBL
9:40products do ID so anytime you select a
9:46product you know for example if I select
9:48this TV its ID is one it looks up that
9:50here and then if there is a match it is
9:52going to add the quantity sold and
9:55returns that as quantity sold okay so
9:58let's execute this query and and see
9:59what we get look at that seven laptops
10:0218 desktops TVs we haven't sold any and
10:05then if you want to order that by
10:06product name we can simply say order by
10:09name of the
10:12product okay so here we are using a
10:15subquery inside a select statement in a
10:18select list okay let's see how to
10:20replace this again using a join again
10:22that's very easy to do okay so we have
10:25to know how to use Group by and joins
10:27here so I'm going to going to copy this
10:30query just to save some time in
10:33typing so select what do we want we want
10:36the name of the
10:37product and we want the total quantity
10:41so sum
10:44of
10:49quantity as total quantity or quantity
10:54sold from TBL products and I want to do
11:02a left join with TBL product sales table
11:06on this you know the join condition is
11:08the same and the important thing is we
11:11have to use Group by because we want to
11:13group by name and then some their
11:15quantities so Group by
11:18name okay so we discussed about joints
11:20in the in the previous sessions of this
11:22video series so I'm not going to go into
11:25the details of joints now here so all I
11:28want to show here is um you know we can
11:31replace subqueries with joints okay look
11:33at that I get the same output okay
11:36desktop 18 laptop 7 and TVs Nal
11:39similarly the same output here 187 Nal
11:43so most of the time the subqueries can
11:46be very easily replaced with
11:48joints okay so today we have seen how to
11:51use a subquery in the wear clause and
11:53how to use a subquery in the select
11:56Clause subqueries are always so what are
11:59sub sub queries from these examples it
12:00should be very clear that a subquery is
12:02simply a select statement that returns a
12:04single value and that can be nested
12:06inside a select update insert or delete
12:09statement we have seen how to Nest a
12:10subquery inside a select list and inside
12:13the we Clause similarly we can um you
12:16know have that in a delete or in an
12:18update statement or even in an insert
12:22statement it is also possible to Nest
12:24subquery inside another subquery so here
12:27we have a subquery here this is the
12:29subquery okay now you have a wear Clause
12:33here so here you can have another
12:35subquery if you wish to okay so a
12:38subquery can contain another subquery
12:40that subquery can contain another
12:41subquery so this way we can Nest
12:43subqueries up to 32 uh levels according
12:46to msdn and subqueries are always
12:49enclosed in parenthesis and we have seen
12:50that and are also called as inner
12:52queries and the query that contain the
12:54subquery is called as outter query The
12:56Columns from a table that is present
12:58only inside a subquery cannot be used in
13:00the select list of the autter query this
13:02is important again so we have the
13:05subquery here for example if you look at
13:08this
13:09one
13:11so this a subquery now if you look at
13:14this TBL product sales table this up
13:17this table appears only within this
13:19subquery now if you look at the product
13:22sales table it has got other columns as
13:25well for example unit price that comes
13:28from TBL product sales table but this
13:30TBL product table sales table is present
13:33only inside the subquery within the
13:36opening and closing of that parenthesis
13:38now can I use the columns from this
13:41table in the select list of the outer
13:43query can I use or can I try to retrieve
13:46unit price column
13:50from this TBL product sales table that
13:53appears only in a subquery no you cannot
13:55if you try to do that you get an error
13:59okay invalid column name unit
14:01price so the columns from a table that
14:04is present only inside a subquery cannot
14:06be used in the select list of the AO
14:07query now we have seen that we can
14:11achieve the same result either by using
14:13a subquery or a join okay so when we
14:15have these choices then what to choose
14:18for performance subqueries or joins okay
14:21we'll talk about the performance aspect
14:23in the next video session on this slide
14:26you can find resources for asp.net C and
14:28SQL Server interview questions that's it
14:30for today thank you for listening have a
14:32great day

About this transcript

This page contains the full transcript of Subqueries in sql Part 59 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,529 words across 347 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.