SQL Server Queries Part 12 - Correlated Subqueries

welcome to this why cell tutorial in this session we're going to teach you how to work with correlated subqueries in Microsoft sequel server what we'll cover in this session is first of all how to create correlated subqueries we'll start with a reminder of how normal sub queries work and then show you how to convert a normal sub query into a correlated sub-query we'll explain the importance of using table aliases and then when we've run the query we'll try to attempt to explain how it actually works when we've done that we'll finish the video with a couple of other examples of correlated subqueries like spy only explain how you can use different comparison operators and how you can use calculated fields in a correlated sub-query as well so let's get started let's start with a quick reminder of how normal sub-queries work what I've got here is a basic query showing me three fields from my movies database or execute that you can see the set of results what I'd like to do is show these details only for films with the maximum running time in minutes and that's where a normal sub query comes in handy if I add a where clause it's my main query I can ask to share where the film run time in minutes is equal to and then here's where I write a sub query to select the max then time in minutes from the entire table so selects Max film runtime minutes excuse the typing from TV I'll film when I execute this query now I'll see that I get one single film returned there was more than one film with the same highest running time I'd see more than one record and but that's how a basic sub query works the important thing about this example an important thing about basic sub queries is that the sub query itself can be executed completely independently of the main outer query so if I select that basic selects Aymond and execute it it still returns a value all by itself the main difference between a correlated sub-query and a normal sub query is that a correlated sub-query relies on the outer query to be executed to provide its values so what that means is a correlated sub-query will be executed for every record returned by the outer query so to demonstrate that what we're going to do is rewrite this query if I just execute it again so you can remind yourself of the results I'm gonna execute this query so that we can see details for films with the longest running time in each country in the table one very important thing you must do when you're writing correlated subqueries is ensure that the tables used in the outer query and the inner query both have a sensible alias so I've already assigned an alias so that filled in table in the outer query I've given in the alias of F I'm gonna quickly assign one to the film table in the inner query I'm gonna call that one as G because it's an alphabet the reason that's important is because in order to turn this into a correlated sub-query I need to add a where clause and in the where clause I need to refer to the film table in both the outer and inner query and without the alias it would be impossible to distinguish between the two the where Clause itself is actually fairly straightforward I want to show records in the inner query where the film country ID field in the film table in the inner query is equal to the film country ID field from the film table in the outer query if I execute this query now what we should see is the set of results where I get one neck word for every single country sharing the film with the longest running time in that country so how does the correlated sub-query actually work let's say we can run through a quick example one thing to realize is that Ana correlated sub-query the inner query is executed for every single record that might be returned by the outer query so if we go through an example for the first record that might be returned by the outer query I've written a quick extra query here which shows me the film country ID name and running time in minutes of all of the films from the film table and the country ID for the first film is 241 so the first time the inner query is executed it tries to find the highest running time in minutes of films with a country ID of 241 but you've written a separate query here which shows us what that value is so the highest running time in minutes for films in the United States is 195 the running time in minutes of Jurassic Park is 127 so I go back to the full query here the value 127 is not the same as the value 195 so Jurassic Park isn't returned to the final set of results that process is then simply repeated for every single record in the main outer query so the next record in there will be spider-man whose country ID is 241 so we find what the highest running time in minutes for films with a country ID of 241 is that's 195 again of course and then we compare spider-man's running time which is 121 against 195 and that doesn't match so spider-man ISM returned to the main query either eventually we'll have processed the entire list of film names sort of films and we'll end up with at least one results for every single country in the table from this point on it's reasonably simple to experiment to get different sets of results so for example let's quickly change the operator rather than trying to find films whose running time in minutes is equal to the highest what I'd like to do now is find films whose running time in minutes is longer than the average running time for films in a particular country so in this example I'd hopefully experts get more than one result I'm going to show all the films in each country whose running time in minutes is longer than the average length of films in that country if I ask you this query now I ought to get some more than one results for each country and now we do I can also use expression fields or calculated fields in my correlated subqueries so rather than showing films that are longer than average for a particular country let's show films that are longer than average of all films in a particular year what I need to do in order to make that work is calculates the year of the film or release 8 so I'll do that in the ads query first side calculates a year of F at film release date I can actually get rid of my country table altogether I won't meet that in this query so let's get rid of the join and the country table and what I can do now is ask to see in my inner query where the year of G dot film release date is equal to the year of F dot film release date add a quick order by clause as well to make sure all by I'm give this a quick alias all by Y and if I execute this entire query I'll find films that are longer than average for all films released in the same year if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 83,784

Rating: 4.9682541 out of 5

Keywords: sql server, correlated, subquery, sub query, subqueries, sub queries, wise owl

Id: 0ETfzlAQqBQ

Length: 8min 21sec (501 seconds)

Published: Wed Oct 31 2012