Video summary
The video introduces subqueries in PostgreSQL as a powerful tool often misunderstood due to their syntax, explaining that they consist of an outer query and an inner query where the latter acts as a complete statement nested within the former. The primary use case demonstrated is comparing individual values against aggregate results, such as identifying clients with an estimated net worth higher than the average. A key lesson emphasized is that aggregate functions like AVG cannot be used directly in the WHERE clause; instead, subqueries serve as a workaround by calculating the aggregate value first and returning it as a single scalar number that the outer query can then compare against.
Beyond the WHERE clause, the tutorial explores placing subqueries in the SELECT and FROM clauses to handle different analytical needs. When used in the SELECT list, a subquery can calculate an average or sum for the entire dataset and display it as a new column alongside individual records, allowing for direct row-by-row comparisons even when filtering by specific categories like species. Similarly, using a subquery in the FROM clause transforms a complex query into a derived table, effectively creating a temporary virtual table that can be filtered, grouped, or joined just like any standard physical table, which simplifies handling multiple aggregations or complex filtering logic.
The video also covers the IN operator as another versatile application for subqueries, particularly useful for comparing values against lists of data from other tables. By selecting specific columns from a secondary table within a subquery, users can efficiently filter records that match or do not match any values in that list, which is far more efficient than manually typing out long lists of values. The presenter concludes by encouraging viewers to experiment with these techniques hands-on, noting that while the syntax might seem tricky initially, consistent practice with different scenarios will solidify understanding and reveal the immense utility of subqueries in solving complex database problems.
Read the full video transcript
What's going on everybody? Welcome back
to another video. Today we are
continuing our Postgra SQL series and in
this lesson we're going to be looking at
subqueries. Now subqueries are pretty
unique and I think a lot of people
struggle with them just because of the
syntax itself and it's kind of hard to
look at it and think about it and know
what to do. But I'm going to try to
break it down really simply so that by
the end of this video you know
subqueries really well. And we're not
just going to look at subqueries in the
wear clause because that's where I would
say most people think about it. We're
going to look at it in the from, in the
select, and in different scenarios.
Before we begin, I just want to show
this to you. And I think this is kind of
the easiest way to look at a subquery.
And this is kind of the common
terminology for a subquery. It's an
outer query and an inner query. The
outer query looks like this. Select your
columns from your table and then you
have your wear and then you have a
subquery or an inner query and the inner
query is just another full query. Select
this from this where this. So we have a
little query or the inner query inside
of a larger query or the outer query.
That's all a subquery is. But it can be
confusing to know when and where to use
it. So, we're going to walk through some
scenarios and where to put these
subqueries in different parts of your
SQL query. Let's start with a really
common example and it's looking at and
comparing against averages. Now, if I
look at estimated net worth, I might be
over here and I might think, huh, I
wonder who has more money than the
average person. This might be a question
you get in like an interview or you know
your client comes up to you and say I
want to know which of our clients are
spending more money than the average
person. So that's kind of the example
that we're looking at. We want to know
whose estimated net worth is greater
than average. Let's come in here and
we're going to say where and we're going
to do estimated if I can spell this
right net worth
and then we're say is greater than and
this is where we want to just say
average estimated net worth. Right? We
want to say where the estimated net
worth is greater than the average
estimated net worth. But if we run this
it's not going to work. You can't use
aggregate functions in the wear clause.
But we can kind of get around this. This
is where subqueries are so useful. It's
kind of like a cheat code. It's like I'm
not actually using the average estimated
net worth. I'm using a value pulled from
this estimated net worth. And so let's
see how we can write this. I'm going to
come down here. I'm just going to format
it like this so you can see it as like a
completely separate thing. But I this
isn't the exact format I would use.
We're going to say select the average
estimated net worth
and then I'm going to do enter and I'm
going to say from [snorts] character
info. So this is like an absolute cheat
code because I just want to get the
average net worth up here but it won't
let me because you can't use
aggregations. So what this is doing is
this is running this query. It's
evaluating it to a number and then it's
just putting the number here. So in this
section now that's where the number is
going to be. So now if I run this
and that's my bad. Uh we actually need
to put the parenthesis right here. Now
we can run it and these are the people
who have an above average estimated net
worth. And if we just run this by
itself, let's look at what the average
is because this is a full query, a full
statement. It's going to take 2,8,666.
So 2 million, that's a lot. and then it
gets placed right here. So we just say
we're estimated net worth is greater
than that 2,ion8,000.
And these are the people who have that
estimated net worth higher than that
average. Now this isn't typically how I
would actually write it. I might do
something like this
where it looks like this. It's all on
one line so someone can easily read it.
Or I might do it like
this where again it's just easily
readable. And then the next line I'd go
back here and say whoops. And then I
would say something like order by or
something like that. I try to make this
very evident that this is a subquery
because if someone else looks at this
query or I pass this off to a team
member, I want them to be able to really
easily see this is a subquery that we're
using in this query. Now along this same
line of selecting this average estimated
net worth, let's come down here. I'm
going to take this and right now we're
using it in the wear statement or the
wear clause. But we don't have to use it
just in the wear clause. For example,
what if I want to do uh character name?
We'll take the estimated net worth. I'm
not going to write that out. I'll mess
it up. The estimated net worth. And we
also want the average estimated net
worth just like we were looking at
before. We want it Oops, I knew this has
an underscore. We want it all in one
query, right? We're going to run this.
It's not going to work. we'd have to
group by and then we would have to have
these columns as our group by. We don't
want that. We just want to compare their
estimated net worth compared to the
estimated net worth. We can do the exact
same thing that we did up here. In fact,
let's just copy this because I want to
do that. I want to save our time. So, if
I bring this in here and I run it like
this, now we'll bring it over here. Now,
this is a new column with just this
average value. So, we're just placing it
as a number here. And I'll say as
average worth, and I'm butchering the
writing of this. So, now this is its own
new column. Let's go ahead and run this.
Now, we have our character name, our
estimated net worth, and then it's just
a default value for this entire column.
This is the average net worth. So, we
can kind of compare to each person. So
this again is a very common use case.
You don't just have to use in the wear.
You can also use it in the select,
especially if you're using things like
aggregations. It's great to pull that
in. You don't have to just keep it as
this. We could also add filters here,
right? We could add, we just want to
look at comparisons. So we're going to
say where the species is equal to human.
So we're going to say average human_net
worth. And let's run this.
And now this is the average net worth
just for people who are humans. So we
can compare, oh Yoda, he's not a human
or R2-D2, he's not a human, but we can
compare his estimated net worth or its
estimated net worth compared to a
human's average net worth. And so this
subquery can be its own entire query.
You can do tons of stuff in here. As
long as it pulls back just one value,
that's perfect. It doesn't have to be um
a numeric value, by the way. It could
also be a text value, but you can't have
multiple values in here. For example, in
a normal query, we would be able to do
the sum of the estimated net worth.
Right? If we just look at this like
this, we're just going to run the
subquery. We can run that and get two
values. But we cannot run it in the
overall query because now we have two
values that are being placed in the
select and it doesn't know which one to
actually use. It even says here subquery
must return only one column and so that
just won't work. So we'll get rid of
that sum and now it'll work again. Now
another common use and let's bring this
down. Another common use is actually
using it in the from statement. This one
I think is maybe the most trickiest one
for most people because usually you just
pull from a table and that's it. That's
all you do. you're pulling from this
table that is just sitting here as a
table and it's, you know, pretty
straightforward. But when you try to
start using the from, it's a little bit
confusing. All you're doing is you're
creating another table with a query. So
instead of character info, I can specify
what data is going to be in this from in
the table that we're pulling and then I
can query off of it. It's pretty sweet.
Let's just look at a quick example. So
I'm just going to look at our data.
Let's try to keep this one pretty
simple. Let's just say we're going to
take the from and we're going to write
another select in here. So, we're going
to say select and we can write any query
we want. Let's just do species
and then we'll do a comma and we'll do
average estimated net worth, which is
what we've been working with. And then
we'll say from
that's going to be our character_info
table. And then we're going to say group
by species. Now let's just run this
query. So this query has the species and
the average. And when we run it like
this, we're selecting everything from
this table. So it should give us the
exact same output. But now we can come
in here and we can say select everything
and we can filter. So this would also be
something you can do with the having
statement. You can filter on
aggregations. But now we're just
filtering with a wear statement because
we're selecting from this table. This is
our table now. So since this is our
table, we're not aggregating anything in
the select with a group by. We can just
filter on this. We're going to say where
the average in fact let's name this real
quick. I'm going to say as average
worth, we'll say where the average worth
is greater than uh let's do 50,000.
And we'll run it just like this. And so
now we're able to filter down this sub
query that we ran. We can filter it down
just like this is our table. We can kind
of pretend it's over here. It's a table
that we're using. And then we can select
it. We can group on it. We can use where
order by all these different things just
like a normal query. So that's one that
I think trips a lot of people up. It can
be a little bit confusing at first, but
it's super super powerful and very
useful. Now let's look at another
example. And this is going to be
completely different than what we've
been looking at uh before. I'm just
going to say select everything and we'll
come right here. We're just looking at
our table. So, we're selecting from our
character info and we have this species.
But in another table, if I pull this up
extra,
we have another table as well. Now, what
if I wanted to compare? I want to say,
okay, this is our original table. This
is our extra table. I want to know what
species are in this table but are not in
this table. We can do that. So, all we'd
have to say is select everything from
the character info. And then we're going
to say where the species and we're going
to do a special command. That's going to
be in. Now, this in command or this in
statement is really great because it
doesn't just read in one value. It reads
in many values. And so in this subquery,
we're going to say select species. And I
would actually probably do this on the
next line over like this if I were to
structure it like this. Then I would say
from and now we're going to say the
other table. So we're going to say
character info extra. Right now we're
actually doing the opposite of what I
just said, but I'm going to change in a
second. But we're selecting everything
from character info where the species
right here is also in the character info
extra which is this right here. So if
there's a match from the character info
table and the character info extra, it
should be in our output. Let's go ahead
and run this. And now you can see we
filtered it down. There's human, droid,
and human. Those are the only ones that
were in both character info and species.
But we can also say not in. So now we're
looking for the species in our character
info that was not in this other table.
Let's go ahead and run this. And now
these are the ones that are only in
character info and they are not in
character info extra. So we have Wookie,
Unknown, Zrack, and Gungan. These are
all unique only to character info. This
in and not in is really powerful. And
you don't even have to use necessarily a
subquery here. You could also specify
this with values. Oops, let me get rid
of this. I could just say where it's
human or droid, right? And I could write
that out and I could type it. But that
would take forever, right? Imagine data
with thousands of different species.
Then that would take forever to handw
write that out. And so using this
subquery, you're able to save yourself
an immense amount of time comparing
these two tables. Now, I know we covered
a lot in subquery. We used it in the
select statement, in the from statement,
in the where statement. And hopefully,
you were able to understand subqueries a
lot better. I'm going to challenge you.
I want you to keep testing this out.
Keep trying it out. Test different
things and see what works and what
doesn't work. That's how you learn. You
just got to get hands-on. You got to
test it out. And you can learn
subqueries really well just by using
this data set right here. With that
being said, I hope that you learned
subqueries really well and that you
enjoyed this lesson. If you did, be sure
to like and [music] subscribe, and I
will see you in the next video.