YT YouTube2Text

YouTube2Text → Transcripts → CTE in sql server Part 49

CTE in sql server Part 49 — Transcript

by kudvenkat · 2,050 words · 297 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I'm
0:02wenet this is part 49 of SQL server in
0:05this session we'll learn about common
0:07table Expressions before continuing with
0:09the session I strongly recommend to
0:11watch part 48 of this video
0:14series Common Table expressions are
0:16introduced in SQL Server 2005 so what is
0:20s CTE CTE is a temporary result set that
0:24can be referenced within a select insert
0:27update or delete statement that immedi
0:29Med follows the CTE let's understand
0:32what we mean by this definition with an
0:34example so here you can find the Syntax
0:37for creating a CTE so to create a CTE we
0:41use the withth keyword for example to
0:43create a stor procedure we use create
0:46procedure command similarly to create a
0:48table we use create table command but to
0:51create a CTE we use the width keyword so
0:55width keyword followed by the name of
0:58the CTE
1:00and then the columns that would make up
1:02your CTE so what is a CTE a CTE is a
1:06temporary result set so obviously a
1:09result set will have columns so what are
1:11the columns that would make up your
1:13result set that is what you specify here
1:17within a pair of parenthesis then you
1:19use the ask keyword then another set of
1:23PA you know parenthesis and then your
1:25CTE query there let's look at this with
1:28an example
1:30we have two tables here TBL employee and
1:32TBL Department tables TBL employee has
1:35got ID name gender and Department ID
1:37columns and TBL department has got
1:39Department ID and Department name
1:41columns now let's say I want you to
1:43write a query which gives me an output
1:46that you can see here I want the total
1:49number of employees by Department
1:52obviously to achieve this output I don't
1:55have to use a CTA I can simply achieve
1:57this by using joins and group bu but
2:00since we are talking about CTE here
2:02let's see how to achieve this using a
2:05CTE so I'm creating a c here called
2:08employee count I'm using the width
2:11keyword with employee count so employee
2:14count is the name of the CTE and
2:16Department ID and total employees are
2:18the columns that form you know the
2:22CD as and then this is your query so if
2:25you look at this query it's pretty
2:27straightforward what is this query doing
2:29it select two columns Department ID and
2:32total employees okay count of Star as
2:35total employees from TBL employee Group
2:38by Department ID so this query will
2:40return me the total number of employees
2:43by Department ID but if you look in the
2:45output I want total number of employees
2:47by Department name okay so this select
2:52query is nothing but your CTE query and
2:54if you look at the select query it's
2:55returning these two columns and these
2:58two columns will map to these two
3:00columns that you can see here you know
3:03this CTE has got Department ID and total
3:05employees your select query has got
3:07Department ID and total employees so
3:10these columns map to this CTE columns
3:13okay and then look at the way we are
3:15using the CTE this
3:18CTE is now being used in the select
3:21query so you're joining the employee
3:23count CTE with TBL Department table and
3:26why are we doing that because the
3:28employee count CTE has got Department ID
3:31and tottal employees Department table
3:33has got Department ID and Department
3:35name so we join the employee account
3:37with TBL department on the department ID
3:42column and we retrieve Department name
3:45from TBL
3:46department and total employees from
3:49employee count CTE and finally we order
3:52by total employees so if you execute
3:54this entire query we get the output that
3:56you see here let's look at the CT CTA in
4:01action so we are creating this CTA with
4:04employee count these are the columns
4:06that would make this
4:08CTA as and this is the CTA query the
4:12select query that would actually serve
4:14the data for the CTE so now when I
4:16actually execute this select query Alone
4:19look at this I get this this is nothing
4:21but a temporary result set a result set
4:24and for this result set you are giving a
4:27name called employee count
4:31and this column Department ID and total
4:33employees returned by your select query
4:35is mapping up to the columns you know in
4:38the
4:40CTE okay and then what you're doing
4:42you're joining this employee account
4:44with TBL Department table and the rest
4:47of the stuff is is pretty much a simple
4:49join here and finally you're ordering by
4:52total employees so if we execute this
4:55entire staff we should get the output
4:57that we are expecting all right right
5:00now the columns that you specify for the
5:02CTE here are actually optional if you
5:05don't specify I mean look at this
5:08obviously when you execute this
5:10query you get these two columns now if
5:13you don't specify the column names for
5:16your CTE
5:17here what is going to happen the same
5:20column names are retained and then used
5:24as the columns for this
5:26CTE so let's execute this and we get the
5:30same amount output still okay so it is
5:35also possible that you can just say Okay
5:37instead of saying Department ID I can
5:40just say
5:41d ID and instead of saying total
5:44employees I can say total but then the
5:47moment you do that the columns you know
5:50though this query is returning them as
5:52Department ID and total employees you
5:54know for the CTE they will be mapped to
5:57DP ID and total employees will be mapped
6:00to total so when you refer to that CTE
6:03in the select statement you will have to
6:05change those column names accordingly
6:08okay because if I try to execute this
6:10now we get an error invalid column name
6:13Department ID because on employee count
6:16CTE I don't find a column called
6:18Department ID because you have redefined
6:21you have renamed that to be DP DP ID so
6:25you should use DP
6:28ID
6:30and similarly here you have to specify
6:33that as
6:37total so now when we execute this we
6:40should succeed and get the same
6:43output all right um you know if you
6:47don't
6:48specify I mean if you do specify the
6:50columns the number of columns that you
6:52specify here should match with the
6:55number of the columns that your select
6:57query is returning if you say for
6:59example let's say I have another column
7:01XY Z for example okay look at this your
7:04select query is actually returning two
7:06columns Department ID and total
7:07employees Department ID will be mapped
7:10to D ID total employees will be mapped
7:12to Total but there is no you know third
7:15column in the select query to map to XY
7:17Z so obviously when I try to execute
7:20this it throws an error stating that
7:22employee count has fewer columns than
7:26were specified in the column list
7:29obviously that makes sense okay so the
7:32number of columns that you specify here
7:34should match the number of the columns
7:36returned by the CTE
7:38query all
7:42right now this is important to keep in
7:45mind you know if you look at the
7:47definition of the CTA a CT is a
7:49temporary result set we know what we
7:51mean by what we mean by that and then a
7:54CTE is a temporary result set that can
7:56be referenced within a select insert
7:59update or delete statement that
8:01immediately follows the CTA so if you
8:04look at the way we are using the CTA we
8:07are using the CTE within the select
8:08statement if you look at the select
8:10statement it follows the CTE there is
8:13nothing in between the CTE and the
8:15select statement that's why we are able
8:17to use that but on the other hand it's
8:20the same CTE except that you know I have
8:23select hello here okay we are saying we
8:26are using another select statement and
8:28within this select statement I am not
8:30referring to the CTA but then there is
8:33another select statement here where I am
8:35referring to the CTA so obviously what's
8:37going to happen by the time you know we
8:40execute this query gets executed the CTE
8:43is gone because this CTA will be
8:46available to a select insert update or
8:50delete that immediately follows the CTE
8:53if you try to use it anywhere else then
8:56you know if you try to do something else
8:58in between we get an error let's look at
9:00this in
9:04action so here we have the employee
9:07count CTE you know it's the same thing
9:09and then we have the select hello in
9:11between now look at this if I comment
9:13that and then if I try to execute this
9:16entire thing you know we succeed we get
9:19the output that we were getting before
9:21but on the other hand look at this the
9:23moment I uncomment
9:27this we are not using using the CTE in
9:30the select insert update or delete
9:32statement that immediately follows the
9:33CTE so obviously you cannot access this
9:37employee count in this select statement
9:39because this is not the immediate select
9:41statement for this CTE so let's see what
9:44happens if I try to execute this we get
9:47an error stating Common Table expression
9:49defined but not
9:52used okay so that's why in the
9:54definition it's very clear you know a
9:57CTE is a temporary result set that can
9:59be referenced from within a select
10:01insert update or delete statement that
10:03immediately follows the CTE and I hope
10:06that makes sense
10:09now all right it's also possible to
10:13create multiple ctes using a single
10:16width Clause now we know that to create
10:18a CTA we use the width
10:21Clause so you
10:23specify with keyword and then the name
10:26of the CTA and then the columns that
10:28would make pick up that CTE as your CTE
10:32query so this entire thing is one CTE
10:36but if you want an additional CD using
10:39the same width keyword what you can
10:41basically do is put a
10:44comma another name of the CT another CT
10:48and then the columns that would make up
10:49that CT as you know select query that
10:53can you know that makes up this CTE so
10:56it's possible to create more than one
10:58CTE using the withd keyword just by
11:01separating them with commas so what we
11:03are doing here it's pretty simple now if
11:05you look at this select query all we are
11:08doing is we are getting the total you
11:10know the total number of employees by
11:13department but we're filtering you know
11:15we are only doing it for it and payroll
11:18and here we are doing it for HR and
11:20admin departments so obviously when I
11:22execute this query you should see we get
11:25the total number of employees for it and
11:28payroll
11:29departments and when we execute this
11:32query you should see that we get the
11:36total number of employees for admin and
11:38HR department and we have named our CS
11:42accordingly so this gives us employee
11:44count by HR and admin Department this
11:47gives us the employee count by payroll
11:49and IT department and the way we are
11:52using that there is a select statement
11:54look at this now you might be wondering
11:56okay I am using this city within the
11:59select statement this is another select
12:01statement where I'm using you know maybe
12:04this CT so I'm not using them within a
12:08select statement that immediately
12:09follows CD actually this entire thing is
12:12treated as one statement because you're
12:15using the union keyword and we have
12:17spoken about Union keyword in the
12:19previous sessions of this video series
12:21which basically unions the result of
12:24this query with this query so this
12:26entire thing is treated as one a
12:29statement so obviously now if I execute
12:31this entire thing you should see total
12:33employees by department for all the four
12:37departments okay so this example
12:39illustrates that it's possible to create
12:41multiple CTS using a single with
12:46clause on this slide you can find
12:48resources for asp.net and cop and SQL
12:51server interview questions that's it for
12:53today thank you for listening have a
12:55great day

About this transcript

This page contains the full transcript of CTE in sql server Part 49 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,050 words across 297 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.