Detailed Notes
1. Where clause part 2 - inner and sum
select * from horses where weight [less than] 100;
select * from horses where age [greater than] 10 and weight [less than] 100; select id from horses where weight [less than] 100;
select * from horses where age [greater than] 10 and id in (select id from horses where weight [less than] 100);
select * from owners where owner_id in (select owner_id from horses where weight [less than] 100);
select o.* from owners o inner join horses h on h.owner_id = o.owner_id and h.weight [less than] 100;
select o.owner_id from owners o inner join (select owner_id from horses where weight [less than] 100) oq on oq.owner_id=o.owner_id;
select o.* from owners o inner join horses h on h.owner_id = o.owner_id where h.weight [less than] 100;
select * from horses where age = min(age);
select * from owners where owner_id in (select owner_id from horses where weight [less than] -1);
select age,weight from horses where age in (select max(age) as age from horses);
select age,weight,breed from horses where age [less than] 10 and owner_id in (select o.owner_id from owners o inner join (select owner_id from horses where weight [less than] 100) oq on oq.owner_id=o.owner_id);
select age,weight from horses where age in (select max(age) as age from horses);
select age,weight,breed from horses where age [less than]10 and owner_id = (select o.owner_id from owners o inner join (select owner_id from horses where weight [less than] 100) oq on oq.owner_id=o.owner_id);
Transcript Text
[Music] hello and welcome back we are continuing our our sequel story of lessons uh getting into all kinds of little things playing around with my sec my sequel mysql and mariadb this time we're going to get back to some some where clause related stuff so we're going to play around with this and probably the next episode as well so we're going to start with some simpler ones some of which we've seen but to go a little more into those and then um get into some more you know some more complex things so we're going to start with just we'll go back to that horses table because it's a nice big one and it's got a bunch of funky little fields so since we've got it it's easy to look at now um what i did here is went ahead and did a um i've gotta describe so i just did describe horses just to see what wow that just totally blew up on me let me try that again i hadn't seen that before there we go i don't know what happened there but okay so we've got a bunch of different fields over here on the left and typically what you're going to do is you're going to do a select as we've talked about and you can use where's in other places we're going to start with this so you could do like select star from horses and then you're gonna do wear something so first let's do this let's figure out some of what we got so let's do a distinct distinct age from horses so let's see how old these guys are and gals i guess a whole bunch of different ones so we've got some nulls we've got some negatives in there and then a couple of you know small numbers we've got a zero we got a whole bunch of different stuff so let's take a look at uh let's select star from well let's just do uh what do we want to call it name so let's do name comma breed comma age from horses and this is going to be a simple one and we just do where and we take a value so age equals 16. let's do that so there's only one now let's do let's see what do we have with age 15 okay there's a munch there now if we look at let's get a little more complicated so let's look at the uh let's look at the date created for the actu let's add date created and date updated because i want to play around some dates so we're going to go here and we're going to do uh dt created comma dt updated we haven't played a lot with dates but we can see here so we've got created on a whole bunch of different days and updated on you know different days as well not only yeah look like here they're not always the same now if we want to do let's say so these looks like they started on essentially april 15th it was the earliest it was created and june 7th was the latest so what we can do is instead of that we can do well well actually let's do out of this group so now we're going to have we're going to add so we're going to say and and let me go ahead and do it this way so i can keep it make sure it's in our notes so now what we're going to do and let's do where dt created is greater than let's say june 1st so what we're going to do is we're going to 202 2-06-01 and if we take that let's do this and did i get that and let's try that did it come through okay yes it did okay now notice here that six one has at 0 8 12 17. so this is greater than 6 1 it's assuming because we didn't put anything in there the time is zero zero zero zero zero so it is the same because it's just gonna take what we got so it's the same as zero zero zero zero zero zero we're gonna get those same three but if we wanted to change that to say 12 o'clock now we're only going to get two and so we're with dates notice i didn't have to do anything i didn't have to do any conversions or anything like that it's basically going to take um it's going to take that frame that date as a more or less as a string and it's going to do some conversion for us so we don't have to do some complex stuff or anything like that now we could do uh we've seen some of those so we could do um we could do like year and then we're going to see is we're preparing to get none of these whoops if i put that right oh there we go um and date created oh it's just greater than the year so i'm sorry so now let me copy this one and take this over here i don't know what that is let's do this there we go okay so here what it did is it's taking just the year part so it's anything where it's greater than 2022 so it's going to be anything that's january 1st if i wanted to do greater than month then i'm gonna find anything basically you know june or later so if i do that now i can see [Music] oh interesting so it did not pick that up so i picked that up wrong i'm gonna try that one more time created okay yep no interesting it didn't like the month and i may be missing something if i do day well that's not gonna help me much so oh i wonder if that's reversed so it's is it did it take a reverse nope it still didn't so a month it doesn't like but year well and that's right because it was like a i forget the conversion but if we want to go back it's actually it's not month this year gives us year but month does not give us that because if we do that if i go here and i just do select month um what's that 2022-oops dash 06-01 oh it is so it should oh i'm sorry so it's not i'd have to that's my mistake i have to go month here and month here with the year it was okay because it was basically just looking at let me show you let's see so if we do this uh interesting it doesn't like that so let's go look at this real quick so first let me do this let me go back to this little discussion so when we did year that year would be the same as doing this select where it's greater than 2022 because it's basically like a string and since 202 the year starts it um there so it's going to take everything from that year now here we've got this month and this month so let's go look at this select because that doesn't seem so if we do that let's go look at these two oh oh um let's do uh from horses let's just do that because we've got to give it a from so we can pick that dt created and we'll just keep it simple and so now we're going to see month but here oh i'm sorry because i reversed my months here so i didn't write that that was january i'm sorry corrected that i did not correct that so now if i do month greater than june if i get my stuff right then i see that's the wrong oh sorry let's do the actual query and so when the month is greater than so it's gonna be greater than six but if i go back and do may 1st it's going to give me you know because it's just the month it really doesn't matter what day whether i do may 1st it's going to give me all junes if i do may 28th it doesn't matter it gives me the same thing because all it cares about is the month part and if i did day on those two i think it's day and maybe date we'll find out in a second yep it's so here like since this is 5 28 the date's only going to be if it's a 29 30 or i guess a 31 if you were to have one so we're able to do some monkeying around with our dates to do that if we go back and look here and notice that we've got we've been using our ands so now let's go back and do uh select distinct age from horses and let's do so now let's come back here uh and we're going to take this same thing and now let me just do this what we want to do so let's say just age is 15 or let's start with this or h equals 16. or let's say well let's do that first so if we do this um oops gotta do that then we're gonna see there's an h15 and then there's h16s now there was a zero and let's try that negative one so let's go back and say we want to hit age 16 or age negative 1 because those are the two high ends and let's just go that way now we could say you know greater than 14 for the other one and we get 15 and 16. so let's try these two so if we do these we see that oh we get a bunch of those there's a bunch of negative ones let's do zero i didn't even count these so let's see what we got here that's not too bad okay so there's only a few zeros so i'll clean this up a little bit just because so we can do these two and we can get our zero uh we can get our ages 16 and zero now let's put this here let's say we also want to do and date created as greater than pardon my throat there my clearing is greater than 61 so that's going to be so let's say that's 9 so let's do greater than 4 18. and what we're going to see so let's look at this one let's see what it gives us let's do that so now if we do it so now we say where the age equals 0 or h equals 16 and date created equals is greater than 418 then we ended up getting all of them because we have this or and we don't really have it logically set up because it can be either the age of 0 or the age of 16 and the and basically just gets sort of lost because we've already hit one of the ores so instead what we need to do is take that same one and if we wrap it in parentheses then we're gonna get this so now it's going to be either the age is equal to 0 or 16 and the date's created equal to that did i do that right yeah i did that right okay is that date created yeah they created so now i only get those two because it's saying it's either one of these two ages and because it combines says with those two then it also has to be tt created so you need to be aware of parentheses when you're dealing with complex where clauses now sometimes people will actually write these in a way to make it a little easier to read is they'll do some formatting be something like you select the columns you do from and then what you're going to do is you're going to have each of the clause type pieces and they may even do it like this so it's a little easier for you to sort of read that now another thing that you could do is which is not super uncommon i've seen done is where you'll do like a clause and another clause so i could also say if i do and let's say date updated is greater than 518. so now if i take this so now i'm going to say i want only where the date updated is that so i'm going to see that so now i've got that those two ores gathered but then i've got an and another and so i've got three main things for the query and now i get that but if it's an or then i'm going to get something really funky and i would have to actually i'm not sure exactly how to plot that one out but because it's an or there you go it's because um we have this or updated or date updated that thing just like so these two get basically combined and this one says oh or if dt update is grown that which means everything over here that i'm getting is going to have a date uh a dt updated greater than 518 or if it doesn't then it's got to be one of these other things then it's you know then it this is why it's hitting it because the age 0 or the age of 16 and the date created is that so again if i wanted to combine these two then i would have to do parentheses around it and now i'll get maybe nothing oh no i do because both have a oh where the date created is greater than that or the date updated is greater than that but so in either case i'm in pretty good shape um i can probably get a date updated late greater than 14. i don't know if i'll get another one let's try that real quick see if a different record shows up i haven't looked close enough quietly at my data nope get the same thing i think that's a good point to stop for this one we're going to get into a little bit more complicated stuff but really this this episode this lesson i want to just focus on just wanted to focus on ands and ors and making sure that you understand the parentheses matter so it's much less like logic you would see in any other programming language you know where there is some sort of boolean logic of ands and ors and equals and not equals and greater than less than and those sorts of things we've seen them a little bit and now i just want to get a little deeper next episode we will continue digging into where clauses but as always go out there and have yourself a great day a great week and we will talk to you next time [Music] you
Transcript Segments
[Music]
hello and welcome back
we are continuing our
our sequel
story of
lessons
uh getting into all kinds of little
things playing around with my sec my
sequel mysql and mariadb
this time we're going to get back to
some
some where clause related stuff
so we're going to play around with this
and probably the next episode as well so
we're going to start with some simpler
ones some of which we've seen but to go
a little more into those and then
um
get into some more you know some more
complex things so we're going to start
with just we'll go back to that horses
table because it's a nice big one and
it's got a bunch of
funky little fields
so since we've got it it's easy to look
at
now um what i did here is went ahead and
did a um
i've gotta describe so i just did
describe horses
just to see what
wow that just totally blew up on me let
me try that again
i hadn't seen that before
there we go
i don't know what happened there but
okay
so we've got a bunch of different fields
over here on the left
and typically what you're going to do is
you're going to do a select as we've
talked about and you can use where's in
other places we're going to start with
this so you could do like select star
from horses
and then you're gonna do wear something
so first
let's do this
let's figure out some of what we got so
let's do a distinct
distinct
age
from horses so let's see how old these
guys are and gals i guess
a whole bunch of different ones so we've
got some nulls we've got some negatives
in there
and then a couple of you know small
numbers we've got a zero we got a whole
bunch of different stuff
so let's take a look at
uh let's select star from well let's
just do
uh what do we want to call it
name so let's do
name
comma breed
comma age from horses and this is going
to be a simple one and we just do
where and we take a value so age equals
16. let's do that
so there's only one
now let's do let's see what do we have
with age 15
okay there's a munch there
now if we look at
let's get a little more complicated so
let's look at the
uh let's look at the date created
for the actu let's add date created and
date updated because i want to play
around some dates
so we're going to go here
and we're going to do uh dt created
comma dt updated
we haven't played a lot with dates but
we can see here
so we've got created on a whole bunch of
different days
and updated on
you know different days as well not only
yeah look like here they're not always
the same
now if we want to do
let's say so these looks like they
started on essentially april 15th
it was the earliest it was created and
june 7th was the latest so what we can
do
is instead of that we can do well well
actually let's do
out of this group
so now we're going to have we're going
to add so we're going to say and
and let me go ahead and do it this way
so i can keep it
make sure it's in our notes
so now what we're going to do and let's
do where dt created
is greater than let's say june 1st so
what we're going to do is we're going to
202 2-06-01
and if we take that
let's do this and
did i get that
and let's try that did it come through
okay yes it did okay
now notice here
that
six one has at 0 8 12 17. so this is
greater than 6 1 it's assuming because
we didn't put anything in there the time
is zero zero zero zero zero so
it is the same because it's just gonna
take what we got
so it's the same as zero zero zero zero
zero zero
we're gonna get those same three
but if we wanted to change that to say
12 o'clock
now we're only going to get two
and so we're
with dates notice i didn't have to do
anything i didn't have to do any
conversions or anything like that it's
basically going to take
um
it's going to take that
frame that date
as a more or less as a string and it's
going to do some conversion for us so we
don't have to do some complex stuff or
anything like that
now we could do
uh we've seen some of those so we could
do
um
we could do like year
and then we're going to see is we're
preparing to get none of these whoops
if i put that right
oh there we go um
and date created
oh it's just greater than the year so
i'm sorry so now
let me copy this one and take this
over here
i don't know what that is let's do this
there we go okay
so here
what it did is
it's taking just the year part
so it's anything where it's greater than
2022 so it's going to be anything that's
january 1st
if i wanted to do greater than
month
then i'm gonna find anything basically
you know june or later
so if i do that now i can see
[Music]
oh interesting so it
did not pick that up so i picked that up
wrong
i'm gonna try that one more time created
okay
yep
no interesting it didn't like the month
and i may be missing something if i do
day
well that's not gonna help me much
so
oh i wonder if that's reversed
so it's
is it did it take a reverse
nope it still didn't so a month it
doesn't like but year
well and that's right because it was
like a
i forget the conversion but
if we want to go back it's actually it's
not month this year gives us year but
month does not give us that because if
we do that
if i go here
and i just do select month
um
what's that 2022-oops
dash
06-01
oh it is so it should oh i'm sorry
so it's not i'd have to that's my
mistake i have to go
month here and
month here
with the year it was okay because it was
basically just looking at
let me show you let's see so if we do
this
uh
interesting it doesn't like that
so let's go look at this real quick
so first let me do this
let me go back to this little discussion
so when we did year that year
would be the same as doing this select
where it's greater than
2022
because it's basically like a string and
since 202 the year starts it
um
there so it's going to take everything
from that year now here
we've got this month and this month so
let's go look at this
select
because that doesn't seem so if we do
that
let's go look at these two
oh
oh
um let's do
uh from horses
let's just do that
because we've got to give it a from so
we can pick that dt created
and we'll just keep it simple
and so now we're going to see
month but here oh i'm sorry because i
reversed my months here
so
i didn't write that that was january i'm
sorry corrected that
i did not correct that
so now if i do month greater than june
if i get my stuff right
then i see
that's the wrong oh
sorry
let's do the actual query
and
so when the month is greater than so
it's gonna be greater than six but if i
go back and do may 1st it's going to
give me you know because it's just the
month it really doesn't matter what day
whether i do may 1st
it's going to give me all
junes if i do may
28th it doesn't matter it gives me the
same thing because all it cares about
is the month part and if i did
day on those two
i think it's day and maybe date
we'll find out in a second yep it's
so here like since this is 5 28
the date's only going to be if it's a 29
30 or i guess a 31 if you were to have
one
so we're able to do some monkeying
around with our dates to do that if we
go back and look here and notice that
we've got we've been using our ands so
now
let's go back and do uh select distinct
age from horses
and let's do
so now let's come back here
uh
and we're going to take this same thing
and now let me just do this
what we want to do so let's say just age
is 15 or let's start with this
or
h equals 16. or let's say
well let's do that first
so if we do this
um
oops gotta do that
then we're gonna see
there's an h15 and then there's h16s now
there was a zero and let's try that
negative one so let's go back and say we
want to hit age 16 or
age negative 1 because
those are the two high ends
and let's just go that way now we could
say
you know greater than 14 for the other
one and we get 15 and 16. so let's try
these two
so if we do these we see that oh we get
a bunch of those
there's a bunch of negative ones let's
do
zero i didn't even count these so let's
see what we got here
that's not too bad okay so there's only
a few zeros so
i'll clean this up a little bit just
because
so we can do these two and we can get
our
zero
uh we can get our ages 16 and zero now
let's put this here
let's say we also want to do
and date created as greater than
pardon my throat there my clearing is
greater than 61 so that's going to be so
let's say that's 9 so let's do
greater than
4 18.
and what we're going to see
so let's look at this one
let's see what it gives us
let's do that
so now if we do it so now we say where
the age equals 0 or h equals 16 and date
created equals
is greater than 418
then we ended up getting
all of them
because
we have this or and we don't really have
it
logically set up because it can be
either the age of 0 or
the age of 16
and the and basically just gets sort of
lost because we've already hit one of
the ores
so instead what we need to do
is take that same one
and if we wrap it in parentheses
then we're gonna get this so now it's
going to be either the age is equal to 0
or 16 and the date's created equal to
that
did i do that right yeah i did that
right okay
is that date created yeah they created
so now
i only get those two
because it's saying it's either one of
these two ages
and
because it combines says with those two
then it also has to be tt created
so you need to be aware of parentheses
when you're dealing with complex where
clauses now sometimes people will
actually write these
in a way to make it a little easier to
read is they'll do some formatting be
something like you select the columns
you do from
and then what you're going to do is
you're going to have
each of the clause type pieces
and they may even do it like this so
it's a little easier for you to sort of
read that
now another thing that you could do is
which is not super uncommon i've seen
done is where you'll do like
a clause and another clause
so i could also say if i do and
let's say date updated
is greater than
518. so now if i take this
so now i'm going to say i want only
where the date updated is that so i'm
going to see that so now i've got that
those two ores gathered but then i've
got an and another and so i've got three
main things for the
query
and now i get that
but if it's an or
then i'm going to get something really
funky
and i would have to actually i'm not
sure exactly how to
plot that one out but because it's an or
there you go it's because
um
we have this or updated or date updated
that thing just like so these two get
basically combined and this one says
oh or if dt update is grown that which
means everything over here that i'm
getting is going to have a date uh a dt
updated greater than 518 or if it
doesn't
then
it's got to be one of these other things
then it's you know then it this is why
it's hitting it because the age 0 or the
age of 16 and the date created is that
so again
if i wanted to combine these two
then i would have to do parentheses
around it and now i'll get
maybe nothing
oh no i do because both have a
oh where the date created is greater
than that or
the date updated is greater than that
but so in either case i'm in pretty good
shape
um
i can probably get a date updated late
greater than 14. i don't know if i'll
get another one let's try that real
quick see if a different record shows up
i haven't looked close enough quietly at
my
data nope get the same thing
i think that's a good point to stop for
this one
we're going to get into a little bit
more complicated stuff but really this
this episode this lesson i want to just
focus on
just wanted to focus on ands and ors and
making sure that you understand the
parentheses matter
so it's much less like logic you would
see in any other programming language
you know where there is some sort of
boolean logic of ands and ors and
equals and not equals and greater than
less than and those sorts of things
we've seen them a little bit
and now i just want to get a little
deeper next episode we will continue
digging into where clauses
but as always go out there and have
yourself a great day a great week and we
will talk to you
next time
[Music]
you