SQL Server Programming Part 11 - Common Table Expressions (CTEs)

welcome to this YSL tutorial in this session we're going to teach you how you can use common table expressions or CTS for short in Microsoft sequel server we'll begin this session with a quick explanation of what a CTE actually is and will show you how you can create a very basic one to demonstrate the simple principles well then move on and show you a slightly more practical use for CDs calculating the sequence of aggregates will also show you a neat way to name the columns in a CTE to avoid writing aliases and finally we'll finish the video with a quick look at how to create multiple CDs and I can use them in combination in the final query so let's get started in SQL server a common table expression or a CTE for short is simply a technique for creating a temporary set of Records which you can then reuse immediately in another select statement for instance so for our first very simple example and please do bear in mind this is an incredibly simple example just to demonstrate the principle we've got a simple select segment here which shows us a list of films released before the year 2000 and it's this if I'd like to turn into my common table expression so in order to do that here are the standard things you'll need to do but this like statement you'll need to write the word with and then come up with a sensible name that describes your CTE so mines gonna be called early films follow that with the word as and then you simply need to wrap up your select statement in a set of round brackets so by opening certain parentheses above the Select statement and close it right at the end I can simply in down these to tidy up the layout and there you go that's your common table expression you'll probably notice at this point that we still have a syntax error indicator with this little squiggly red underline here and that's because once you've created a common table expression you need to immediately use it in another statement so for example we could use this the set of temporary records returned by my CTE we could then use in a subsequent select statement so it's a really really simple example we can write below this select and I can simply select everything using the asterisk from my CTE which is called early films and there's my my CTE named and I'd be completely pointless obviously to do this in the real world let me let me add a quick extra thing you could add another where clause for instance so this could be filtered so it's gonna be where the film's runtime minutes perhaps is greater than 120 so show me all the film was released before the year 2000 whose running time is longer than 120 minutes again obviously we could have built these to where clauses into one single select statement bear in mind this is just purely to demonstrate the principle of how a CTE works if I execute this query I ought to end up with a list of films whose release dates before 2000 and his running time is longer than hundred and twenty so we've seen an example of a very basic CTE just to demonstrate the principle but common table expressions really come into their own when you have a sequence of complicated steps to perform to reach the answer you want so it's another example why we're showing the number of films made in each country in the film table so you've groups about the film's country ID and if I execute this query will see a list that's basically the account of films made in each country what I'd like to do now is change this into a CTE so that I can find the average of this column of numbers so in order to do that well first of all have today is give this field an alias I'm going to give it the number of films alias now I can send this into a CTE in the same way we did earlier on so I'm gonna use the width keyword filming counts is what I'm gonna call it as and then I can open up a set of parentheses place them at the end and then just to tidy up my layout indent my select statement now that's my CTE what I need to do now is execute another select statement on the results returned by it so below the parentheses I'm gonna type in select from and my common staple expression was called film counts so that's all I'm going to use here film counts and I can simply look for the average of one of the fields within it and the field that I want should be called number of films when I execute this entire query now that will tell me the average number of films released per country so hopefully you can see with this example this would be somewhat more complicated to try to do using the single select statement the common tab like freshmen here really really helps us to break down the logic of the individual steps we need to perform as a quick side note it's worthwhile mentioning that you can reel able the columns in a CTE without using aliases as I've done here alright take this alias away completely what I can do is I can actually add the names for my CTE columns after the name that I've decided to give it so if I open another set of parentheses I can create a column called country and I can create another column called number of films well that means now if I execute the query it'll work happily as it did earlier on and give me the same results and but I can also refer to those columns by name in my second select statement so I could do things like refer to the country field and there it is in the intellisense list and I could also refer to my number of films field as well no that is so I could query again I'll get a different set of results that would show me the same information but it's worthwhile knowing that a second way to rename the columns in a CTE you can simply include a comma separated list in parentheses after the CT is named hopefully it goes without saying although I'm about to say it anyway that if you've labeled a certain number of columns in the definition of the CTE you need to make sure you select the same number of columns when you when you select the records for it all the examples we've shown so far have involved just a single common table expression but it's certainly worthwhile knowing that you can generate a list of CCES at the same time and then use the entire list in a combination in the final result set so here we've gone back to our previous early films CTE what I'd like to do now is add another CTE which shows me a list of films released since the year 2000 so to do that in the quickest easiest way possible I'm going to copy everything from my first CTE definition apart from the word width I'll copy that then I need to add a comma after the first CTE then I can simply paste in what I previously had and then modify his name I'll call this one recent films of course I need to modify my operators there so I'm looking for all the films whose release date is on or after the January 2000 so this point I now still need to use these to see tes in a final select statement so that's add another slight statement I'm going to cite everything from I'm gonna go with the early films first there it is early films I'll give this an alias as II I'm gonna use an inner join to link it to the recent films CTE I'll give this an alias as R and I'll say I want to join these two tables on the film name field so on each film name and whether is equal to u dot film name so what this combination of CTE should now do when I execute the query it will show me a list of films whose names appear in both the early film table and the recent films table if I execute the entire query that's what we get so King Kong actually appears twice in the early film table and once in the recent films and we have around the world in those days and cuz yeah we all as well so that's how you build a list of CTE s it's as easy as adding a comma after the first one coming up with a new name and then writing another select statement the same rule applies as with just a single CTE you must use a reference to each one that you've created in a final set of results if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 61,189

Rating: 4.9249148 out of 5

Keywords: sql, sql server, common table expression, CTE, wise owl

Id: U0wXjUi2v_U

Length: 8min 49sec (529 seconds)

Published: Thu Mar 21 2013