All righty.
So next up, we're talking about a new operator called in i n.
And what it does is it allows us to provide a set of values to three, ten, 50 different values and
then check if a given column is in that set.
So basically, let me put that in an easier to understand way say we wanted to do this.
I want to select all the books written by Carver, Raymond Carver or Lahiri, Jhumpa Lahiri or Smith,
in our case, Patti Smith.
So we know how to do that right now.
I mean, if you listen to how I just said that with these ORs, we would say select all books written
by Carver or by Lahiri or by Smith.
It would look something like this select title and author last name from books where author last name
is equal to Carver or author last name is equal to Lahiri or author last name is equal to Smith.
So this works.
I'll come on over to Cloud nine.
First thing I'll need to do is switch databases.
I am using the new testing DB, so I'm going to use book shop again.
Okay.
So now I'm going to use this select title and author l name from these three or from where author is
one of these three.
If I paste that in, you can see we get all the books by Lahiri, Smith and Carver.
Perfect.
However, as you probably guess, there's a better way to do it in will make it much easier and much
shorter.
So it looks like this select title and author from the first book or from books.
Same thing where author last name is in.
And then in parentheses, we pass in a comma separated list of values.
So if author l name matches anything in here, it will be the the row will be selected.
So it's really straightforward, very simple to use.
It basically goes from this multiple lines and look at how much repetition we have.
Author L name equals or author l name equals or author ll name equals and condenses it down to this.
So let's try it out.
I'll paste this down here so you can just compare.
If we paste it in, you can see it does the exact same thing.
And the other good thing is that as we add in more conditions or more authors, in our case, this continues
to get much longer every time.
This is really just a very simple change.
If we also want to add in another author, very easy.
So it's pretty useful.
You're not limited just to working with text.
So I'll just duplicate this just to show you.
We could do select title and released here where released here is in let's say 2017 or 1985 just like
that.
And if we paste that in.
We get these two books.
All right.
So that was just to show you, it doesn't have to be strange.
You can work with numbers as well, as you probably guessed.
Maybe it didn't.
I don't know.
But there's also a knot in and it works just like not between worked compared to between it will do
the opposite of in.
So let's say this is a really dumb hypothetical, but let's say I'm really superstitious and I have
a problem with even numbers.
And I go into the bookstore and I tell the clerk this, even though I promise I'm not superstitious
and I don't have a problem with even numbers.
If anything, I really like even numbers if you can.
But I go into the store and I say, I want to get all books that were not published in even number of
years, 2000 until 2016.
So I want to exclude 22000 to 2004 and so on.
Well, I could do this, which is kind of a nightmare where I'm basically using logical ends and not
equals.
So remember, we'd have to go back a few slides here when we were doing this version here before we
talked about in I was doing equals and or so author l name equals carver or author l name equals the
theory.
But when we negate it and do the opposite, I need to negate the equals within not equals.
And I flip the or to an and which.
When I say it that way, I realize it may sound a bit more intimidating than it is basically logically
if we step through this.
What I'm saying is I want to select all the books where released here is not 2000 and it's not 2002
and it's not 2004 and it's not any of these.
So anything other than these values and or wouldn't work here, it has to be end.
OC But the point is that this is a mess.
It's obnoxious, it's gigantic.
I do copy it over and I try running it.
You'll see we get all books from odd number of years.
But there's a better way.
We can use not in which works just like you'd expect.
Rather than saying in.
We just say not in.
And we provide that list, that set of values.
And in this case, I've just passed them all in.
Notice the difference there.
Massive difference.
And if we run this.
Oops.
We get the exact same result.
But much, much shorter and much easier to add on to or change.
Now, some of you might be thinking, well, this still seems really silly.
There's a couple of things that we can change.
One is that I only want books released after 2000.
So right now, I'm basically saying I don't want any even years in the 2000s.
But we're still getting even years.
96.
Is there any others?
I guess it's just 96.
So what I could do is I say that I only want books released after 2000 and it would just look like this.
So the only reason I'm showing you this is so that you can see how to use logical and in conjunction
with something like not in.
So all that we're doing here is selecting books where the release after the year 2000, including 2000
greater than or equal to 2000.
So that's going to narrow it down significantly.
And then within that subset, we only want ones that were released in years that are not contained here.
So the odd numbers, the odd 2000, 2001, 2003 and so on.
So as far as why you would do this, let's say that I have taste in literature and I only want recent
books from odd years and let's do an order by.
There we go.
So we get 21 all the way up to 2017, all the modern books with odd years, but there's still something
more.
And it doesn't require us to use in or not in, because if you look at this, this is pretty repetitive.
Well, repetitive is the wrong word.
It's long.
And there's a pattern here.
Right.
So there has to be a better way to express this.
And what I'm going to show you here has nothing to do with.
In or not in.
So it's a bit of a tangent, but I feel that it's worth talking about if you're familiar with programming.
The solution here is to use an operator called Modulo, and basically this is also known as the remainder
operator.
What it will allow us to do is test if something is even.
So how do we know if something is even in general?
Well, it's divisible evenly by two.
Right?
If we can divide two into a number and there's no remainder, that means that it's even.
So four divided by two is two and it's just a whole number.
So it's perfect.
But three or let's say seven divided by two is not a clean, even division.
So that means that it's not an even number.
So there's a way to do that.
It looks like this.
So the percent sign is known, as I said, is modulo the remainder operator.
So if we just focus on that part, this is saying release year divided by two, the remainder is not
equal to zero.
So if we plug in 2000 right here, 2000 divided by two.
The remainder of that is zero.
But if we plug it in 2001, the remainder is not zero.
The remainder is one, meaning that it's odd.
So again, this is above and beyond what you need to know.
But I just figured it would come up in the comments if someone saw this gigantic mess here.
Someone would probably say, there's a better way.
And they're right.
It just happens to not use in or not in, which is the whole point of this video.
But I figured it was still worth talking about.
So what we're doing selecting the same stuff title released here from books.
First thing is the same only selecting books that are released after the year 2000 or including the
year 2000.
And then they also have to be odd years.
So release year is not released.
Your mod two is not equal to zero.
Two does not divide cleanly and paste that in just so we have a record.
Run it.
And you can see now I only get the same exact result.
Right.
Let me order it so that we can compare.
No.
I have a typo.
Okay.
So we get the exact same results that we got earlier, but we didn't have to write all these years out
explicitly.
And the other advantage is this solution works when we're in 2018 and 2020 and whatever other even numbers
we have, as long as they are even and greater than 2000, it will be filtered out.
So this is, in my opinion, a better solution.
It grows and it expands more.
The other thing, there's a bit of performance difference.
It's not major, but when we look at this.
Basically, what if we're pretending?
Let's personify or anthropomorphize my SQL and it's taking a year like 2013.
When we have not in it's going to try.
Is 2013 equal to 2000?
No, it's equal to 2002.
No is equal to 2004.
And it's going to keep going down.
It has to try every option.
There's no way to know unless it actually matches it or tries to match it against every year we have
in this set.
So that could potentially take a lot more time than doing a single mathematical operation like we have
here.
We're just checking is the release here mod two not equal to zero?
And it's really not a big difference, but it's just something that's nice to be aware of.
Okay, so big tangent here.
This video was really about in and not in.
So just to go back to there, very useful.
Allow us to pass in a set of numbers or strings or values, dates, whatever it is, which then our
rows are checked against to see if they are either contained or not contained, depending on if we're
using in or not in.
All right.