YT YouTube2Text

YouTube2Text → Transcripts → Derived tables and common table expressions in sql server Part 48

Derived tables and common table expressions in sql server Part 48 — Transcript

by kudvenkat · 2,897 words · 418 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to premium technologies I
0:01am venket this is part 48 of sequel
0:04server in this session we'll learn about
0:06derived tables in common table
0:08expressions we'll compare these with
0:10other constructs that are available in
0:12sequel server like views table variables
0:15local and global temporary tables let's
0:17start with an example I have TBL
0:20department table here which is called
0:21department ID and Department name
0:23columns TBL employee table here which
0:25has got ID name gender and department ID
0:28columns now let's say I want you to join
0:30these two tables and produce the output
0:32that you can see on the right-hand side
0:33here I want the department name and the
0:37total number of employees within that
0:38department but then your list should
0:41only include those departments which has
0:44got two or more employees and obviously
0:47to achieve this output we have several
0:50ways but then we will see how to do that
0:52using views okay so we are creating a
0:55view here create view view name as we
0:59are selecting the department name
1:01department ID and count of star which
1:05would count the number of employees
1:06within that department and we are giving
1:08it an aleeah's called total employees
1:11from TBL employee join with TBL
1:14department and obviously between these
1:16two tables department ID is the column
1:19that's common so we are joining those
1:21two tables on that column and you will
1:23have to group by department name
1:25department ID because we want the count
1:27and we have spoken about joins and grew
1:30by in a very great detail in the
1:33previous sessions of this video series
1:35so please check that part and we have
1:37spoken about views as well ok so we are
1:40creating this view so once we have this
1:42view it's very easy to compute this
1:45result that you want so the columns that
1:48you want or the department name and
1:50total employees so obviously we have
1:52those columns in the view department
1:54name and total employees all you have to
1:56do is select those columns from that
1:58view and then select only those
2:01departments which has got total
2:03employees count greater than or equal to
2:062 which will give us this output ok but
2:10then let's say if we are using this view
2:13only within this query then it doesn't
2:15really make sense to create a view for
2:18that purpose that's why we have other
2:20constructs in sequel server which we'll
2:22be exploring next so views get saved in
2:25the database and can be available to
2:27other queries and stored procedures as
2:29well so if you are you know using this
2:31view in other stored procedures and and
2:33you know in other queries then that's
2:36fine but then if you are using this as a
2:38one-time you know just this one time in
2:41this query then then it doesn't really
2:44make sense to have this view created we
2:47can make use of other constructs that
2:48are available in sequel server like CDE
2:51derived tables temp tables table
2:53variables etc which we'll be talking now
2:55all right let's look at this in action
2:57so we have these two tables TBL employee
3:01TBL department you know the data
3:05okay so let's create this view execute
3:12command completed successfully so when
3:14we refresh this folder we should see the
3:16view there so views get physically
3:18stored in the database now if you want
3:21the columns from that view look at this
3:23we have all the columns from the view
3:25select star from if you say view it
3:28would return all the columns within the
3:31view we have got three columns
3:32Department name Department ID and total
3:34employees but then in our output we are
3:37only expecting department name and total
3:40employees so we are only selecting the
3:43required columns department name total
3:44employees from that view where total
3:47employees is greater than or equal to
3:49two which would return IT and HR
3:52departments so let's execute that query
3:54and we only get that all right but then
3:58if you are using this view just in this
4:01query then it doesn't really make sense
4:03to create a view all right let's see how
4:08to achieve the same thing using
4:10temporary tables so obviously the logic
4:15to calculate the department name and
4:18total employees it's exactly the same
4:20you know we have this query this query
4:23will not change it's just that we are
4:25going to use that with other with
4:27the constructs that are available in
4:28sequel server so here we are using
4:31temporary tables okay so the query is
4:34the same select department name
4:36department ID count of star as total
4:39employees we are giving it an aleeah's
4:41and we are selecting these columns into
4:43hash temp employee count this is the
4:47temporary table so you might be
4:48wondering where did you define the
4:51columns that this temporary table has
4:53you know when you use select into syntax
4:56what's going to happen behind the scenes
4:57sequel server will create those columns
5:00for us automatically you don't have to
5:02define the structure of the temporary
5:05table so we are selecting these columns
5:07into this temporary table you know the
5:09rest of the logic is the same we are
5:11joining TBL employee with TBL department
5:13on department ID column and grouping
5:17them by department name and department
5:19ID columns okay so this logic remains
5:21them it's just that you are selecting
5:24that data into this temporary table and
5:26then what you are doing it's pretty much
5:28same after that you want the department
5:30name TBL total employees instead of the
5:34view you are saying from the temporary
5:36table where total employees count is
5:39greater than or equal to two okay let's
5:42look at the set in action and remember
5:44since this is a temporary table it's a
5:46good practice to actually drop the table
5:48after you have finished using it and
5:51remember there are two types of
5:53temporary tables local temporary tables
5:55and global temporary tables depending on
5:57the type of temporary table you are
5:59creating the scope differs okay and
6:03temporary tables are stored in temp DB
6:05local temporary tables are visible only
6:07in the current session and can be shared
6:09between nested stored procedures meaning
6:12if you have created a stored procedure I
6:15mean a temporary table in stored
6:16procedure a and if stored procedure a
6:18calls stored procedure B then the
6:21temporary table that you have created in
6:23stored procedure a will be available in
6:25stored procedure B so local temporary
6:28tables are visible in the current
6:30session only in the transition and can
6:32be shared between nested stored
6:34procedure calls
6:35whereas global temporary tables are
6:37visible to all the sessions and a
6:40destroyed
6:41the last connection referencing the
6:43table is closed that global temporary
6:45table is basically closed let's look at
6:48this in action so we are selecting these
6:52columns Department name Department ID
6:54count of star into this temporary table
6:56so let's execute that so we should have
7:00the temporary table created now so
7:02obviously when we say select star from
7:04the temporary table you should see all
7:07the rows and columns so select star from
7:09the temporary table which here is temp
7:14employee count since we have a single
7:16pound sign it's a local temporary tables
7:18we have spoken about temporary tables in
7:20a great detail in the previous sessions
7:21of this video series so if you're new
7:23check those paths first so we have these
7:26columns so obviously if you want only
7:29those you know Department name and total
7:31employees columns you select those two
7:33columns from that temporary table where
7:36total employees is greater than or equal
7:37to 2 so when we execute this query we
7:40get the expected output so the output is
7:43exactly similar except that here we are
7:45making use of temporary tables instead
7:48of a view all right
7:51now let's look at how to do exactly the
7:54same thing using a table variable now
7:58when we created the temporary table we
8:01didn't define the structure the
8:03structure is automatically inferred
8:05based on the Select statement that you
8:06are using here because you're using
8:08select into but then when you are using
8:10table variable here we are defining the
8:13structure explicitly so declare a table
8:16variable I mean a variable and this
8:19variable is of type table obviously if
8:22it's a table a table will have columns
8:25and data types so you'll have to define
8:26them as well so this table is going to
8:28have department name department ID and
8:31total employees just like you know how
8:37we have this view department name
8:39department ID and total employees
8:41columns ok so next what we are doing we
8:46are inserting into that
8:50table variable department name depart
8:53this logic remains the same the query is
8:55exactly the same we are selecting
8:57department name department ID count of
8:59star from TBL employee joining that with
9:02TBL department on department ID columns
9:04and then finally grouping it by
9:06department name and department ID so now
9:09we have this table variable from the
9:11table variable you can select the
9:13required columns Department name and
9:15total employees where total employees is
9:17greater than or equal to 2 so it's
9:19exactly similar in the previous example
9:21we were using temporary table in this
9:23example we are using a table variable ok
9:27just like temporary tables a table
9:29variable is also created in the temp dB
9:32you know most of the people think table
9:34variable is actually created in memory
9:35which is not true both temporary tables
9:38and table variables are created in the
9:41temp TB the scope of a table variable is
9:44the batch or the stored procedure or a
9:46statement block in which it is declared
9:49but the advantage of using table
9:51variables is that you can pass them as
9:54parameters between procedure calls
9:57between one procedure to another
10:00procedure ok so let's look at this in
10:02action obviously it's a very good
10:06practice to drop temporary table so
10:08let's drop that table I mean even if we
10:13don't drop a local temporary table it
10:15gets automatically dropped in the
10:17session that has created it is closed so
10:21using table variable we are creating the
10:23table structure here declare the table
10:27variable specify the column names and
10:29the datatypes and then insert into that
10:31table variable and then select whatever
10:34columns you want from that table
10:37variable so let's execute this we should
10:41see the same output except that we are
10:44using the table variable and the
10:46advantage of using table variable is
10:47that you don't have to drop at like
10:49temporary tables but there are several
10:51other differences as well between table
10:54variables and temporary tables
11:01all right so we can also achieve the
11:04same thing using derived tables now look
11:07at this when we created a table variable
11:10we will have to define the columns and
11:12the data types for a table variable but
11:15whereas if you are using a derived table
11:18this table is derived so you don't
11:20really define a table as such you just
11:22give it an Aaliyah's so if you look at
11:24this this is the query that we have been
11:26looking at all this while select
11:28department name Department ID count of
11:30star s total employees from TBL employee
11:33joining that with TBL department the
11:35same query here now if you look at this
11:37this query is wrapped between these
11:39brackets and then you're seeing as
11:42employee count so this entire thing from
11:45this bracket to this bracket you know is
11:48considered as a table okay it derived
11:51table and you are giving that table a
11:54named employee count and then if you
11:58look at this the columns that are
12:00present in this table are nothing but
12:02the columns in the Select list here so
12:04you have department name department ID
12:06and total employees columns okay so now
12:09you treat this from here till here as a
12:14table so you treat this as a table
12:16employee count so select department name
12:19total employees from this employee count
12:23where total employees is greater than or
12:26equal to 2 so here you know this is a
12:30derived table this employee count is a
12:33derived table and derived tables are
12:35available only in the context of the
12:38current query here the Select statement
12:40if you have another select statement you
12:43know beneath that this derived table is
12:46not available for that select statement
12:48it's only available here within this
12:50context so let's look at this in action
12:57so if you look at this one if I execute
12:59this it gives us those columns
13:02Department name Department ID and total
13:04employees all those columns but then
13:07what we are essentially doing here is
13:09wrapping that inside this bracket and
13:11then giving it in name and this name can
13:14be treated as a table by itself which
13:16has got these three columns and what you
13:19are doing
13:19from these three columns you're
13:21selecting just Department name and total
13:23employees columns and then you're
13:25filtering on that column so where total
13:28employees is greater than or equal to
13:29two so obviously we only get those two
13:32rows where the employees count is
13:35greater than or equal to two which in
13:37this case is ID and H are just like
13:39other examples that we have seen and
13:43finally we will see how to achieve the
13:45same thing using something called CDE
13:48common table expression we will be
13:51talking about si te s in a great detail
13:53in the next session this is just an
13:55introduction to CDE so achieving the
13:57same output with using a CDE
14:00common table expression and CTS are
14:03introduced in sequel server 2005 so if
14:05you look at this one this is again very
14:07simple okay so the exact same query that
14:11we have been working with this query
14:13that you can see here now what we have
14:15done is we use this keyword vert and
14:18then you have given a name this is
14:20nothing but the common table expression
14:23name CTE name so with employee count is
14:26the name of your CTE and then you can
14:29specify the columns that your CTE
14:32returns okay this part is optional if
14:35this query is returning a unique column
14:38names then you don't have to define this
14:41column list okay but I have defined it
14:44here just to be clear so we this common
14:48table expression employee count is
14:50having three columns department name
14:52department ID total employees okay where
14:54are these columns coming from these
14:56columns are coming from the Select query
14:58so this select query look at this we
15:02have the brackets here so this is like a
15:05table what is the name of this table
15:08employee count
15:10and this is a CTE so we use the wit
15:13keyword so usually the syntax for CTE is
15:16in such a way that you use the width
15:17keyword with CTE name the columns that
15:21your CTE returns as and your select
15:24query your select query actually
15:27determines what columns are being
15:29returned by the CDE alright so we are
15:34done this is pretty much similar to
15:35derived table now if you look at this
15:37derived table this query
15:40you know returns I mean it's like a
15:43table you're just naming it as employee
15:45count along the same lines for a CTE
15:48this query and you're saying with CTE
15:52name and the columns as and then your
15:56query obviously then what you can do is
15:59since this is treated as a table now a
16:02common table expression and this common
16:04table expression has got three columns
16:06just like derive table and out of those
16:09three columns we just want the
16:10department name and total employees
16:11columns from that CD which is employee
16:14count here where total employee is
16:16greater than or equal to two so we get
16:19the same output let's look that in
16:21action so that's the CTE so let's
16:27execute this and we should get the same
16:31output all right a CTE can be thought of
16:35as a temporary result set it's a
16:38temporary table or you can also think of
16:40as I derived a table that is defined
16:43within the execution scope of a single
16:46select insert update delete or create
16:48view statement you can use this city
16:51even within a view okay but this is
16:56defined only within the execution scope
16:58of a single select insert update delete
17:00or create view statement of this is a
17:03little confusing what we mean by this we
17:05are going to talk about common table
17:07expressions in a great detail in the
17:09next session I will explain this
17:11definition clearly you know in the next
17:14video session and a CTE is a similar is
17:17similar to a derived table in that it is
17:19not stored as an object and lost only
17:22for the duration of the query so this is
17:25not being stored as a temporary table or
17:27a view okay it only lasts for the
17:31duration of this query outside the
17:34context of this query the city it
17:35doesn't really have any meaning we'll
17:39talk about CD is in a great detail in
17:40the next session of this video series on
17:44this slide you can find resources for
17:46asp.net c-sharp and sequel server
17:47interview questions that's it for today
17:49thank you for listening have a great day

About this transcript

This page contains the full transcript of Derived tables and common table expressions in sql server Part 48 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,897 words across 418 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.