MySQL Database Tutorial - 20 - Subqueries Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to your twentieth
my SQL tutorial congratulations on
making it this far but I will have to
say that you better be ready to get your
mind blown in about one minute because
that's what I'm about to do what I'm
about to teach you guys is about
sub-queries now what a sub-query is is a
query inside another query inception so
it sounds weird it is but it can come in
useful a whole lot so let me go ahead
and show you guys a really quick example
first now the first example I want to
show you guys I have to admit it's
pretty useless in there's a lot easier
way that you can do this but it's the
easiest example I can possibly think of
so say we wanted to do something like we
have all of these items on our website
our eBay website now what we want to do
is we want to list the items whose price
is above average so how do we solve this
problem let's take this step at a time
the first thing that we need to do is we
need to add up the price of all of these
items and get the average once we have
the average price we can use that number
to list all the items that are greater
than that price for example if the
average price of these items was $500 we
would figure that out first and then
once we figured out the average price is
$500 we will list all the items greater
than $500 sounds simple well now we have
to figure out how to do it using
computer code so with MySQL the first
thing that we need to do is figure out
the average of all the items simple
enough let's go ahead and select the
average cost from items now let's go
ahead and copy this and make sure that
you copy it as well because we're going
to need this query later on whenever we
hit go we get the answer of 463 and 93
cents so the average price of all of
these items is 463 dollars so what I
want to point out is we can go ahead and
just write that down on a piece of paper
next to our computers or take a note of
this
this entire query is basically equal to
the value 463 so later on instead of
using the numerical value for 6 3 we can
just go ahead and use this entire query
as well so basically whenever we would
typing the number 4 6 3 we can just go
ahead and type in this query instead and
it'll be the exact same thing so go
ahead and remember to copy or you know
cut that as well so you have that
because we're going to be using that
query later on so now that we have the
average of 463 what we need to do is we
need to list all of the items greater
than that price so let me go ahead and
do that right now
so select name and cost from items where
the cost is greater than and now this is
where we can type 463 just like that and
then we can go ahead and order by cost
descending and whenever you run this
check it out it basically lists all of
the items that are greater than 4 6 3 3
or what we can do is we can run a query
inside a query aka a sub query so
remember how I told you that you can
just go ahead and plug in for 63 right
there well that's nice and all but let's
go ahead and learn how to use sub
queries go ahead and delete that number
and I'm just going to go ahead and add
some white space so this is probably a
little bit easier to see now inside your
parentheses I'll give you guys a bunch
of white space so you can clearly see it
just go ahead and add that query before
so now how my SQL is going to work is
like this the very first thing it's
going to do well let me take a step back
and explain you guys how MySQL works
MySQL runs its queries from the inside
out
the very first thing it figures out is
what's in between its parentheses so
before it runs this entire query it's
going to figure out what's in between
these parentheses first so it's going to
get the average cost from the items
which is 463 and then it's going to
treat this string not as a string
anymore but as the numerical value for
63 so
is going to say okay select the name and
cost from items where cost is greater
than 463 order by cost descending so
it's basically the exact same as the
query ran last time when we specifically
type them the number but check it out
whenever we run it this time
it runs the inside query first and then
the outside query and that's what you
get so if you're saying okay
Bucky first of all this is a really
useless query because instead of having
this you could just go ahead and type in
average cost and that would be the same
well yes it is but then how would I
explain sub queries well I know this is
a useless example and that there are
easier ways to do this but I just want
to show you guys a very simple sub query
and in the next tutorial what I'm going
to do is I'm going to show you guys when
you would actually use a sub query now
that you understand the basics of how
they're used in a useful example so
again I know this example was very
simple and very useless but in the next
tutorial I promise you guys I'll show
you guys when you would use this that
would actually come in handy
and another thing before I show you guys
that example I want to mention this you
know how we just figured out the value
then typed in for 63 whenever you're
running a computer program you just
can't wait for users to figure out
values and then type them in somewhere
else later on they need to do everything
automatically so that's when you would
do this and also if you needed a very
specific value whenever we typed in 463
we rounded a whole bunch of numbers off
whenever you needed a very specific
value this would give you a more
specific value than just copying a
number from some routes so you know I'm
just going to shut up and uh yeah yeah
I'll see you guys in the next video