Already.
So next up we're talking about a new operator called in N and what it does is it allows us to provide
a set of values to three ten fifty different values and then check if a given column is in that set.
So basically like put that in the easier to understand way.
So we wanted to do this on a select all the books written by Carver Raymond Carver or Lahiri Hillary
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 oars we would say she liked all books written
by Carver or either here or by Smith who look something like this.
It's like title An author last name from books where author last name is equal to Carver or author last
name is equal to the history or the last name is equal to Smith.
So this works.
Come on over to cloud nine.
First thing you'll need to do is switch databases.
I'm using the new testing D-B.
I mean a used bookshop again.
OK so now I'm going to use this select title and author L name from these 3 or from where author is
one of these three if I paste that in.
You can see we get all the books by the Harry Smith and Carver perfect.
However as you probably guess there's a better way to do it in.
Make it much easier and much shorter.
So it looks like this.
So like title an 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 name matches anything in here it will be that the row will be select.
So it's really straightforward very simple to use.
It basically goes from this multiple lines and look how much repetition we have.
Author L name equals or author L name equals author L name equals and condenses it down to this.
So let's try to paste this down here so you can just compare
repasted in.
You can see it does the exact same thing.
And the other good thing is that you know 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 release your where release year is in let's say 2017 or 1985 just like that.
And if we pace that it and we get these two books.
All right.
So just to show you it doesn't have to be strings you can work with new 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 it.
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 I go into the bookstore and I tell the court 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 an even number of
years 2000 until 2016.
So I want to exclude 2002 to 2004 and so on.
Well I can do this which is kind of a nightmare where I'm basically using logical ends and not equals.
So remember we have to go back a few slides here when we were doing this version here before we talked
to it in I was doing.
Equals and or so author equals cover or author I'll name it here.
But when we negate it and do the opposite.
I need to negate that equals with a not equals.
And I flip that or two in 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 really steer 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 and.
OK.
But the point is is that this is a mess.
It's noxious It's gigantic.
I do copy it over and I tried running it.
You'll see we get all books from odd number 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 have just passed them all.
And notice the difference there massive difference.
And if we run this
we get the exact same result.
But much much shorter and much easier to add onto 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 5:51.
So what I could do is 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.
And so all that we're doing here is selecting books where the release after the year 2000 including
2000 greater than or equal to two thousand.
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 2001 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 Maggiolo.
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 2 right.
We can divide 2 into a number and there's no remainder.
That means that it's even so 4 divided by two is two and it's just a whole number.
So it's perfect but 3 or let's say seven divided by two.
It's 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 modulo the remainder operator.
So if we just focus on that part this is saying release year divided by 2.
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 in 2001 the remainder is not zero.
The remainder is 1 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 and which is the whole point of this video.
But I figured it is still worth talking about.
So what we're doing is selecting the same stuff title really square 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 pod years so release year is not released year maade 2 is not
equal to zero two does not divide cleanly and paste that interest.
We have a record.
Run it and you can see now I only get the same exact results right.
Let me order it so that we can compare.
No.
Type of OK.
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 up.
So this is a in my opinion a better solution it grows and it expands more.
The other thing there is a bit of a performance difference.
It's not major but when we look at this basically what if we're pretending less personify or anthropomorphized
might as well and it's taking a year like 2013 when we have not in it's going to try.
Is 2013 equal to 2000.
No.
Is it equal to 2002.
No it's well 2004 and 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 it a single mathematical operation like we
have here.
We're just checking is the release year model 2 not equal to zero.
And it's really not a big difference but it's just something that's nice to be aware of.
OK.
So big tangent here.
This video was really about in not in.
So just to go back to that were very useful.
Allow us to pass in a set of numbers or strings or values dates or whatever it is which then are rows
are checked against to see if they either contained or not contained depending on if we're using in
or not in.
All right.