1
00:00:00,150 --> 00:00:02,850
Next up, we're going to cover the in operator.

2
00:00:03,240 --> 00:00:09,450
So if we wanted to do something like select all the books that are written by Carver or Lahiri, I'm

3
00:00:09,450 --> 00:00:13,320
talking about last names, Carver or Lahiri or Smith.

4
00:00:13,350 --> 00:00:15,120
We know how to do that, right?

5
00:00:15,120 --> 00:00:22,920
We can use or I would do something like, let's say I want to select title and author L name.

6
00:00:24,210 --> 00:00:25,500
From books.

7
00:00:25,830 --> 00:00:29,190
And then I just have a bunch of Where's where?

8
00:00:29,430 --> 00:00:33,000
Author ll name is exactly equal to Carver.

9
00:00:33,990 --> 00:00:36,420
Or author.

10
00:00:37,300 --> 00:00:41,050
Our name is exactly equal to who else Did we have Lahiri in there?

11
00:00:41,530 --> 00:00:42,970
Or the author?

12
00:00:42,970 --> 00:00:47,530
Last name is exactly equal to Smith.

13
00:00:49,140 --> 00:00:51,570
And this should work, assuming you don't have a typo.

14
00:00:51,600 --> 00:00:53,490
We have multiple expressions here, right?

15
00:00:53,640 --> 00:00:55,270
Author ll name equals Carver.

16
00:00:55,290 --> 00:00:56,580
Author L name equals Lahiri.

17
00:00:56,580 --> 00:00:57,300
And so on.

18
00:00:57,300 --> 00:01:01,310
And we join them together with or so only one of them has to be true.

19
00:01:01,320 --> 00:01:06,870
In fact, only one of them can be true because there's no way to have on one row the author l name set

20
00:01:06,870 --> 00:01:08,730
to Carver and Lahiri.

21
00:01:08,730 --> 00:01:11,310
So only one will ever be true per each row.

22
00:01:11,310 --> 00:01:17,280
But this should give us all of the books written by Lahiri, Smith and Carver.

23
00:01:17,280 --> 00:01:19,500
And you can see that's indeed what's happening.

24
00:01:19,500 --> 00:01:20,940
But there's a simpler way.

25
00:01:20,970 --> 00:01:24,720
There's a nicer way that doesn't require so much extra work.

26
00:01:24,720 --> 00:01:30,120
Because what if I had like, ten authors I was trying to search for in this same format?

27
00:01:30,120 --> 00:01:34,590
I would have a lot of or author l name equals or author L name equals.

28
00:01:35,280 --> 00:01:36,770
It's just not ideal.

29
00:01:36,780 --> 00:01:44,610
So the solution is to use in the in operator helps us select values based off of whether some column

30
00:01:44,610 --> 00:01:47,970
is in some set of values.

31
00:01:48,300 --> 00:01:51,270
So we use parentheses to make a set of values.

32
00:01:51,270 --> 00:01:54,450
Anything in this list like Carver, Lahiri and Smith.

33
00:01:54,480 --> 00:01:59,190
If author l name is in that set, then the result is included.

34
00:01:59,190 --> 00:02:00,420
It's selected.

35
00:02:00,600 --> 00:02:03,720
So let's try rewriting this same thing.

36
00:02:03,720 --> 00:02:14,340
Select title and author l Name from Books where the author last name is in We Don't Say is but in.

37
00:02:14,820 --> 00:02:17,280
And then we provide a list in parentheses.

38
00:02:17,280 --> 00:02:18,270
Carver.

39
00:02:20,720 --> 00:02:24,020
And then Lahiri and Smith.

40
00:02:25,100 --> 00:02:26,780
All right, let's try running this one.

41
00:02:28,000 --> 00:02:29,170
Paste it over here.

42
00:02:29,560 --> 00:02:30,180
Semicolon.

43
00:02:30,190 --> 00:02:30,880
I forgot that.

44
00:02:30,880 --> 00:02:32,280
And we get the same results.

45
00:02:32,290 --> 00:02:32,710
Lahiri.

46
00:02:32,710 --> 00:02:33,130
Lahiri.

47
00:02:33,130 --> 00:02:33,550
Smith.

48
00:02:33,580 --> 00:02:34,000
Carver.

49
00:02:34,000 --> 00:02:34,630
Carver.

50
00:02:34,810 --> 00:02:36,590
But look at how much shorter this is.

51
00:02:36,610 --> 00:02:37,600
It's cleaner.

52
00:02:37,750 --> 00:02:41,140
It's a bit more obvious what you're doing.

53
00:02:41,140 --> 00:02:43,420
It has some meaning behind it.

54
00:02:43,420 --> 00:02:46,780
Or that's more, obviously, a parent, I guess you would say.

55
00:02:46,780 --> 00:02:51,310
Just looking at this line compared to this, you kind of have to look at all of these oars to figure

56
00:02:51,310 --> 00:02:52,960
out what's going on here.

57
00:02:52,960 --> 00:02:57,460
It's just like, oh, you're trying to find out the last names that are in this group of last names.

58
00:02:57,460 --> 00:03:00,350
And we also have a knot in.

59
00:03:00,370 --> 00:03:05,500
Operator Just like there's a like and not like and between and not between.

60
00:03:05,500 --> 00:03:07,420
There's an in and not in.

61
00:03:08,380 --> 00:03:15,070
Let's use not in just to start to get the opposite results that we were getting here.

62
00:03:15,070 --> 00:03:20,620
So everybody who is not Carver Lahiri or Smith.

63
00:03:21,610 --> 00:03:24,580
So I'll negate it with not in.

64
00:03:25,840 --> 00:03:30,060
Leave everything else the same and then I will copy it.

65
00:03:30,070 --> 00:03:30,840
Although it looks like.

66
00:03:30,850 --> 00:03:32,350
Is it complaining to me about something?

67
00:03:33,160 --> 00:03:34,960
Oh, I was missing a semicolon up here.

68
00:03:35,000 --> 00:03:36,460
Okay, paste this in.

69
00:03:36,460 --> 00:03:38,440
And we see all the other authors.

70
00:03:38,440 --> 00:03:38,950
We don't see.

71
00:03:38,950 --> 00:03:39,430
Lahiri.

72
00:03:39,430 --> 00:03:41,530
We don't see Smith, We don't see Carver.

73
00:03:41,530 --> 00:03:42,880
But everyone else is here.

74
00:03:43,420 --> 00:03:45,460
So here's one last example.

75
00:03:45,460 --> 00:03:47,050
Let's say I'm very superstitious.

76
00:03:47,050 --> 00:03:48,970
I have a problem with even numbers.

77
00:03:48,970 --> 00:03:49,720
I just.

78
00:03:49,720 --> 00:03:50,080
Come on.

79
00:03:50,080 --> 00:03:51,280
I needed to come up with something.

80
00:03:51,280 --> 00:03:52,270
It makes no sense.

81
00:03:52,270 --> 00:03:54,490
But let's say that's my superstition.

82
00:03:54,490 --> 00:03:58,360
So I want to select all the books not published in these even years.

83
00:03:58,360 --> 00:04:00,760
2000, 2000 to 2004.

84
00:04:01,150 --> 00:04:04,120
I could write this horrible query.

85
00:04:04,150 --> 00:04:10,690
I could say find all the titles and release here from books where release here is not 2000, it's also

86
00:04:10,690 --> 00:04:13,510
not 2002 and it's also not 2004.

87
00:04:14,140 --> 00:04:16,060
And this will work if I run it.

88
00:04:16,269 --> 00:04:17,980
Let me zoom out a little bit.

89
00:04:18,339 --> 00:04:19,180
There we go.

90
00:04:19,360 --> 00:04:22,660
We see all odd numbers, 2003 one.

91
00:04:22,660 --> 00:04:24,250
We see 96 in here.

92
00:04:24,250 --> 00:04:30,220
But that's because I was only excluding years greater than 2000 onwards.

93
00:04:30,220 --> 00:04:30,390
Right?

94
00:04:30,400 --> 00:04:30,970
2000.

95
00:04:30,970 --> 00:04:32,440
2000 to 2004.

96
00:04:32,770 --> 00:04:33,430
This sucks.

97
00:04:33,430 --> 00:04:35,680
So this is not a great option.

98
00:04:35,890 --> 00:04:43,450
So I could use in or not in and reduce that significantly if I now run that version.

99
00:04:43,990 --> 00:04:45,550
OC It's looking good.

100
00:04:45,580 --> 00:04:53,590
However, I've only put in the years Putin, I've only put in the years that are greater than 2000.

101
00:04:53,590 --> 00:04:58,420
So I am getting all these earlier results like 96, 81, 89.

102
00:04:58,450 --> 00:05:01,810
Let's say that I really only want books that are more recent.

103
00:05:01,810 --> 00:05:04,870
I have a bias against old books, I guess.

104
00:05:04,870 --> 00:05:06,160
Let's say that's the case.

105
00:05:06,160 --> 00:05:12,040
I want books that are released after the year 2000, so I could add in another condition.

106
00:05:12,040 --> 00:05:15,940
So where release here is greater than or equal to 2000.

107
00:05:15,940 --> 00:05:18,340
And it's also not one of these.

108
00:05:18,340 --> 00:05:20,890
It's not 2000, 2000 to 2004.

109
00:05:21,250 --> 00:05:28,120
I guess this works, but this is still really long and also a little clumsy and it doesn't really scale

110
00:05:28,120 --> 00:05:29,950
up as time moves on.

111
00:05:29,950 --> 00:05:31,750
We're now in, what, 20, 22?

112
00:05:31,750 --> 00:05:38,770
I'm recording this, so I've already I would have to add in 2018 if I had those books in 2020 and 2022

113
00:05:38,770 --> 00:05:39,850
and then 2024.

114
00:05:39,850 --> 00:05:41,080
It just is not great.

115
00:05:41,080 --> 00:05:46,060
If what I truly want to do is get rid of all even year release dates.

116
00:05:46,300 --> 00:05:48,310
So there's a fancier way.

117
00:05:48,310 --> 00:05:50,740
This is beyond what you need to know.

118
00:05:50,740 --> 00:05:52,120
This is just kind of a bonus thing.

119
00:05:52,120 --> 00:05:57,400
I wanted to show we can use the percent sign operator, which is called modulo.

120
00:05:57,460 --> 00:06:00,880
Modulo is also known as the remainder operator.

121
00:06:01,120 --> 00:06:09,820
So what it will do if I have something like ten and then I do modulo four, what will happen here is

122
00:06:09,820 --> 00:06:16,030
we'll get the remainder, the resulting remainder of dividing four into ten a whole number of times.

123
00:06:16,030 --> 00:06:18,550
So how many times does four go into ten?

124
00:06:18,580 --> 00:06:20,080
It goes in two times.

125
00:06:20,080 --> 00:06:24,340
So that leaves us with the remainder of two because four times two would be eight.

126
00:06:24,340 --> 00:06:27,370
Ten minus eight is two and we see two.

127
00:06:27,370 --> 00:06:28,930
But let's try one more example.

128
00:06:28,930 --> 00:06:35,440
Select, I don't know, 17 modulo how about six?

129
00:06:36,400 --> 00:06:41,080
What is the largest number of times six evenly goes into 17.

130
00:06:41,080 --> 00:06:42,730
It's just twice, right?

131
00:06:42,730 --> 00:06:44,290
Because that would be 12.

132
00:06:44,410 --> 00:06:46,120
And then what is the remainder?

133
00:06:46,120 --> 00:06:47,710
17 -12.

134
00:06:47,920 --> 00:06:49,390
The remainder is five.

135
00:06:49,630 --> 00:06:51,280
So how does this help us?

136
00:06:51,280 --> 00:06:59,560
Well, we can instantly tell if any number is even if we mod by two modulo by two, and if we get zero

137
00:06:59,560 --> 00:07:03,130
as a result, that means two goes into that number evenly.

138
00:07:03,130 --> 00:07:04,870
It doesn't matter how big it is.

139
00:07:05,020 --> 00:07:08,350
If it is even we'll end up with zero.

140
00:07:08,350 --> 00:07:11,170
If it's an odd number, we end up with one.

141
00:07:11,230 --> 00:07:17,860
So we can use modulo here to say is a release here modulo two, not equal to zero.

142
00:07:17,860 --> 00:07:19,210
That would be one option.

143
00:07:19,210 --> 00:07:21,670
Or I could say is it equal to one?

144
00:07:22,090 --> 00:07:25,270
In either case, I would get odd number years.

145
00:07:25,270 --> 00:07:26,560
So let's try this query.

146
00:07:26,560 --> 00:07:31,060
We'll select the title and the released year from books.

147
00:07:31,060 --> 00:07:35,170
And I'm going to start by just limiting myself to books or to years greater than 2000.

148
00:07:35,170 --> 00:07:38,890
So over where released year is greater than or equal to 2000.

149
00:07:39,740 --> 00:07:42,500
And released.

150
00:07:42,540 --> 00:07:49,330
Yea, when you divide it by two, the remainder should be exactly equal to one.

151
00:07:49,340 --> 00:07:51,890
That means it's an odd numbered year.

152
00:07:52,490 --> 00:07:57,530
I could have also said it's not equal to zero, but I'll say it's equal to one.

153
00:07:57,770 --> 00:07:58,160
All right.

154
00:07:58,160 --> 00:08:02,000
So remember, this is the remainder operator, and this is above and beyond what we need to know.

155
00:08:02,000 --> 00:08:03,100
But here we are.

156
00:08:03,110 --> 00:08:09,710
We now only get the odd numbered years from 2000 beyond nothing before the year 2000.

157
00:08:09,710 --> 00:08:17,180
So that actually had nothing to do with not in, but I wanted to expand upon the initial concept of

158
00:08:18,140 --> 00:08:22,880
finding books that were not released in these years where we did use not in, but there is a better

159
00:08:22,880 --> 00:08:23,210
way.

160
00:08:23,210 --> 00:08:24,500
So that's where modulo came from.

161
00:08:24,590 --> 00:08:28,910
Anyway, moving on, we've got a little bit more to cover in this section before an exercise.