1
00:00:00,270 --> 00:00:00,930
All righty.

2
00:00:00,930 --> 00:00:05,280
So next up, we're talking about a new operator called in i n.

3
00:00:05,610 --> 00:00:13,260
And what it does is it allows us to provide a set of values to three, ten, 50 different values and

4
00:00:13,260 --> 00:00:18,420
then check if a given column is in that set.

5
00:00:18,840 --> 00:00:23,310
So basically, let me put that in an easier to understand way say we wanted to do this.

6
00:00:23,310 --> 00:00:32,430
I want to select all the books written by Carver, Raymond Carver or Lahiri, Jhumpa Lahiri or Smith,

7
00:00:32,430 --> 00:00:33,750
in our case, Patti Smith.

8
00:00:34,320 --> 00:00:36,770
So we know how to do that right now.

9
00:00:36,780 --> 00:00:42,750
I mean, if you listen to how I just said that with these ORs, we would say select all books written

10
00:00:42,750 --> 00:00:45,930
by Carver or by Lahiri or by Smith.

11
00:00:46,350 --> 00:00:53,220
It would look something like this select title and author last name from books where author last name

12
00:00:53,220 --> 00:00:59,820
is equal to Carver or author last name is equal to Lahiri or author last name is equal to Smith.

13
00:00:59,820 --> 00:01:00,840
So this works.

14
00:01:00,840 --> 00:01:02,610
I'll come on over to Cloud nine.

15
00:01:02,610 --> 00:01:07,170
First thing I'll need to do is switch databases.

16
00:01:07,170 --> 00:01:12,420
I am using the new testing DB, so I'm going to use book shop again.

17
00:01:15,010 --> 00:01:15,550
Okay.

18
00:01:15,550 --> 00:01:23,230
So now I'm going to use this select title and author l name from these three or from where author is

19
00:01:23,230 --> 00:01:24,370
one of these three.

20
00:01:24,550 --> 00:01:29,440
If I paste that in, you can see we get all the books by Lahiri, Smith and Carver.

21
00:01:30,100 --> 00:01:30,850
Perfect.

22
00:01:31,690 --> 00:01:36,970
However, as you probably guess, there's a better way to do it in will make it much easier and much

23
00:01:36,970 --> 00:01:37,600
shorter.

24
00:01:38,440 --> 00:01:43,210
So it looks like this select title and author from the first book or from books.

25
00:01:43,240 --> 00:01:47,410
Same thing where author last name is in.

26
00:01:47,920 --> 00:01:53,020
And then in parentheses, we pass in a comma separated list of values.

27
00:01:53,230 --> 00:01:59,980
So if author l name matches anything in here, it will be the the row will be selected.

28
00:02:00,370 --> 00:02:03,430
So it's really straightforward, very simple to use.

29
00:02:03,550 --> 00:02:09,520
It basically goes from this multiple lines and look at how much repetition we have.

30
00:02:09,550 --> 00:02:17,140
Author L name equals or author l name equals or author ll name equals and condenses it down to this.

31
00:02:17,620 --> 00:02:18,790
So let's try it out.

32
00:02:20,050 --> 00:02:22,390
I'll paste this down here so you can just compare.

33
00:02:25,150 --> 00:02:28,150
If we paste it in, you can see it does the exact same thing.

34
00:02:28,450 --> 00:02:36,130
And the other good thing is that as we add in more conditions or more authors, in our case, this continues

35
00:02:36,130 --> 00:02:37,780
to get much longer every time.

36
00:02:37,810 --> 00:02:39,880
This is really just a very simple change.

37
00:02:39,880 --> 00:02:44,440
If we also want to add in another author, very easy.

38
00:02:45,490 --> 00:02:46,590
So it's pretty useful.

39
00:02:46,600 --> 00:02:49,260
You're not limited just to working with text.

40
00:02:49,270 --> 00:02:52,090
So I'll just duplicate this just to show you.

41
00:02:52,120 --> 00:03:04,780
We could do select title and released here where released here is in let's say 2017 or 1985 just like

42
00:03:04,780 --> 00:03:05,350
that.

43
00:03:08,110 --> 00:03:09,400
And if we paste that in.

44
00:03:10,430 --> 00:03:11,720
We get these two books.

45
00:03:12,860 --> 00:03:13,130
All right.

46
00:03:13,130 --> 00:03:15,680
So that was just to show you, it doesn't have to be strange.

47
00:03:15,680 --> 00:03:20,870
You can work with numbers as well, as you probably guessed.

48
00:03:20,960 --> 00:03:21,800
Maybe it didn't.

49
00:03:21,800 --> 00:03:22,340
I don't know.

50
00:03:22,340 --> 00:03:30,440
But there's also a knot in and it works just like not between worked compared to between it will do

51
00:03:30,440 --> 00:03:31,760
the opposite of in.

52
00:03:33,110 --> 00:03:39,260
So let's say this is a really dumb hypothetical, but let's say I'm really superstitious and I have

53
00:03:39,260 --> 00:03:40,400
a problem with even numbers.

54
00:03:40,400 --> 00:03:46,610
And I go into the bookstore and I tell the clerk this, even though I promise I'm not superstitious

55
00:03:46,610 --> 00:03:48,500
and I don't have a problem with even numbers.

56
00:03:48,650 --> 00:03:52,400
If anything, I really like even numbers if you can.

57
00:03:52,940 --> 00:04:01,130
But I go into the store and I say, I want to get all books that were not published in even number of

58
00:04:01,130 --> 00:04:03,740
years, 2000 until 2016.

59
00:04:04,350 --> 00:04:08,010
So I want to exclude 22000 to 2004 and so on.

60
00:04:08,910 --> 00:04:16,470
Well, I could do this, which is kind of a nightmare where I'm basically using logical ends and not

61
00:04:16,470 --> 00:04:17,279
equals.

62
00:04:17,880 --> 00:04:24,900
So remember, we'd have to go back a few slides here when we were doing this version here before we

63
00:04:24,900 --> 00:04:32,820
talked about in I was doing equals and or so author l name equals carver or author l name equals the

64
00:04:32,820 --> 00:04:33,300
theory.

65
00:04:33,450 --> 00:04:38,760
But when we negate it and do the opposite, I need to negate the equals within not equals.

66
00:04:38,760 --> 00:04:43,980
And I flip the or to an and which.

67
00:04:43,980 --> 00:04:49,470
When I say it that way, I realize it may sound a bit more intimidating than it is basically logically

68
00:04:49,470 --> 00:04:50,500
if we step through this.

69
00:04:50,520 --> 00:04:56,460
What I'm saying is I want to select all the books where released here is not 2000 and it's not 2002

70
00:04:56,460 --> 00:04:59,070
and it's not 2004 and it's not any of these.

71
00:04:59,220 --> 00:05:04,290
So anything other than these values and or wouldn't work here, it has to be end.

72
00:05:04,950 --> 00:05:07,740
OC But the point is that this is a mess.

73
00:05:07,770 --> 00:05:09,960
It's obnoxious, it's gigantic.

74
00:05:10,780 --> 00:05:14,820
I do copy it over and I try running it.

75
00:05:15,270 --> 00:05:18,150
You'll see we get all books from odd number of years.

76
00:05:21,560 --> 00:05:23,090
But there's a better way.

77
00:05:23,720 --> 00:05:27,230
We can use not in which works just like you'd expect.

78
00:05:27,230 --> 00:05:28,640
Rather than saying in.

79
00:05:28,640 --> 00:05:29,870
We just say not in.

80
00:05:29,870 --> 00:05:32,990
And we provide that list, that set of values.

81
00:05:32,990 --> 00:05:35,300
And in this case, I've just passed them all in.

82
00:05:35,930 --> 00:05:37,160
Notice the difference there.

83
00:05:37,160 --> 00:05:38,360
Massive difference.

84
00:05:39,170 --> 00:05:40,490
And if we run this.

85
00:05:40,520 --> 00:05:41,090
Oops.

86
00:05:45,940 --> 00:05:47,440
We get the exact same result.

87
00:05:49,020 --> 00:05:53,400
But much, much shorter and much easier to add on to or change.

88
00:05:54,240 --> 00:05:58,020
Now, some of you might be thinking, well, this still seems really silly.

89
00:05:58,380 --> 00:06:00,150
There's a couple of things that we can change.

90
00:06:00,180 --> 00:06:05,130
One is that I only want books released after 2000.

91
00:06:05,550 --> 00:06:10,680
So right now, I'm basically saying I don't want any even years in the 2000s.

92
00:06:10,740 --> 00:06:12,350
But we're still getting even years.

93
00:06:12,510 --> 00:06:13,620
96.

94
00:06:15,180 --> 00:06:16,040
Is there any others?

95
00:06:16,050 --> 00:06:17,550
I guess it's just 96.

96
00:06:18,090 --> 00:06:26,130
So what I could do is I say that I only want books released after 2000 and it would just look like this.

97
00:06:26,490 --> 00:06:32,400
So the only reason I'm showing you this is so that you can see how to use logical and in conjunction

98
00:06:32,400 --> 00:06:33,770
with something like not in.

99
00:06:33,810 --> 00:06:41,710
So all that we're doing here is selecting books where the release after the year 2000, including 2000

100
00:06:41,730 --> 00:06:43,460
greater than or equal to 2000.

101
00:06:43,470 --> 00:06:45,690
So that's going to narrow it down significantly.

102
00:06:46,140 --> 00:06:53,190
And then within that subset, we only want ones that were released in years that are not contained here.

103
00:06:53,190 --> 00:06:58,110
So the odd numbers, the odd 2000, 2001, 2003 and so on.

104
00:06:59,540 --> 00:07:05,720
So as far as why you would do this, let's say that I have taste in literature and I only want recent

105
00:07:05,720 --> 00:07:09,830
books from odd years and let's do an order by.

106
00:07:13,650 --> 00:07:14,280
There we go.

107
00:07:14,340 --> 00:07:21,390
So we get 21 all the way up to 2017, all the modern books with odd years, but there's still something

108
00:07:21,390 --> 00:07:21,870
more.

109
00:07:22,660 --> 00:07:28,180
And it doesn't require us to use in or not in, because if you look at this, this is pretty repetitive.

110
00:07:28,180 --> 00:07:29,650
Well, repetitive is the wrong word.

111
00:07:30,280 --> 00:07:31,270
It's long.

112
00:07:31,270 --> 00:07:33,150
And there's a pattern here.

113
00:07:33,160 --> 00:07:33,800
Right.

114
00:07:33,850 --> 00:07:36,670
So there has to be a better way to express this.

115
00:07:37,270 --> 00:07:39,730
And what I'm going to show you here has nothing to do with.

116
00:07:39,760 --> 00:07:40,880
In or not in.

117
00:07:40,900 --> 00:07:46,390
So it's a bit of a tangent, but I feel that it's worth talking about if you're familiar with programming.

118
00:07:46,810 --> 00:07:54,070
The solution here is to use an operator called Modulo, and basically this is also known as the remainder

119
00:07:54,070 --> 00:07:54,880
operator.

120
00:07:55,030 --> 00:07:58,510
What it will allow us to do is test if something is even.

121
00:07:59,020 --> 00:08:01,420
So how do we know if something is even in general?

122
00:08:01,870 --> 00:08:04,090
Well, it's divisible evenly by two.

123
00:08:04,090 --> 00:08:04,510
Right?

124
00:08:04,510 --> 00:08:08,770
If we can divide two into a number and there's no remainder, that means that it's even.

125
00:08:09,680 --> 00:08:14,210
So four divided by two is two and it's just a whole number.

126
00:08:14,210 --> 00:08:15,080
So it's perfect.

127
00:08:15,080 --> 00:08:20,000
But three or let's say seven divided by two is not a clean, even division.

128
00:08:20,180 --> 00:08:22,340
So that means that it's not an even number.

129
00:08:22,430 --> 00:08:24,110
So there's a way to do that.

130
00:08:24,830 --> 00:08:25,850
It looks like this.

131
00:08:26,870 --> 00:08:31,100
So the percent sign is known, as I said, is modulo the remainder operator.

132
00:08:31,100 --> 00:08:38,419
So if we just focus on that part, this is saying release year divided by two, the remainder is not

133
00:08:38,419 --> 00:08:39,380
equal to zero.

134
00:08:41,020 --> 00:08:47,080
So if we plug in 2000 right here, 2000 divided by two.

135
00:08:47,290 --> 00:08:49,060
The remainder of that is zero.

136
00:08:49,360 --> 00:08:52,670
But if we plug it in 2001, the remainder is not zero.

137
00:08:52,690 --> 00:08:54,940
The remainder is one, meaning that it's odd.

138
00:08:55,600 --> 00:08:58,570
So again, this is above and beyond what you need to know.

139
00:08:58,570 --> 00:09:03,290
But I just figured it would come up in the comments if someone saw this gigantic mess here.

140
00:09:03,310 --> 00:09:05,590
Someone would probably say, there's a better way.

141
00:09:05,980 --> 00:09:06,820
And they're right.

142
00:09:07,270 --> 00:09:11,710
It just happens to not use in or not in, which is the whole point of this video.

143
00:09:11,710 --> 00:09:14,050
But I figured it was still worth talking about.

144
00:09:14,650 --> 00:09:18,970
So what we're doing selecting the same stuff title released here from books.

145
00:09:19,300 --> 00:09:26,110
First thing is the same only selecting books that are released after the year 2000 or including the

146
00:09:26,110 --> 00:09:26,950
year 2000.

147
00:09:27,070 --> 00:09:31,990
And then they also have to be odd years.

148
00:09:32,860 --> 00:09:35,200
So release year is not released.

149
00:09:35,210 --> 00:09:37,810
Your mod two is not equal to zero.

150
00:09:37,810 --> 00:09:43,420
Two does not divide cleanly and paste that in just so we have a record.

151
00:09:44,410 --> 00:09:45,100
Run it.

152
00:09:45,900 --> 00:09:49,590
And you can see now I only get the same exact result.

153
00:09:49,590 --> 00:09:50,130
Right.

154
00:09:50,550 --> 00:09:53,280
Let me order it so that we can compare.

155
00:09:57,490 --> 00:09:58,260
No.

156
00:09:58,300 --> 00:09:59,110
I have a typo.

157
00:10:01,130 --> 00:10:01,580
Okay.

158
00:10:02,060 --> 00:10:08,960
So we get the exact same results that we got earlier, but we didn't have to write all these years out

159
00:10:08,960 --> 00:10:09,770
explicitly.

160
00:10:09,770 --> 00:10:16,310
And the other advantage is this solution works when we're in 2018 and 2020 and whatever other even numbers

161
00:10:16,310 --> 00:10:21,500
we have, as long as they are even and greater than 2000, it will be filtered out.

162
00:10:21,590 --> 00:10:24,100
So this is, in my opinion, a better solution.

163
00:10:24,110 --> 00:10:26,630
It grows and it expands more.

164
00:10:27,020 --> 00:10:30,080
The other thing, there's a bit of performance difference.

165
00:10:30,080 --> 00:10:33,140
It's not major, but when we look at this.

166
00:10:33,900 --> 00:10:36,960
Basically, what if we're pretending?

167
00:10:36,960 --> 00:10:44,520
Let's personify or anthropomorphize my SQL and it's taking a year like 2013.

168
00:10:46,120 --> 00:10:48,850
When we have not in it's going to try.

169
00:10:48,880 --> 00:10:51,490
Is 2013 equal to 2000?

170
00:10:51,530 --> 00:10:53,680
No, it's equal to 2002.

171
00:10:53,710 --> 00:10:55,210
No is equal to 2004.

172
00:10:55,210 --> 00:10:56,590
And it's going to keep going down.

173
00:10:56,620 --> 00:10:57,820
It has to try every option.

174
00:10:57,820 --> 00:11:03,460
There's no way to know unless it actually matches it or tries to match it against every year we have

175
00:11:03,460 --> 00:11:04,240
in this set.

176
00:11:04,720 --> 00:11:11,080
So that could potentially take a lot more time than doing a single mathematical operation like we have

177
00:11:11,080 --> 00:11:11,530
here.

178
00:11:12,580 --> 00:11:17,380
We're just checking is the release here mod two not equal to zero?

179
00:11:18,310 --> 00:11:22,810
And it's really not a big difference, but it's just something that's nice to be aware of.

180
00:11:23,100 --> 00:11:25,440
Okay, so big tangent here.

181
00:11:25,450 --> 00:11:28,690
This video was really about in and not in.

182
00:11:28,690 --> 00:11:31,870
So just to go back to there, very useful.

183
00:11:32,830 --> 00:11:39,160
Allow us to pass in a set of numbers or strings or values, dates, whatever it is, which then our

184
00:11:39,160 --> 00:11:44,470
rows are checked against to see if they are either contained or not contained, depending on if we're

185
00:11:44,470 --> 00:11:45,880
using in or not in.

186
00:11:46,240 --> 00:11:46,840
All right.