- 22 hours ago
Subqueries in PostgreSQL can feel confusing at first, but this lesson breaks them down into clear, practical examples you can actually use. The video explains the difference between an outer query and an inner query, then shows how subqueries work in the WHERE, SELECT, and FROM clauses.
You’ll see how to compare estimated net worth against an average, pull an aggregate value into a result set, and filter data by species using IN and NOT IN. The lesson also shows why subqueries are useful when you want to compare rows, create a temporary result set, or avoid writing long manual lists.
This is a hands-on PostgreSQL tutorial for learners who want to understand SQL subqueries, nested queries, aggregate functions, filtering, and query building in a simple way. It’s especially useful for beginners, interview prep, and anyone practicing PostgreSQL with real examples.
Created for anyone searching for PostgreSQL subqueries, SQL nested queries, subqueries in WHERE clause, subqueries in SELECT, subqueries in FROM, IN and NOT IN SQL, and PostgreSQL tutorial for beginners. Great for study, interview practice, and building confidence with SQL query logic.
You’ll see how to compare estimated net worth against an average, pull an aggregate value into a result set, and filter data by species using IN and NOT IN. The lesson also shows why subqueries are useful when you want to compare rows, create a temporary result set, or avoid writing long manual lists.
This is a hands-on PostgreSQL tutorial for learners who want to understand SQL subqueries, nested queries, aggregate functions, filtering, and query building in a simple way. It’s especially useful for beginners, interview prep, and anyone practicing PostgreSQL with real examples.
Created for anyone searching for PostgreSQL subqueries, SQL nested queries, subqueries in WHERE clause, subqueries in SELECT, subqueries in FROM, IN and NOT IN SQL, and PostgreSQL tutorial for beginners. Great for study, interview practice, and building confidence with SQL query logic.
Category
📚
LearningTranscript
00:00What's going on, everybody? Welcome back to another video. Today, we are continuing our
00:03PostgreSQL series, and in this lesson, we're going to be looking at subqueries. Now, subqueries
00:08are pretty unique, and I think a lot of people struggle with them just because of the syntax
00:13itself, and it's kind of hard to look at it and think about it and know what to do, but
00:18I'm going to try to break it down really simply so that by the end of this video, you know
00:22subqueries really well, and we're not just going to look at subqueries in the where clause
00:26because that's where I would say most people think about it. We're going to look at it
00:30in the from, in the select, and in different scenarios. Before we begin, I just want to
00:35show this to you, and I think this is kind of the easiest way to look at a subquery, and
00:40this is kind of the common terminology for a subquery. It's an outer query and an inner
00:44query. The outer query looks like this. Select your columns from your table, and then you
00:49have your where, and then you have a subquery or an inner query, and the inner query is just
00:56another full query. Select this, from this, where this. So we have a little query, or the
01:02inner query, inside of a larger query, or the outer query. That's all a subquery is, but
01:10it can be confusing to know when and where to use it, so we're going to walk through some
01:14scenarios and where to put these subqueries in different parts of your SQL query. Let's
01:20start with a really common example, and it's looking at and comparing against averages.
01:26Now, if I look at estimated net worth, I might be over here, and I might think, huh, I wonder
01:32who has more money than the average person. This might be a question you get in like an
01:37interview, or your client comes up to you and say, I want to know which of our clients are
01:41spending more money than the average person. So that's kind of the example that we're looking
01:46at. We want to know whose estimated net worth is greater than average. Let's go in here,
01:51and we're going to say where, and then we're going to do estimated, if I can spell this
01:56right, net worth. And then we're going to say is greater than, and this is where we want
02:03to just say average estimated net worth, right? We want to say where the estimated net worth
02:08is greater than the average estimated net worth. But if we run this, it's not going to work.
02:14You can't use aggregate functions in the where clause. But we can kind of get around this. This
02:21is where subqueries are so useful. It's kind of like a cheat code. It's like, I'm not actually
02:26using the average estimated net worth. I'm using a value pulled from this estimated net worth.
02:33And so let's see how we can write this. I'm going to come down here. I'm just going to format
02:37it like
02:37this so you can see it as like a completely separate thing. But this isn't the exact format I would
02:43use.
02:43We're going to say select the average estimated net worth. And then I'm going to do enter.
02:51And I'm going to say from character info. So this is like an absolute cheat code because I just want
02:59to get the average net worth up here, but it won't let me because you can't use aggregations.
03:03So what this is doing is this is running this query. It's evaluating it to a number,
03:10and then it's just putting the number here. So in this section now, that's where the number is going
03:14to be. So now if I run this, and that's my bad, we actually need to put the parentheses right
03:21here.
03:22Now we can run it. And these are the people who have an above average estimated net worth.
03:28And if we just run this by itself, let's look at what the average is because this is a full
03:32query,
03:32a full statement, it's going to take 2,008,666. So 2 million, that's a lot. And then it gets
03:41placed
03:41right here. So we just say we're estimated net worth is greater than that 2,008,000. And these are
03:47the people who have that estimated net worth higher than that average. Now this isn't typically how I
03:53would actually write it. I might do something like this, or it looks like this. It's all on one line.
04:00So someone can easily read it. Or I might do it like this, where again, it's just easily readable.
04:08And then the next line, I'd go back here and say, whoops. And I would say something like order by
04:13or something like that. I try to make this very evident that this is a sub query, because if
04:18someone else looks at this query, or I pass this off to a team member, I want them to be
04:22able to
04:23really easily see this is a sub query that we're using in this query. Now along this same line
04:29of selecting this average estimated net worth, let's come down here, I'm going to take this.
04:34And right now we're using it in the where statement or the where clause. But we don't have to use
04:39it
04:39just in the where clause. For example, what if I want to do character underscore name,
04:44we'll take the estimated net worth, I'm not going to write that out, I'll mess it up,
04:48the estimated net worth. And we also want the average estimated net worth, just like we were looking
04:54at before, we want it, oops, I need this as an underscore. We want it all in one query, right?
05:00We're going to run this, it's not going to work, we'd have to group by. And then we would have
05:05to
05:05have these columns as our group by, we don't want that, we just want to compare their estimated net
05:10worth compared to the estimated net worth. We can do the exact same thing that we did up here.
05:16In fact, let's just copy this, because I want to do that, I want to save our time.
05:21So if I bring this in here, and I run it like this, now we'll bring it over here. Now
05:28this is
05:28a new column with just this average value. So we're just placing it as a number here. And I'll say
05:35as average worth, and I'm butchering the writing of this. So now this is its own new column. Let's
05:42go ahead and run this. Now we have our character name, our estimated net worth, and then it's just
05:48a default value for this entire column. This is the average net worth. So we can kind of compare
05:55to each person. So this again is a very common use case, you don't just have to use in the
06:00where,
06:00you can also use it in the select, especially if you're using things like aggregations,
06:05it's great to pull that in. You don't have to just keep it as this, we could also add filters
06:11here,
06:12right? We could add, we just want to look at comparisons. So we're going to say where the
06:16species is equal to human. So we're going to say average human underscore net worth. And let's run
06:25this. And now this is the average net worth just for people who are humans. So we can compare, oh,
06:32Yoda, he's not a human or R2D2, he's not a human. But we can compare his estimated net worth or
06:38its estimated net worth compared to a human's average net worth. And so this subquery can be
06:45its own entire query, you can do tons of stuff in here, as long as it pulls back just one
06:51value,
06:51that's perfect. It doesn't have to be a numeric value, by the way, it could also be a text value,
06:57but you can't have multiple values in here. For example, in a normal query, we would be able to do
07:03the sum of the estimated net worth, right? If we just look at this, like this, we're just going to
07:10run the subquery, we can run that and get two values. But we cannot run it in the overall query,
07:17because now we have two values that are being placed in the select, and it doesn't know which
07:23one to actually use. It even says here, subquery must return only one column. And so that just won't
07:29work. So we'll get rid of that sum. And now it'll work again. Now another common use, and let's bring
07:35this down. Another common use is actually using it in the from statement. This one, I think, is maybe
07:42the most trickiest one for most people, because usually you just pull from a table. And that's it.
07:48That's all you do. You're pulling from this table that is just sitting here as a table. And it's, you
07:53know, pretty straightforward. But when you try to start using the from, it's a little bit confusing.
07:58All you're doing is you're creating another table with a query. So instead of character info,
08:04I can specify what data is going to be in this from in the table that we're pulling,
08:09and then I can query off of it. It's pretty sweet. Let's just look at a quick example. So
08:15I'm just going to look at our data. Let's try to keep this one pretty simple. Let's just say
08:20we're going to take the from, and we're going to write another select in here. So we're going to say
08:26select, and we can write any query we want. Let's just do species. And then we'll do a comma,
08:32and we'll do average estimated net worth, which is what we've been working with. And then we'll say
08:41from, that's going to be our character underscore info table. And then we're going to say group by
08:49species. Now let's just run this query. So this query has the species and the average. And when
08:58we run it like this, we're selecting everything from this table. So it should give us the exact
09:04same output. But now we can come in here and we can say select everything and we can filter. So
09:10this
09:11would also be something you can do with the having statement, you can filter on aggregations. But now
09:15we're just filtering with aware statement, because we're selecting from this table, this is our table
09:21now. So since this is our table, we're not aggregating anything in the select with a group by,
09:26we can just filter on this, we're going to say where the average, in fact, let's name this real
09:31quick, I'm going to say as average worth, we'll say where the average worth is greater than, let's do
09:3950,000. And we'll run it just like this. And so now we're able to filter down this sub query
09:47that we ran, we can filter it down just like this is our table, we can kind of pretend it's
09:52over here,
09:53it's a table that we're using. And then we can select it, we can group on it, we can use
09:58where,
09:59order by all these different things just like a normal query. So that's one that I think trips
10:03a lot of people up, it can be a little bit confusing at first. But it's super, super powerful
10:08and very useful. Now let's look at another example. And this is going to be completely different than
10:13what we've been looking at before. I'm just going to say select everything. And we'll come right here,
10:20we're just looking at our table. So we're selecting from our character info. And we have this species.
10:26But in another table, if I pull this up, underscore extra, we have another table as well. Now what if
10:37I wanted to compare, I want to say, okay, this is our original table. This is our extra table. I
10:43want
10:44to know what species are in this table, but are not in this table, we can do that. So all
10:51we'd have to
10:52say is select everything from the character info. And then we're going to say where the species,
10:58and we're going to do a special command, and that's going to be in. Now this in command is in
11:04statement
11:04is really great, because it doesn't just read in one value, it reads in many values. And so in this
11:11sub query, we're going to say select species. And I would actually probably do this on the next line
11:17over like this, if I were to structure it like this, that would say from, and now we're going to
11:21say the other table. So we're gonna say character info underscore extra. Right now, we're actually
11:28doing the opposite of what I just said, but I'm going to change in a second. But we're selecting
11:32everything from character info, where the species right here is also in the character info extra,
11:40which is this right here. So if there's a match from the character info table, and the character
11:45info extra, it should be in our output. Let's go ahead and run this. And now you can see we
11:51filtered
11:51it down. There's human, droid, and human. Those are the only ones that were in both character info
11:58and species. But we can also say not in. So now we're looking for the species in our character info
12:06that was not in this other table. Let's go ahead and run this. And now these are the ones that
12:12are
12:13only in character info, and they are not in character info extra. So we have Wookiee, Unknown,
12:20Zabrak, and Gungan. These are all unique, only to character info. This in and not in is really
12:27powerful. And you don't even have to use necessarily a sub query here. You could also specify this with
12:34values. Let me get rid of this. I could just say where it's human, or droid, right? And I could
12:42write
12:42that out, and I could type it. But that would take forever, right? Imagine data with thousands
12:47of different species, then that would take forever to handwrite that out. And so using this sub query,
12:53you're able to save yourself an immense amount of time comparing these two tables. Now we know we
12:58covered a lot in sub query, we used it in the select statement, in the from statement, in the where
13:03statement. And hopefully you were able to understand sub queries a lot better. I'm going to challenge you,
13:08I want you to keep testing this out, keep trying it out, test different things, and see what works
13:13and what doesn't work. That's how you learn. You just got to get hands on, you got to test it
13:17out,
13:17and you can learn sub queries really well just by using this data set right here. With that being
13:21said, I hope that you learned sub queries really well, and that you enjoyed this lesson. If you did,
13:26be sure to like and subscribe, and I will see you in the next video.