MySQL Database Tutorial - 12 - Are you IN or are you NOT IN? Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to big and number
12 and in this lesson we're we're gonna
be talking about is the in and not in
statements so say we were working on
this awesome database and the police
come storming in the door in there like
Bucky we just caught the murder and I'm
like what murderer who the heck murdered
anyone he needs like don't worry about
it anyways they're in your database and
we know that they either live in
California North Carolina or New York
can you give us a list of all those
suspects so you were like heck yeah
select name and state from customers
we're and we're like hold on we typed
that wrong I'm gonna have to type that
again we're state equals and we're like
what's your list because we forgot
California or state equals North
Carolina or state equals New York and
we're just gonna go ahead and say okay
just go ahead and let me run this query
and bam and there is your list it he's
like yeah okay oh wait wait wait hold on
did you just say something sorry I fell
asleep that query took way too long to
type and check it out we only have three
options here but if we were to have more
states then look at this query it's
gonna get very messy and you know with
three options that's all it took but
what if we wanted you know to include
ten or fifteen states then things would
get really complicated so I'm gonna show
you guys a shortcut whenever you need to
use a bunch of or statements you can
just use the statement in and it's going
to make it a whole lot easier so I'm
just going to go ahead and hit select
name and I think we saw at the state
from customers where state now the very
first thing you do is you give it the
condition to search for I want to search
for a bunch of different states so I
want to search first state in now right
after in go ahead and type parentheses
and instead of making a whore
did I just say whore I am sorry I
apologized all the moms out there
shouldn't have said that that's why you
know people don't let my tutorials be
washed in college or high school or
anything because sometimes I just say
inappropriate things like that
but anyways after in we're gonna give it
a huge list for example CA or lows my
next one and see and I think it was New
York if I'm correct or NY now that's
basically all we do so as you can see
instead of making three different or or
statements we just go ahead and say okay
where the state is in California North
Carolina or New York and then we just go
ahead and hit go and check it out exact
same results except we didn't have to
use all of those or statements now
typically whenever you are searching for
a bunch of different things in the list
then you add a condition just so order
it at the end it just makes it organized
a little bit better so we would probably
want to order this by state and now
check it out this is just gonna make it
a little bit prettier so as you can see
instead of saying okay search for all
the people who live in California North
Carolina or New York even though we're
sorta kelis urchin for three different
or test we can just go ahead and make
one test with a list of all the states
and it makes it a whole lot easier now
say that the police was like ah you know
what in fact I figured out that the
suspect the one we were trying to catch
he actually does not live in these three
states he must live in the other 47
states so can you give me a list of all
the suspects who live in the other 47
states and you may be thinking oh crap
so before we learned about this nice
little n thing we might have to make a
huge or list that says or Arkansas or
Alabama or and we would have to list 47
or statements however check this out if
we want to say okay search for all the
people who do not live in these three
dates all we have to do is do this guy's
this is probably the coolest thing I
ever taught you not in California North
Carolina or New York how awesome is that
so isn't that a whole lot easier than
making 47 or statements basically
whenever we want to search for customers
who do not live in any of these states
then instead of in where it'd be the
opposite they would have to live in
these states just go ahead and type not
now you're gonna see this not not only
when you're working with lists but
whenever you put not before any testing
condition it pretty much reverses the
test so we could have something well
I'll show you guys test later on but now
let me just go ahead and run this query
real quick and check it out we now get a
list of all the customers who do not
live in these three states in other
words we get a list of all the customers
who live in the other 47 states so first
of all that police officer isn't doing a
very good job because you know there's a
lot of people left who live in 47 states
so you know they need to kind of brush
up on the work a little bit but anyways
what we are worried about are these
queries so basically like I said
whenever you want to use a bunch of or
statements you can just use in and then
type a list and parentheses instead and
it does the exact same thing it just
makes it a whole lot easier or whenever
you want to exclude a list or use
something like not in just go ahead and
type the keyword not in and then make
sure to put all your options in a list
in guys another key thing make sure not
to forget the parentheses because it's
very important so there you go there you
have it that is what in and not in does
basically takes a bunch of or statement
and clean some up make some a whole lot
easier so thank you guys for watching
don't forget subscribe and I'll see you
guys in the next video