Detailed Notes
1. Where clause part 1 - simple desc horses select distinct age from horses;
select name,breed,age,dt_created,dt_updated from horses where age=15 and dt_created [greater than] '2022-06-01'
select name,breed,age,dt_created,dt_updated from horses where age=15 and dt_created [greater than] year('2022-06-01 12:00:00');
select name,breed,age,dt_created,dt_updated from horses where age=15 and dt_created [greater than] '2022';
select name,breed,age,dt_created,dt_updated from horses where age=15 and month(dt_created) [greater than] month('2022-06-01');
select month(dt_created),month('2022-01-06') from horses where age=15 and dt_created [greater than] '2022';
select name,breed,age,dt_created,dt_updated from horses where age=15 and day(dt_created) [greater than] day('2022-05-28');
select name,breed,age,dt_created,dt_updated from horses where age=0 or age=16 and dt_created [greater than] '2022-04-18'
select name,breed,age,dt_created,dt_updated from horses WHERE (age=0 OR age=16) AND (dt_created [greater than] '2022-04-18' OR dt_updated[greater than] '2022-04-18');
Transcript Text
[Music] well hello and welcome back we are continuing our sql maria db tutorials and we're continuing looking at where clauses now last time around we looked at uh sort of some basic ones we did get into ands and oars but now i want to play around a little differently with this one get a little more complex so we're working with this horse's table and let's say um let's do let's do select distinct weight from horses so we've got a whole bunch um so looks like uh let's do this so now what we want to do is uh i don't want to do this i want to do this let's do this first so now what we can do is we can do like our normal select star from horses where let's say weight is less than uh 100. so we're going to start with that simple where clause so there we've got this nice complicated result set now what we can do we can simplify it and we're going to say now what we're going to do is we're going to take all of those ids and so now you're going to get this now this is sort of a manufactured example but that's okay so now i'm going to do this and what i can do is in my where clause is i can say select star from horses where uh let's do let's see what else we want let's pick another field just cuz um oh let's do our ages okay so let's put their age greater than uh greater than 10. okay so we could do where age is greater than 10 and we're going to get a whole bunch of rows back i'm sure if we do that so if we do that there we got 1 000 but what we can also do now we could just do which is what we're normally going to do so this little manufacturer but normally we could do is we could say where age is greater than 10 and weight is less than 100. and then we're sign we're set to go get 1241 rows but let's say that it wasn't those are like a maybe it's a field in another table or something like that or we had to double join it so what we could also do is we could say oh let's just keep that one there and instead we're going to come down here and we're going to say and id in this is like i said it's a little backwards way to do it you would not normally do this but you would if this inner select or if this select here was off of another table quite possibly so now if i do that and that took 0.433 seconds let's see what it takes to do this a little longer because it's doing some funky stuff but we still got the same number of rows back and so when you looked at tables did we have one it's like we have uh owners so we can take our owner's table uh let's describe that so we have an owner id and a sire and then we have in horses i think it's called an owner id yep an owner id so we could do select star from owner where owner id in so let's get all the owners of horses this is not necessarily the only way you could do it but we could come in here and say let's go oh that's right so now we're going to select own rd from horses where weight is less 100 and we're going to get the owner information from them uh oh it's owners my mistake oh semicolon and now we're getting the owner information based on whether they have a horse where the race where the weight is less than 100. so we can actually put some pretty complicated stuff into our where clause we can actually sort of combine now you probably for the most part do not want to do that you want to use inner joins but there are times where it makes sense to have a select in the in the where clause um sometimes it's actually and we've i think we've actually looked at this a little bit before so there's a couple ways to break this down and this is really getting a little bit in performance tuning and stuff but that's okay now what we ideally what we would do is select star from well let's say uh call owners o so what we really want is we want to select oh that's not it's a zero no no oh dot star from owners inner join uh horses on uh let's make that horses.h on h dot owner id equals o dot id and we can actually do it here and we can say and h dot weight less than 100. and now we don't even have a where clause at all and we should get 356 rows back whoops uh oh dot id enter join horses h on oh it's owner id i'm sorry it's not id it is owner id so if we do that boom then we get our 356 rows back so now we're dealing with an inner join another way we can do this is we can say let's do the o from owners again and we can actually do we can take this select uh let's see like this and we're going to call it uh oq for the owner's query on oq dot owner id equals h uh yeah o dot owner id oops i should spell it right let me put some semicolons here to save a few people if you're looking at these in the notes and so now i get the same thing now i don't know if this went faster uh that was 0.13 this is 0.08 so it actually went faster this way uh which is interesting because actually what it did is um it builds this first it goes to that inner piece it's going to build it first and so it's got a much smaller amount to uh to join to then he uh here oh where's the entire horses table and it's substantially different because now this is joining to the horse table and then comes in and handles the that you could also do this and i can say where weight is less than 100 where if i do that let's see how fast that comes back that's 0.103 so that's even not as fast as the other one was so you've got a couple of different ways you can move around stuff uh between interselects or inner joins and selects and putting those selects into your um into your where clause now for example one of the things we might want to do is let's say select star what we want to do is let's say we want to get all of the horses where age equals minimum age now if you try to do that because you want it to be the minimum right so i'm going to say i just want to give everybody where it's the minimum age and the table well it's not going to like that because you have to have a group by and we've talked a little bit about those so what you really want to do is you need to say select uh minimum age from horses so if i do that and let's say as age so i can just do that or i can do select uh let's say yeah that's probably going to want to do i'm trying to think a little bit how i do that [Music] yeah that's probably the easiest way to do that i'm sorry i'm thinking a little bit on the fly so now what i can do and this would be a case where i'd want to do it is i can say select age comma weight from horses where age equals and so now or actually it probably should be age and i guess it doesn't matter if i do that now i'm gonna see all of these guys with their uh age and weight so that negative one so let's say uh max and so here's a little more typical kind of example is that what i'm trying to do is get a grouping together and there's most of them there now that's only one and so he doesn't weigh anything even though his age of 16. so in this case i'm i'm doing some of my uh my maxes my groups groupings and things like that in that select and then using that as part of the where clause and particularly if you're using mins and max that doesn't really it's really not going to make sense to do the inner join although it probably could be done i'm not sure how fast it's going to run but from a readability point of view what you're going to see here is if people look at it and they'll say oh i'm going to select my values from my table like this and then my where clause is oh okay where my age is select max age and this sometimes a way to build a craw if you're trying to sort of like join across a or not join but whittle down your results across a lot of different tables sometimes it's easier to get that join that select in the where clause as opposed to or i guess you can do an interjoint with it as well but use those queries to sort of get yourself to a a smaller result set first and then work from there which you could do you could do it in like a stored procedure or a function or something like that but sometimes that doesn't it doesn't lend itself to that as well so you want you know maybe you've got it you're for some reason restricted to a query so you can do it like this and you can give yourself sometimes a pretty complex query but you can include uh with that some of these uh you know some other selects and some other tables and you can even get complex with that because you can actually have a i mean you have a full-blown um so like if i did if i do o dot id uh sorry that's owner id so i could take this and that's going to give me this i can't remember how many results this gives me back okay that gives you 356 rows back so what i could actually then turn around and do is say select star or let's just do uh age comma weight comma breed from horses where our id in uh this big massive thing watched it this way so it's just easier to see and i'm also gonna do um let's say we're age less than 10. and and i'm just adding random stuff at this point but no i've got like some that this is getting pretty nasty i've got a pretty gnarly uh oh it's not h it's h comma because i've got this really i'm starting to get a pretty complex thing in my where clause not recommended uh sometimes those things are i don't know if they're you have to do it but sometimes you do so now we're we've been adjusting this a bit but you're gonna see all your ages here are gonna be over here in this left column we're now less than 10. so you can get like i said you can get a little uh interesting with your where clauses and it's usually a matter of either the energy uh the using the n uh here like where owner id in but if it's a single number i can do equals like where did i do my match yeah so i can also do from this one the problem is you got to make sure what your result set is so if i go where h equals because i only get one then this will work and it'll probably come back a little faster than it did let's see 0.341 versus oh that may have been cached a little bit so um but you can also get but so you can do equals but if you try to do equals where you're gonna get multiples so if i said here uh let me leave that there i'm gonna break it so now if i say he equals that or she i guess doesn't matter what its gender is i can come in and it's going to say here that sub query returns more than one row because i'm saying owner id equals but this thing this big ugly thing returns lots of rows 356 rows so if you've got if you if you know you're only going to have one row then you're okay if you are going to have some number of rows or you can okay with equal if you're going to have a large number of rows or actually more than one row then go ahead and make it n i n and then the challenge becomes what if it doesn't exist so if i take let me do one let's go back to a simpler one so let's do select from owners so select owner id from horses where weight is less than uh negative one so let's look at that first this we don't get anything back so now let's see what happens when we try to do the owner id in and so we're also going to get nothing back so in this case we don't get any rows but yes because we can't it's not in there but we don't get an error or anything like that it's just going to say oh there's nothing left it's still going to run all this so technically yeah you may want to do like in those cases you're probably better off to do an inner join um and of course if you start messing around the where clause then it's sometimes gonna be easier to join and then do like an inner or left join as opposed to um some sort of you know equals or in in your query i think we've gone far enough into our where clauses this time and so we'll come back we'll explore some other fun stuff around these as interesting stories come up and interesting approaches come up to do some of our queries and some of our tuning and things of that nature but for now we'll let you get back to it so go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our sql maria db
tutorials and we're continuing looking
at where clauses
now last time around we looked at uh
sort of some basic ones we did get into
ands and oars but now
i want to play around a little
differently with this one get a little
more complex so we're working with this
horse's table
and
let's say
um let's do let's do select distinct
weight
from horses
so we've got a whole bunch
um
so
looks like uh
let's do this
so now what we want to do
is
uh i don't want to do this i want to do
this
let's do this first
so now what we can do is we can do like
our normal select star
from horses
where
let's say
weight
is less than
uh 100.
so we're going to start with that simple
where clause
so there we've got this nice complicated
result set now what we can do we can
simplify it and we're going to say now
what we're going to do is we're going to
take all of those ids
and so now you're going to get this
now this is sort of a manufactured
example but
that's okay
so now i'm going to do this and what i
can do is in my where clause is i can
say select star from
horses
where uh let's do let's see what else we
want let's pick another field just cuz
um
oh let's do our ages
okay so let's put their age greater than
uh
greater than 10. okay
so we could do where age is greater than
10
and we're going to get a whole bunch of
rows back i'm sure if we do that so if
we do that there we got 1 000 but what
we can also do now we could just do
which is what we're normally going to do
so this little manufacturer but normally
we could do is we could say where age is
greater than 10
and
weight is less than 100.
and then we're sign we're set to go get
1241 rows but let's say that it wasn't
those are like a maybe it's a field in
another table or something like that
or we had to double join it so what we
could also do
is we could say oh let's just keep that
one there
and instead we're going to come down
here and we're going to say and
id in
this is like i said it's a little
backwards way to do it you would not
normally do this but you would
if this inner select or if this select
here was off of another table quite
possibly so now if i do
that and that took 0.433 seconds let's
see what it takes to do this a little
longer because it's doing some funky
stuff but we still got the same number
of rows back
and so when you looked at tables
did we have one
it's like we have uh owners
so we can take our owner's table
uh let's describe that
so we have an owner id and a sire and
then we have
in horses i think it's called an owner
id yep an owner id so we could do
select
star from
owner
where
owner id
in
so let's get all the owners of horses
this is not necessarily the only way you
could do it but
we could come in here and say let's go
oh that's right so now we're going to
select own rd from horses where weight
is less 100 and we're going to get the
owner information from them
uh oh it's owners
my mistake
oh semicolon and now we're getting the
owner information based on whether they
have a horse where the race where the
weight is less than 100. so we can
actually put
some
pretty complicated stuff into our where
clause we can actually sort of combine
now
you probably
for the most part do not want to do that
you want to use inner joins
but
there are times where
it makes sense to have a select in the
in the where clause um sometimes it's
actually
and we've i think we've actually looked
at this a little bit before so there's a
couple ways to break this down and this
is really getting a little bit in
performance tuning and stuff but that's
okay now what we ideally what we would
do is select star from well let's say uh
call owners o
so what we really want is we want to
select oh
that's not it's a zero no no oh dot star
from owners
inner join
uh horses
on
uh let's make that horses.h
on
h dot owner id equals o dot id
and we can actually do it here and we
can say and
h dot weight
less than 100.
and now we don't even have a where
clause at all
and we should get 356 rows back
whoops uh
oh dot id
enter join horses h on
oh it's owner id i'm sorry it's not id
it is owner id
so if we do that boom then we get our
356 rows back so now we're dealing with
an inner join
another way we can do this
is
we can say
let's do the o from owners again
and we can actually do we can take this
select
uh let's see like this and we're going
to call it uh
oq for the owner's query
on
oq dot
owner id equals h uh
yeah o dot owner id
oops i should spell it right
let me put some semicolons here to save
a few people if you're looking at these
in the notes
and so now i get the same thing now i
don't know if this went faster
uh that was 0.13 this is 0.08 so it
actually went faster this way
uh which is interesting because actually
what it did
is um
it builds this first it goes to that
inner piece it's going to build it first
and so it's got a much smaller amount to
uh to join to then he
uh
here oh where's the entire horses table
and it's
substantially different
because now this is joining to the horse
table and then comes in and handles the
that
you could also do
this and i can say where weight is less
than 100 where
if i do that let's see how fast that
comes back that's 0.103 so that's even
not as fast as the other one was
so you've got a couple of different ways
you can move around stuff
uh between interselects
or inner joins and selects and putting
those selects into your
um
into your where clause now for example
one of the things we might want to do is
let's say
select star
what we want to do is let's say we want
to get
all of the horses
where
age equals
minimum age
now if you try to do that because you
want it to be the minimum right so i'm
going to say i just want to give
everybody where it's the minimum age and
the table well it's not going to like
that because
you have to have a group by and we've
talked a little bit about those
so what you really want to do is you
need to say select
uh minimum
age
from horses
so if i do that and let's say
as age
so i can just do that
or i can do
select uh let's say
yeah that's probably going to want to do
i'm trying to think a little bit how i
do that
[Music]
yeah that's probably the easiest way to
do that i'm sorry i'm thinking a little
bit on the fly so now what i can do
and this would be a case where i'd want
to do it is i can say select
age comma weight
from horses where
age equals and so now
or actually it probably should be age
and i guess it doesn't matter
if i do that now i'm gonna see all of
these guys
with their uh
age and weight so that negative one so
let's say
uh max
and so here's a little more
typical kind of example is that what i'm
trying to do is
get a grouping together and there's most
of them
there now that's only one and so he
doesn't weigh anything even though his
age of 16.
so in this case i'm i'm doing some of my
uh my maxes my groups groupings and
things like that in that select
and then using that as part of the where
clause and particularly if you're using
mins and max that doesn't really
it's really not going to make sense to
do the inner join although it probably
could be done
i'm not sure how fast it's going to run
but from a
readability point of view
what you're going to see here is if
people look at it and they'll say oh i'm
going to select my values
from my table
like this
and then my where clause
is oh okay
where my age is select max age
and this sometimes a way to build a craw
if you're trying to sort of like join
across a or not join but
whittle down your results across a lot
of different tables sometimes it's
easier to get that join
that select in the where clause
as opposed to or i guess you can do an
interjoint with it as well but use those
queries to sort of get yourself to a
a smaller result set first
and then work from there which you could
do
you could do it in like a stored
procedure or a function or something
like that but
sometimes that doesn't
it doesn't lend itself to that as well
so you want you know maybe you've got it
you're for some reason restricted to a
query
so you can do it like this and you can
give yourself
sometimes a pretty complex query
but you can include uh with that some of
these uh you know some other selects and
some other tables
and you can even get complex with that
because you can actually have a i mean
you have a full-blown
um
so like if i did
if i do o dot id uh sorry that's
owner id
so i could take this
and that's going to give me this i can't
remember how many results this gives me
back okay that gives you 356 rows back
so what i could actually then turn
around and do is say
select star
or let's just do
uh age comma weight comma breed
from horses
where
our id
in
uh this big massive thing
watched it this way so it's just easier
to see
and i'm also gonna do
um let's say we're age less than 10.
and and i'm just
adding random stuff at this point but no
i've got like some
that this is getting pretty nasty i've
got a pretty gnarly
uh oh it's not h it's h comma
because i've got this really i'm
starting to get a pretty complex thing
in my where clause not recommended
uh sometimes those things are i don't
know if they're
you have to do it but sometimes you do
so now we're
we've been adjusting this a bit but
you're gonna see all your ages here
are gonna be over here in this left
column we're now less than 10.
so you can get
like i said you can get a little uh
interesting with your where clauses and
it's usually a matter of either the
energy uh the
using the n uh
here like where owner id in
but if it's a single number i can do
equals like where did i do
my match yeah so i can also do
from this one
the problem is you got to make sure what
your result set is so if i go where h
equals
because i only get one
then this will work
and it'll probably come back a little
faster than it did let's see 0.341
versus
oh that may have been cached a little
bit so
um but you can also get but so you can
do equals but if you try to do equals
where you're gonna get multiples so if i
said here
uh let me leave that there
i'm gonna break it
so now if i say he equals that
or she i guess doesn't matter what its
gender is i can come in and it's going
to say here that sub query returns more
than one row because i'm saying owner id
equals but this thing
this big ugly thing
returns lots of rows 356 rows so if
you've got if you if you know you're
only going to have one row then you're
okay
if you are going to have
some number of rows or you can okay with
equal if you're going to have a large
number of rows or actually more than one
row then go ahead and make it n
i n
and then
the challenge becomes what if it doesn't
exist
so
if i take
let me do one let's go back to a simpler
one
so let's do select from owners so select
owner id from horses where weight is
less than
uh negative one
so let's look at that first
this we don't get anything back so now
let's see what happens when we try to do
the owner id in
and so we're also going to get nothing
back
so in this case we don't get any rows
but
yes because we can't it's not in there
but we don't get an error or anything
like that it's just going to say oh
there's nothing left
it's still going to run all this
so technically
yeah you may want to do like in those
cases you're probably better off to do
an inner join
um and of course
if you start messing around the where
clause then
it's sometimes gonna be easier to join
and then do like an inner or left join
as opposed to
um some sort of you know equals or in in
your query
i think we've gone far enough into our
where clauses this time and so
we'll come back we'll explore some other
fun stuff around these as
interesting stories come up and
interesting approaches come up to do
some of our queries
and some of our
tuning and things of that nature but for
now we'll let you get back to it so go
out there and have yourself a great day
a great week and we will talk to you
next time
you