SQL Server Queries Part 11 - Subqueries

welcome to this YSL tutorial in this session we're going to teach you all about how to use subqueries in Microsoft sequel server we'll start by showing you what a sub-query actually is and then explain how you can use them in both the where clause and the Select list of an outer query we'll talk about how to add criteria to a sub query to limit the set of results it returns and finally how you can use a sub query that returns more than one value so let's get started in sequel server a sub query is simply one query nested inside another usually in the select from all where clause as a simple first example here we've got a basic query which returns a single value it tells me the highest number of Oscar wins from a table of films if I execute the query I can see that the single value returned is the number 11 unfortunately with this basic query I can't see any further details of what the film names are the film's release dates are any of the details of the films based on this value that's what I'm going to do is use this as a sub query to find out the extra details of the film the first thing I'm going to do is wrap this query up in a set of round brackets so I'll open a set of round brackets before the Select keyword and then at the end of that query and this will perform part of the where Clause of what will become my outer query so let's start building that I'm going to select some details say the film name and the film release date perhaps from my table of films and then in the where clause I'm going to ask where the film Oscar wins is equal to and this is where my sub query comes in I already know that this query that turns the value of 11 so by writing a query like this is literally asking me to show all films by the Oscar wins value is equal to 11 if I execute the query I'll find that I get two films in the output so there's a simple first example of how to use a sub query you'll find that aggregate functions tend to get used a lot in sub queries so here's another example using the AVG or average function if I ask you this query by itself I'll see that it returns the value 126 which is the average running time in minutes of all of my films I'm once again gonna turn this into a sub query by wrapping it up into a set of round brackets and then building the rest of my query around it I'm gonna select the film's name and film run time in minutes just so you can check that the valleys are being returned correctly from my table films where the film run time in minutes is I'm gonna go for greater than or equal to the value returned by my sub query just because I'm going to be pedantic now I'm going to indent that sub query twice and then execute the query to find a list of all of the films that are longer than the average it's not too big a leap of the imagination hopefully to convert that into a less than or equal to the average running time so there we go you can also use a sub-query in the Select list of an outer query and I'm going to do it in this example to show the average films running time in minutes as an extra column in the result set so to do that I start by just as I would ordinarily add a new column to my select list add a comma to the end of the current list and then I'm simply going to select copy and paste my sub query it will be nice to have an alias so that there column is labeled I'm gonna call this a average I can spell average average run time and when I execute the query this time I'll have the same sub query being used in both the Select list and the where clause so far we've seen a couple of examples of using a sub-query in the where clause of an outer query but things get even more interesting when your sub query itself contains a where clause so here's a straightforward query which shows me the highest film budget from all films released before the year 2000 if I execute the query again I get a single fairly high value but again I can use this to form part of the sub query everything I've written there so far apart from the use statement he's going to become my sub query and what I'd like to do is show a list of all of the films whose budget was greater than the most expensive film released before the year 2000 so I can do that by building up my select list again I'm gonna include the film name and the film release date and I'll include the film budget as well film budget dollars so we can see the values are appearing correctly I'm gonna say that from my film table and I'm going to ask where the film budget dollars is greater than the value that's calculated by my sub query so really all we're asking you to show this information from the film table where the film budget is greater than hundred million or so I've just in dundies this sub-query a couple of times when I execute the query now I'll see that there are three films in the database that are more expensive or were more expensive than the most expensive film before the year 2000 a sub-query doesn't always have to contain an aggregate function here's an example that returns the release date of a specific film if I execute the query I can see that the sub query or the query as it is at the moment returns a specific date if I wanted to find out if any other films have been released on the same date but I can simply turn this into a sub query again by wrapping a set of round brackets around it and then selecting several fields I'm gonna go for the film name and the film release date from my table of films where the film release dates is equal to the result of the sub query just being pedantic again in the sub query and if I execute the whole thing I'll see a list of all the films whose release date is the same as casino so far all of our sub queries have returned just a single value but it is possible to use sub queries that return a set of values so in this example I'm showing a list of all of the direct IDs where the director was born in the year 1946 if I execute the query I get that list of ID numbers what I can then do is show a list of films made by that list of directors and in order to do that I'm gonna wrap up this query instead of round brackets and this will become a sub query again so now I'm going to select the film I say the film name and the film director ID from my table of films where the film director ID is any ID that is not equal to but in the list returned by this sub-query so it's important that I'm not using the equals sign here I'm using the keyword in if I execute the query now I'll see a list of all of the films made by the list of Directors returned by that sub query so I'll certainly I'll recognize Steven Spielberg at least and John Woo I'm not so sure about the other ones for me this is one of the examples or example of a slight overuse of sub queries I think we're I doing this myself in the real world I'd be more tempted to join the film table to the director table and then I've got a much easier way to write my where clause but it's still useful to know that you can use this form of sub query in the real world if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 118,724

Rating: 4.9303589 out of 5

Keywords: sql server, subquery, subqueries, select, where, criteria, wise owl

Id: 5KXbdkv9hEM

Length: 8min 51sec (531 seconds)

Published: Wed Aug 22 2012