Subqueries

Quick reference

• 00:04 In this video, I wanna talk about sub-queries.
• 00:07 And a sub-query is a query inside of another query.
• 00:11 It's a select statement inside of another select statement.
• 00:14 So this just lets you drill down and
• 00:17 become more specific with the information that you retrieve.
• 00:21 And it gives you some options that you might not have otherwise.
• 00:23 So let's create a simple select statement.
• 00:26 And let's pull first name and
• 00:31 last name from our current customers table.
• 00:35 So we just have John Elder, Mark Robertson, blah, blah, blah.
• 00:38 So we have first and last name.
• 00:40 So now, what if we want to do another select statement?
• 00:44 And let's call this Customer ID and From.
• 00:52 Actually, I'm just gonna copy that.
• 00:57 And if we execute both of them, we have just a list of all the customer IDs.
• 01:01 So let's say we wanna narrow this down.
• 01:03 We want where, Customer ID is greater than what?
• 01:11 Four, let's say?
• 01:13 So let's execute this.
• 01:14 And now we get 5, 6, and 7 for our Customer IDs.
• 01:18 So we're listing first and last names in the first query, and
• 01:21 customer IDs greater than 4 in the second query.
• 01:24 So, what if we want to merge these together and make one big query?
• 01:28 Well, we can do that with sub-queries.
• 01:29 And to do a sub-query, all you do is wrap your inner query in parenthesis and
• 01:35 then add it to your first query.
• 01:37 And sub-queries can go in just about anywhere.
• 01:40 They can go in the select statement.
• 01:42 They can go in the from statement.
• 01:43 They can go in aware.
• 01:45 Lots of different places.
• 01:46 So we're gonna use aware, and we're just gonna paste that in.
• 01:50 Now, we need to do a couple other things here.
• 01:52 We need to specify where this should go.
• 01:56 So where, since we're talking about customer IDs,
• 02:01 where the customer ID in this exists.
• 02:06 So let's execute this thing.
• 02:08 And now we just have three results.
• 02:12 John Smith, Julia Styles, and Tracy Smitherton.
• 02:14 If we come up here to this top line, if we add in our Customer ID.
• 02:21 Just so we can see what's going on here.
• 02:23 We see that these customers are customers with Customer ID 5, 6, and 7.
• 02:28 Which is exactly what we're looking for.
• 02:30 If we want to say Customer IDs less than 4, we get 1, 2, and 3.
• 02:35 If we want Customer ID equal to 4, we get Steve Jensen.
• 02:40 So, I could give you a zillion different examples of nesting queries and
• 02:44 sub-queries together, but just be aware that it's possible to nest,
• 02:48 to have sub-queries inside of other queries,
• 02:50 to have select statements inside of other select statements.
• 02:53 And you could see the inner query, it takes the exact same form.
• 02:57 We just copied and pasted what we originally had as our stand alone query.
• 03:01 It didn't change anything inside of it.
• 03:03 It just has the same format.
• 03:04 The first line is the select line, the second line is the from,
• 03:07 the third is the where.
• 03:08 Just like any other query, any other select statement you would ever do.
• 03:12 We just swapped an in to our where in our first query.
• 03:15 So, that's sub-queries.
• 03:17 In the next video, we'll look at indexing.

Lesson notes are only available for subscribers.