MySQL Database Tutorial - 11 - Advanced Filtering Using AND and OR Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to your eleventh
MySQL tutorial and in this lesson what
we're gonna be doing is we're gonna be
taking a look at some advanced filtering
using the and keyword and the or keyword
so let's go ahead and make a simple
statement say select the name state and
we'll also select the city from
customers where and say we want to do
this we want to select the customers
that live in California and they also
must live in Hollywood because there are
a lot of different places to live in
California but we want to say okay if
you live in California and furthermore
you live in Hollywood those are the
customers you want so let's go ahead and
put state equals and remember whenever
we're working with text we need to put
it in between quotation marks and we
also want the city to be Hollywood
hopefully I spelled that right Hollywood
so basically what we can do is we
already know that we can filter the
results using a where statement and then
making a test but if we want both tests
to be true then we need to add a keyword
called and and that only filters the
results where in this condition and this
condition are true so let's go ahead and
let me just copy this run it and check
it out so not only does Jack Nicholson
live in California but he also lives in
Hollywood now the rest of the database
of course other people live in
California but since they don't also
live in Hollywood then that also isn't
true and what if there is another
Hollywood somewhere else like I don't
know Idaho or maybe there's a Hollywood
in New York well that also wouldn't be
true because those people didn't live in
California so basically just remember
this it's basically the same is you know
plain English whenever you say and both
the first and the second thing must be
true so let's go ahead and take it take
a look at the keyword or now if we go
ahead and
select name state and city from
customers where I'll do something like
City equals Boston or I might as well
stick with California state equals CA
then remember I said last time whenever
use and then this test and this test
must be true whenever we run this query
and we go ahead and hit go and then
check it out either the first test or
the second test need to be true so they
don't both need to be true just one or
the other
so that's the difference between an and
or whenever you use and all of the tests
need to be true whenever you use or only
one of them need to be true so of course
you're saying okay why do we get this
California but it isn't Boston it's
because as long as someone lives in
California or boss then it's going to
give us the results in what if we're
saying okay what if someone lived in
Boston California even though that isn't
in place well that still would return
the results because it's either one of
them it's basically like this whenever
this one's true or this one's true or if
they're both true then whenever you're
using or then it's going to return that
result now the last thing I want to
touch on is this sometimes you're gonna
get kind of weird queries or orders
maybe from your boss or teacher let me
go ahead and type you guys something
weird that I'm gonna just type it
because if I try to explain it it's
gonna be really hard to explain okay
select and let me think of something ID
name and City from customers where ID
equals one we don't need parentheses for
that or ID equals two so that makes
sense
and City equals Raleigh okay so what
maybe your boss comes in and says okay
we want to find someone who's
is one or two and they live in Raleigh
so for example if my ID was one or two
and I lived in Raleigh then that's what
we would be looking for so let me go
ahead and read this and it's gonna say
okay my ID is one or two but what's
going on here
Bucky Roberts doesn't live in Raleigh so
why is that selecting Bucky Roberts
because what we were trying to say is
okay make sure that the person has an ID
of one or two which was true and they
must live in Raleigh so if we scroll
down here saying okay something went
around here because of course Bucky
Roberts has the ID of one but we said
and they must live in Raleigh and that
wasn't the case so what's going on is
this whenever my SQL is going to read
this it's gonna read it in left-to-right
fashion so it's gonna say okay basically
it's gonna say anyone with the ID of one
or anyone with the ID of two that lives
in Raleigh okay but that's not what we
wanted we wanted someone with the ID of
one or two and someone who lives in
Raleigh so basically it gets kind of
confusing but I'm gonna show you guys
what we wanted and what MySQL was
looking at we wanted something like this
we wanted someone with the ID of one or
two and we also wanted them to live in
Raleigh but whenever MySQL read this it
said okay I wanted someone with the ID
of one or someone with ID of two and
City of Raleigh so that's another thing
I want to point out whenever you have
something complicated it use multiple or
or ant statements make sure that use you
you use parentheses because let me go
ahead and put parentheses around this so
now when we say okay - well this is how
we meant it sorry for confusing you I
want someone with ID of one or two okay
you got that and they must also live in
Raleigh
so since Bucky doesn't live in Raleigh
he would be excluded from this result so
now when I use parentheses and go ahead
and run this query we get a different
result Noah is the only person with the
ID of either one or two
and he also lives in Raleigh so again
one last time whenever you have multiple
or and ant statements please make sure
to use parentheses and this pretty much
is very important whenever you're using
where with a filter so that's all I want
to point out um thank you guys for
watching don't forget to subscribe and
I'll see you guys in the next lesson