Next up, we're going to cover the in operator.
So if we wanted to do something like select all the books that are written by Carver or Lahiri, I'm
talking about last names, Carver or Lahiri or Smith.
We know how to do that, right?
We can use or I would do something like, let's say I want to select title and author L name.
From books.
And then I just have a bunch of Where's where?
Author ll name is exactly equal to Carver.
Or author.
Our name is exactly equal to who else Did we have Lahiri in there?
Or the author?
Last name is exactly equal to Smith.
And this should work, assuming you don't have a typo.
We have multiple expressions here, right?
Author ll name equals Carver.
Author L name equals Lahiri.
And so on.
And we join them together with or so only one of them has to be true.
In fact, only one of them can be true because there's no way to have on one row the author l name set
to Carver and Lahiri.
So only one will ever be true per each row.
But this should give us all of the books written by Lahiri, Smith and Carver.
And you can see that's indeed what's happening.
But there's a simpler way.
There's a nicer way that doesn't require so much extra work.
Because what if I had like, ten authors I was trying to search for in this same format?
I would have a lot of or author l name equals or author L name equals.
It's just not ideal.
So the solution is to use in the in operator helps us select values based off of whether some column
is in some set of values.
So we use parentheses to make a set of values.
Anything in this list like Carver, Lahiri and Smith.
If author l name is in that set, then the result is included.
It's selected.
So let's try rewriting this same thing.
Select title and author l Name from Books where the author last name is in We Don't Say is but in.
And then we provide a list in parentheses.
Carver.
And then Lahiri and Smith.
All right, let's try running this one.
Paste it over here.
Semicolon.
I forgot that.
And we get the same results.
Lahiri.
Smith.
Carver.
But look at how much shorter this is.
It's cleaner.
It's a bit more obvious what you're doing.
It has some meaning behind it.
Or that's more, obviously, a parent, I guess you would say.
Just looking at this line compared to this, you kind of have to look at all of these oars to figure
out what's going on here.
It's just like, oh, you're trying to find out the last names that are in this group of last names.
And we also have a knot in.
Operator Just like there's a like and not like and between and not between.
There's an in and not in.
Let's use not in just to start to get the opposite results that we were getting here.
So everybody who is not Carver Lahiri or Smith.
So I'll negate it with not in.
Leave everything else the same and then I will copy it.
Although it looks like.
Is it complaining to me about something?
Oh, I was missing a semicolon up here.
Okay, paste this in.
And we see all the other authors.
We don't see.
Lahiri.
We don't see Smith, We don't see Carver.
But everyone else is here.
So here's one last example.
Let's say I'm very superstitious.
I have a problem with even numbers.
I just.
Come on.
I needed to come up with something.
It makes no sense.
But let's say that's my superstition.
So I want to select all the books not published in these even years.
2000, 2000 to 2004.
I could write this horrible query.
I could say find all the titles and release here from books where release here is not 2000, it's also
not 2002 and it's also not 2004.
And this will work if I run it.
Let me zoom out a little bit.
There we go.
We see all odd numbers, 2003 one.
We see 96 in here.
But that's because I was only excluding years greater than 2000 onwards.
Right?
2000.
2000 to 2004.
This sucks.
So this is not a great option.
So I could use in or not in and reduce that significantly if I now run that version.
OC It's looking good.
However, I've only put in the years Putin, I've only put in the years that are greater than 2000.
So I am getting all these earlier results like 96, 81, 89.
Let's say that I really only want books that are more recent.
I have a bias against old books, I guess.
Let's say that's the case.
I want books that are released after the year 2000, so I could add in another condition.
So where release here is greater than or equal to 2000.
And it's also not one of these.
It's not 2000, 2000 to 2004.
I guess this works, but this is still really long and also a little clumsy and it doesn't really scale
up as time moves on.
We're now in, what, 20, 22?
I'm recording this, so I've already I would have to add in 2018 if I had those books in 2020 and 2022
and then 2024.
It just is not great.
If what I truly want to do is get rid of all even year release dates.
So there's a fancier way.
This is beyond what you need to know.
This is just kind of a bonus thing.
I wanted to show we can use the percent sign operator, which is called modulo.
Modulo is also known as the remainder operator.
So what it will do if I have something like ten and then I do modulo four, what will happen here is
we'll get the remainder, the resulting remainder of dividing four into ten a whole number of times.
So how many times does four go into ten?
It goes in two times.
So that leaves us with the remainder of two because four times two would be eight.
Ten minus eight is two and we see two.
But let's try one more example.
Select, I don't know, 17 modulo how about six?
What is the largest number of times six evenly goes into 17.
It's just twice, right?
Because that would be 12.
And then what is the remainder?
17 -12.
The remainder is five.
So how does this help us?
Well, we can instantly tell if any number is even if we mod by two modulo by two, and if we get zero
as a result, that means two goes into that number evenly.
It doesn't matter how big it is.
If it is even we'll end up with zero.
If it's an odd number, we end up with one.
So we can use modulo here to say is a release here modulo two, not equal to zero.
That would be one option.
Or I could say is it equal to one?
In either case, I would get odd number years.
So let's try this query.
We'll select the title and the released year from books.
And I'm going to start by just limiting myself to books or to years greater than 2000.
So over where released year is greater than or equal to 2000.
And released.
Yea, when you divide it by two, the remainder should be exactly equal to one.
That means it's an odd numbered year.
I could have also said it's not equal to zero, but I'll say it's equal to one.
All right.
So remember, this is the remainder operator, and this is above and beyond what we need to know.
But here we are.
We now only get the odd numbered years from 2000 beyond nothing before the year 2000.
So that actually had nothing to do with not in, but I wanted to expand upon the initial concept of
finding books that were not released in these years where we did use not in, but there is a better
way.
So that's where modulo came from.
Anyway, moving on, we've got a little bit more to cover in this section before an exercise.