SQL With - How to Use the With (CTE) Statement in SQL Server - Quick Tips Ep35

hey joy blue here and today I want to talk about the sequel with statement and in sequel server it's called the common table expression and it's abbreviated CTE so the sequel with statement allows you to do really two different things one it allows you to write our refactor sub-queries so you can pull them out of the main query and the other is you can do recursive queries today I just want to talk about the the sub query piece I want to kind of give you an introduction to the Wyss statement so let's start with a table I have out there in the sequel training online simple DB called employee okay so this table has an employee number name job manager hire date salary commission and department number and in order to really talk about the with statement I need to also talk about sub queries and you may or may not know about those but inside of sequel server you can just say you can actually say select star from and then have another select statement and you can just alias that so select star from and then this other select statement that's embedded that's called a sub query so let me run that and you see I get the same thing out of it and so you can do that multiple times and it's called a sub query but what we want to do is talk about the with statement so the with statement actually allows you to go up to the top of your query and start with the keyword of width and then you give this thing here what we're going to do is we're going to pull this select statement out and make this a little more clean and so we can give this select statement now you can think of it as this here returns a table so we want to name that table that's returning and so I'm just going to say our CTE employee table and you can name this whatever you want and then you just say the columns you want and so I'm gonna bring back three columns I'm gonna bring back an employee number employee name and the let's say the manager okay so we've got three columns and notice how I separate those by commas and then the next state may need to put in there is going to be the as statement so you're going to say with the and then give it a name what columns you're going to bring back as and now we need to put in our select statement and really what we can do is just simply grab this here and I will toss it in there and so this is the width part and so I also need to come in and define the columns here instead of star I need to put it in there like that and then now instead of having this sub-query I can say select star from let me scroll up so we see this whole thing so we have we called it CTE employee so I'll copy that and say select star from CTE from employee so we made this our own little table up here called CTE employee and now we can just simply select from it okay so let's see if this thing runs and so there you have it so we just said select star from CT employee and it pull back let me pull this up here it pulled back our three columns with that we defined and so that's really the basic introduction to a with statement there's a lot of things you can do at this point we could join this back act if we want it to let's go ahead and try that real quick and say inner join and we can enjoy back to the employee table itself and get the manager name on that's a CTE employee and notice how intellisense even brings that up a CT employee dot employee name I'm sorry employee number no on the seat we want the manager here manager we want that to equal the employee employee number and so now let's run that and you can see we took our our table that we created Ct employee inter joined it back to employee number and got the manager and if we really want it to make this little cleaner let me go ahead and grab these three columns here so I'll say and we have two we can even alias if we want so what's called a CTE go ahead and put some carriage returns in here so we can see what's going on and then let's bring back the the manager name which is going to be an employee table ee dot and we'll say employee name so and then what idea sad as manage your name and so let's go ahead and something looks wrong here since I a lease the table I cannot use the fully qualified table no math used alias here so let's toss that in there and now let's execute it and so now you can see we have the manager name so that's really the introduction there as always if you find this helpful please leave a comment below click the like button or subscribe to my channel you can also visit me at sequel training online comm and I hope you enjoyed this free video you

Channel: Joey Blue

Views: 53,204

Rating: 4.6524215 out of 5

Keywords: SQL WITH, CTE, Common Table Expressions, SQL (Programming Language), Data, SQL, SQL Tutorial, SQL Training Online, software, software tutorial, SQL WITH Statement, SQL Server, microsoft, sql, server, tutorial, course, class, with, statement, How-to (Conference Subject), Subject (programming)

Id: 5KGjqnMss7g

Length: 6min 32sec (392 seconds)

Published: Mon Oct 22 2012