YT YouTube2Text

YouTube2Text → Transcripts → Recursive CTE in sql server Part 51

Recursive CTE in sql server Part 51 — Transcript

by kudvenkat · 2,020 words · 277 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:02Venit this is part 51 of SQL server in
0:05the session we'll learn about recursive
0:08Comon table expressions and the problems
0:10they solve before continuing with the
0:12session I strongly recommend to watch
0:14Parts 49 and 50 of this video
0:18series I have this employee table which
0:21has got employee ID name and manager ID
0:23columns since a manager is also an
0:26employee in that organization you know
0:28we store both employee and manager
0:30details in the same table now if I ask
0:33you who is the manager of mik you will
0:36look up the manager ID column which is
0:38number two number two you will then take
0:41that value and look up in the employee
0:43ID column number two is Josh so Mike's
0:47manager is Josh so we are taking the
0:50manager ID column and looking up another
0:53column in the same table so you are
0:55referencing the same table so it's
0:58called as a self referencing table now
1:01if I ask you to display employee name
1:03along with their manager name then we
1:05have to join this table with itself and
1:09we know that joining a table with itself
1:11is called a self jooin and we have
1:13spoken about self joins extensively in
1:16part 14 of this video series okay so
1:19this is a simple self-join query so
1:21obviously if I just want to display the
1:24employee names along with the manager
1:26names we can make use of a simple self
1:29join
1:30okay and if you look at this Josh
1:33Manager ID is null okay which means Josh
1:37does not have any manager so it
1:39basically displays null here but we are
1:41displaying super boss with the help of e
1:44null function so if manager. name is
1:48null then we are replacing that null
1:50with super boss okay so and we have
1:53spoken about different ways of replacing
1:55null values in part 15 of this video
1:57series okay so the reason why we talk
2:00about this example here is that we're
2:02going to extend this a little further
2:04and then we will see how to solve it
2:06using a recursive CT so if you just want
2:09to display employee name along with
2:11their manager name you can simply use a
2:13self jooin and that would solve you know
2:17what we are expecting as far as the
2:18output is concerned but let's say I want
2:21to display the employee name their
2:24manager name and along with that I also
2:27want to display their level in the
2:28organization okay now if you look at
2:31this organization chart here Josh is the
2:34super manager because he doesn't have a
2:36manager ID which means he's at the top
2:38of the
2:39hierarchy so and then to Josh there are
2:42two direct reports Tom and Mike you know
2:46are the direct reports of Josh how can
2:47we tell that if you look at Josh his
2:50employee ID is number two and Tom and
2:53Mike their manager ID is number two so
2:56obviously Tom and Mike report to Josh
3:00and then Tom in a similar fashion has
3:02got three direct reports Mike has got
3:04two of them and Sam Pam has got one
3:08direct report so if you look at the
3:09hierarchy Josh is the at the highest
3:12level and Sam is at the bottom of the
3:15hierarchy and so if you look at the
3:17levels of these employees Josh level is
3:20one he's at the top so that's why level
3:23one and if you look at Tom and Mike they
3:26are level two so Tom and Mike you can
3:28see employees Tom and and Mike their
3:30level is two along the same lines Pam
3:32James Simon John and Mary level three
3:35and at the bottom of the hierarchy is
3:36Sam who is level four so right now I
3:39want to display employee name their
3:42manager name and their level within the
3:45organization now to achieve this we can
3:48use recursive CTS using recursive CTS
3:52the code will become a lot easier okay
3:55let us see how to solve this using a
3:57recursive CTE
4:00so this is the recursive CT I mean we
4:02have spoken about you know how to create
4:05a CTE itself in the previous session so
4:08we're not going to talk about the basics
4:09of CTE here we will see how to create a
4:12recursive CTE when what is a recursive
4:14CTE a CTE that references itself is
4:19called as a recursive CTE now in simple
4:21terms forget about everything that you
4:23see on this
4:24slide we know that we create a CTE using
4:27with keyword so with and employee CTE so
4:31here employees CTE is a CTE and then
4:34this is obviously having four columns
4:36employee ID name manager ID and level
4:38columns the reason why I have put level
4:40in square brackets is because level is a
4:43keyword in SQL Server if you if you want
4:45to use a keyword as a column name then
4:48you'll have to wrap that inside the
4:50square brackets okay so here basically
4:53employee CTE is a CTE and if you look at
4:57the CTE query within the as block here
5:01we are again referencing the CTE within
5:05the CTE we are referencing the CTE so
5:08this kind of becomes a self referencing
5:12CTE okay now let's understand what we're
5:14trying to do now if you look at the
5:16output what we are expecting we want the
5:18employee name manager name level okay
5:22but then the way we are implementing
5:24here if you look at the first look at
5:25this CTE itself so employee CTE which is
5:29four columns as and look at this this
5:32first query is pretty simple to
5:34understand we are selecting you know
5:37obviously our CD is rning four columns
5:39employee ID name manager ID and level so
5:42our select query should also return four
5:44columns employee ID name manager ID and
5:47one as level we hardcoded that one and
5:50we'll I'll tell you why in a short while
5:53so select these four columns from TBL
5:58employee where manager ID ID is null so
6:00if you look at this query from the table
6:03TBL employee we are selecting employee
6:05ID name manager ID and then a hardcoded
6:09value of
6:10one from TBL employee where manager ID
6:13is null so for which record is the
6:16manager ID null for Josh record manager
6:19ID n is null so we only get Josh record
6:23and you know Josh is the super boss in
6:25the organization and he should get a
6:28level of one
6:30okay so we we are selecting the level of
6:33one for Josh that's hardcoded here and
6:37we only get Josh's record because we are
6:39checking whether if it is null and who
6:42has got null only Josh so we get only
6:45Josh's record and his level will be one
6:48okay so that's the first query which is
6:50very simple to understand and then we
6:51are using Union all operator now when
6:54you use a union all operator the number
6:57and type of columns that are returned
6:59the first select query should match you
7:02know to the number and type of columns
7:04return return from the second query as
7:06well so if you look at this one in the
7:08select query so if you look at from the
7:11output persp perspective we just
7:13finished retrieving this R now we have
7:15to retrieve the rest of the rows and
7:17increment their levels as they move down
7:21in the hierarchy we should increase
7:23their levels okay so for Tom and Mike
7:25the level should be two and for the rest
7:27of the you know this five people it
7:30should be three and for Pam it should be
7:32four and to do that what we are
7:34basically doing is we are selecting the
7:36you know look at this look at the join
7:38query first we are joining employee CTE
7:42with TBL employee okay so we are joining
7:47the CTE with TBL employee and in TBL
7:50employee we have got all these rows okay
7:54so what's going to happen now we're
7:55going to recursively Loop through TBL
7:58employee and look at this from TBL
8:01employee from the stable TBL employee we
8:04are selecting three columns employee ID
8:07name and manager ID but the fourth
8:10column look at what we are doing we are
8:13taking that from employees CTE dot so if
8:18you look at this this the employee CTE
8:20and it's got a level column so employee
8:22CTE do level + one to the existing level
8:28just add one one more okay so we are
8:32referencing that level column from the
8:34CTE itself and then we are getting
8:36employee ID name and manager ID from TBL
8:39employee okay so we require these four
8:42columns from this query as well we're
8:44getting those by joining TBL employee
8:47with employee CTE and look at this this
8:50on Clause is another interesting one so
8:54you are looking up the manager ID column
8:57in the TBL employee table and you
8:59matching that to employee CTE do
9:02employee
9:03ID okay so that's the CTE itself and
9:06you're doing a union all so this first
9:09select query will give the
9:11manager I mean Super boss details and
9:14then this recursively will retrieve all
9:17the other members incrementing their
9:20levels okay and then so this is just the
9:24CTE okay so if you look at the output I
9:26mean if you look at the columns employee
9:27ID name manager ID and letter
9:30okay so the CTE will have all of them so
9:32let's see just that part now so if you
9:35look at the CTE
9:38itself so until now we have discussed
9:42this okay so let's say let's comment
9:46this section for a
9:50bit and let's say select stuff from this
9:54employee
9:57CTE so whatever CTE we have Constructor
10:00we are selecting all the rows and
10:01columns from that CTE so let's execute
10:04that and look at this I get this output
10:07employee ID name manager ID and level so
10:10we are getting the level as expected now
10:13all we have to do is instead of you know
10:16displaying manager ID and employee ID I
10:18just want name and a manager name okay
10:22to do that we can simply use a self
10:25jooin okay so we can join the CTE with
10:29itself now and that's pretty simple just
10:32like how you join a table with itself
10:34you can join join a CTE with itself
10:38because a CTE is also a temporary result
10:41set so for that purpose what we are
10:44doing
10:46here so let's uncomment this so if you
10:50look at this we want the employee name
10:52and manager name so if you look at this
10:54employee CTE we are giving it two
10:56aliases one is called as empc CTE the
11:00other one is called as manager CTE we
11:03are giving those aliases and from
11:05employee CTE you're selecting the name
11:08and that's nothing but employee name and
11:10then you checking from the manager CTE
11:13you're selecting the name and you're
11:15checking because a manager name can be
11:18null for the super boss so that's why
11:20instead of displaying null we are
11:22replacing that with super boss and we
11:24are giving this column an alias call
11:26manager from TBL employee and obviously
11:29we require the level as well and you can
11:32get the level either from employee CTE
11:34or manager CTE because it's the
11:37same that's it so this part is pretty
11:39simple all we have to understand here is
11:42the second part which is a little
11:43confusing you're joining TBL employee
11:47with employee
11:48CTE and then you're incrementing the
11:51level that's the piece that we need to
11:55understand so obviously now if we
11:57execute this we should get the data that
12:00we are expecting so employee name
12:03manager name and their
12:07level on this slide you can find
12:09resources for asp.net cop and SQL server
12:11interview questions if you want to
12:13receive email alerts when I upload new
12:15videos please subscribe to my channel
12:17that's it for today thank you for
12:19listening have a great day

About this transcript

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