1
00:00:00,090 --> 00:00:07,170
So moving right along our next operator will take a look at is called between and between is pretty

2
00:00:07,170 --> 00:00:13,440
straightforward it allows us to select things based off of two values an upper and lower range.

3
00:00:13,440 --> 00:00:23,100
So I can say I want to find all people who have a birthday between April 20th of 2017 and April 22nd

4
00:00:23,130 --> 00:00:24,300
of 2017.

5
00:00:24,300 --> 00:00:32,009
Or I want to find all books that have page counts between 105 hundred pages so we actually can do this

6
00:00:32,009 --> 00:00:34,080
already using what you know.

7
00:00:34,080 --> 00:00:40,020
So before I show you between, how can we accomplish the same thing using the tools that we already

8
00:00:40,020 --> 00:00:43,110
have, things that we learned in this section already.

9
00:00:43,140 --> 00:00:48,960
Because if you take a moment to think about what being between two values actually means, it's really

10
00:00:48,960 --> 00:00:51,840
just a combination of greater than and less than.

11
00:00:52,880 --> 00:00:55,780
So how do we combine two different conditions?

12
00:00:55,790 --> 00:00:57,110
Well, logical.

13
00:00:57,110 --> 00:01:00,350
And so let's try an example.

14
00:01:00,350 --> 00:01:05,720
Again, just to reiterate, if I wasn't clear, we're doing it without the new between operator.

15
00:01:05,720 --> 00:01:06,590
We haven't seen that yet.

16
00:01:06,590 --> 00:01:09,290
We're doing it the longer way, if you will.

17
00:01:09,380 --> 00:01:18,560
So select all books published between the years 2004 and 2015, and that includes 2004 and 2015, just

18
00:01:18,560 --> 00:01:19,280
to be clear.

19
00:01:20,550 --> 00:01:27,300
So to do that we use logical end to join together to different conditions, to components.

20
00:01:27,660 --> 00:01:30,080
So select title and release gear, blah, blah, blah.

21
00:01:30,150 --> 00:01:38,580
That doesn't matter from books where release here is greater than or equal to 2004 and release year

22
00:01:38,580 --> 00:01:40,620
is less than or equal to 2015.

23
00:01:40,620 --> 00:01:47,250
So both parts that have to be true meaning that whatever the book is that selected or all of the books

24
00:01:47,250 --> 00:01:56,040
that are selected are somewhere between 2004, including 2004 in 2015, including 2015.

25
00:01:56,880 --> 00:01:59,580
So I won't make you watch me type this whole thing.

26
00:02:00,420 --> 00:02:01,830
I'm just going to paste it over here.

27
00:02:01,830 --> 00:02:12,090
But let's start off by just doing select title and release year from books and just check just we know

28
00:02:12,090 --> 00:02:17,400
this, but just to verify that there are things outside that range and now.

29
00:02:18,870 --> 00:02:26,700
If we paste in using the ampersand, ampersand and operator, which of course could also be written

30
00:02:26,700 --> 00:02:34,290
this way, if I could type correctly, you'll see that we get things that are between 2004 and 2015,

31
00:02:34,290 --> 00:02:38,340
and we know that it is inclusive, as you can see, because 2004 is here.

32
00:02:39,870 --> 00:02:43,440
And if we hadn't included that equal sign, then it would not be there.

33
00:02:44,760 --> 00:02:49,530
So really what I'm trying to show you is that you don't need a fancy between operator to accomplish

34
00:02:49,530 --> 00:02:50,130
this.

35
00:02:50,220 --> 00:02:57,780
You can just do it using good old fashioned logic, using greater than less than and logical end.

36
00:02:57,870 --> 00:03:02,550
However, there is an operator between that will make it easier.

37
00:03:03,210 --> 00:03:04,530
So it looks like this.

38
00:03:04,890 --> 00:03:10,650
The syntax is between and then some value and some other value.

39
00:03:11,130 --> 00:03:12,570
This and here.

40
00:03:12,570 --> 00:03:13,050
Just.

41
00:03:13,590 --> 00:03:14,010
I get it.

42
00:03:14,010 --> 00:03:21,030
It could be confusing when we also have this and and this one, but they are different.

43
00:03:21,030 --> 00:03:24,330
So this end is paired with between always.

44
00:03:24,480 --> 00:03:25,660
So here's an example.

45
00:03:25,680 --> 00:03:33,210
Select title and release here again from books where released here between 2004 and 2015.

46
00:03:33,360 --> 00:03:36,570
So this does the exact same thing as this right here.

47
00:03:36,570 --> 00:03:39,930
But we don't have to use a greater than equal to, less than equal to.

48
00:03:39,960 --> 00:03:42,030
We don't have to write release here twice.

49
00:03:42,060 --> 00:03:47,070
All we have to do is say we're released here between 2004 and 2015.

50
00:03:47,370 --> 00:03:52,980
And in my opinion, this is much easier to read, much easier to remember if you're trying to do this

51
00:03:53,100 --> 00:03:55,860
between it's a pretty straightforward thing to recall.

52
00:03:55,890 --> 00:03:57,330
It does what it sounds like.

53
00:03:57,810 --> 00:03:59,730
So let's try it out.

54
00:04:01,020 --> 00:04:01,950
Just copy it.

55
00:04:03,300 --> 00:04:03,930
We'll bring it here.

56
00:04:03,930 --> 00:04:08,850
So we have a record in the note and then I'll just paste it in select title release here from books

57
00:04:08,850 --> 00:04:11,310
where release year between 2004 and 2015.

58
00:04:11,700 --> 00:04:16,470
And as you can see, we get the same results that we did when we use this version.

59
00:04:16,860 --> 00:04:21,360
It's just a little shorter and cleaner and easier to understand, in my opinion.

60
00:04:22,050 --> 00:04:23,490
And again, it is inclusive.

61
00:04:23,490 --> 00:04:24,990
We have 2004 there.

62
00:04:25,770 --> 00:04:30,000
And one thing to keep in mind is that there may be times where you don't want it to be inclusive and

63
00:04:30,000 --> 00:04:31,920
there's not an easy way just to do that.

64
00:04:32,100 --> 00:04:36,570
Well, there is an easy way, but basically it just means change this number.

65
00:04:36,570 --> 00:04:42,870
If you want it to be 2005 to 2014, you could do that.

66
00:04:43,920 --> 00:04:49,980
But there's no easy way to do between non inclusive like here where we can just eliminate the equal

67
00:04:49,980 --> 00:04:50,460
sign.

68
00:04:51,560 --> 00:04:58,740
Okay, so one other thing is that not between is also a thing and it's pretty straightforward.

69
00:04:58,740 --> 00:05:01,890
Basically, it does the opposite of between.

70
00:05:01,890 --> 00:05:09,300
So we could say I want all the books that have a page length that is not between 105 hundred and that

71
00:05:09,300 --> 00:05:12,930
gives us everything less than 100 and everything greater than 500.

72
00:05:13,780 --> 00:05:15,280
So here's our same example.

73
00:05:15,310 --> 00:05:20,480
If we do select title and release year from books where released year is not between.

74
00:05:20,500 --> 00:05:26,080
So all that changes is we add OT before not between 2004 and 2015.

75
00:05:27,100 --> 00:05:32,110
It takes that range from 2004 and 2015 and does everything outside of that.

76
00:05:33,010 --> 00:05:36,640
So I'll just paste it here and then paste it in again.

77
00:05:37,240 --> 00:05:39,850
And now you can see let's do an order by.

78
00:05:47,680 --> 00:05:52,540
Now you can see we have everything up until 2004.

79
00:05:53,790 --> 00:06:01,380
And then everything after 2015, but nothing from 2000 for up to 2015, they're gone.

80
00:06:01,920 --> 00:06:03,240
These are just the leftovers.

81
00:06:04,950 --> 00:06:08,280
So that's pretty much it for the basics of between and not between.

82
00:06:08,550 --> 00:06:12,630
So before I let you go, there is one small side note I just want to bring up.

83
00:06:12,810 --> 00:06:17,940
It's not crucial to understanding between, but it's just something that the documentation mentions.

84
00:06:17,940 --> 00:06:19,620
So I figured I should also mention it.

85
00:06:20,310 --> 00:06:23,500
If we go to the doctor between there is this note at the end.

86
00:06:23,520 --> 00:06:29,400
For best results when using between with date or time values use cast to explicitly convert the values

87
00:06:29,400 --> 00:06:30,840
to the desired data type.

88
00:06:31,320 --> 00:06:33,230
So we haven't seen this cast.

89
00:06:33,240 --> 00:06:34,440
We'll talk about it in a moment.

90
00:06:34,440 --> 00:06:41,550
But what it lets us do is convert from one data type to another from a date to a date time, for instance,

91
00:06:41,640 --> 00:06:46,710
or from a string of our char to a date.

92
00:06:48,090 --> 00:06:51,000
For example, if you compare date time to to date values.

93
00:06:51,000 --> 00:06:57,510
So we're trying to say if some date time is between two different dates, it's best to convert the date

94
00:06:57,510 --> 00:06:59,460
values to also be date times.

95
00:06:59,550 --> 00:07:03,480
Just so you're working with the same kind of consistent data type.

96
00:07:03,960 --> 00:07:10,050
If you use a string constant such as 2001, dash one, dash one and it comparison to a date, it's better

97
00:07:10,050 --> 00:07:11,850
to cast the string to a date.

98
00:07:12,330 --> 00:07:15,180
So let's just take a look at how cast works first.

99
00:07:15,600 --> 00:07:16,890
And it's pretty simple.

100
00:07:16,890 --> 00:07:19,140
So let's take something like this.

101
00:07:19,140 --> 00:07:27,120
2017 0502 That right there is not technically a date, right?

102
00:07:27,120 --> 00:07:30,660
That's not considered a date data type.

103
00:07:30,660 --> 00:07:33,540
It's just text, even though it looks like a date.

104
00:07:33,540 --> 00:07:35,940
And it will it can be treated like one.

105
00:07:35,940 --> 00:07:37,860
My SQL is smart enough to figure that out.

106
00:07:37,860 --> 00:07:45,450
If we pass it into a date function like days or months or format date, but it's not actually a date.

107
00:07:46,220 --> 00:07:53,270
But if we wanted to make it an actual date time, we can cast it and this will work its magic.

108
00:07:53,300 --> 00:07:55,100
Now, just casting it like this won't do anything.

109
00:07:55,100 --> 00:07:57,240
I have to say what to cast it as.

110
00:07:57,260 --> 00:08:01,700
So let's cast it as a date time and we won't see anything unless we have select here.

111
00:08:02,090 --> 00:08:06,910
So what this will do is it takes first thing whatever it is, and it tries to convert it to a date time.

112
00:08:06,920 --> 00:08:11,300
So if there's just the number 99 or something, that will cause a problem.

113
00:08:11,480 --> 00:08:14,840
But in this case, this is easily cast to a date time.

114
00:08:14,840 --> 00:08:17,730
But you might be wondering, okay, the date part is easy to cast.

115
00:08:17,750 --> 00:08:19,070
What about the time part?

116
00:08:19,550 --> 00:08:22,520
Well, it just picks 0000 for you.

117
00:08:22,850 --> 00:08:25,700
So it's now considered a date time.

118
00:08:26,330 --> 00:08:31,820
So basically what the documentation is saying is if you're working with dates and you're trying to compare

119
00:08:31,820 --> 00:08:38,330
them and you're working with between, for example, it's best to use a cast to make sure that all the

120
00:08:38,330 --> 00:08:39,679
data types are the same.

121
00:08:40,159 --> 00:08:42,380
Now, it actually this is kind of confusing.

122
00:08:42,380 --> 00:08:47,270
It will work most of the time just fine if you don't, because my skill is smart enough to figure out

123
00:08:47,270 --> 00:08:51,410
what you want, but it can cause problems, so it's just best to do that.

124
00:08:51,620 --> 00:08:59,420
So I'm going to switch over to that testing database I made with this wonderful name Use testing.

125
00:08:59,420 --> 00:09:03,550
DB And the only reason I'm doing this is so that we have some date data.

126
00:09:03,560 --> 00:09:08,990
So we have a people table and they have a birth date, birth time and birth date time.

127
00:09:09,200 --> 00:09:17,210
So let's say I want to select people who have a birth date time between two dates, let's say between

128
00:09:17,210 --> 00:09:18,500
1980.

129
00:09:19,280 --> 00:09:23,420
Let's do January 1st, 1980, and January 1st, 2000.

130
00:09:24,770 --> 00:09:26,330
So I'm going to write it up here.

131
00:09:26,750 --> 00:09:38,930
But the basics would be select name and we'll do birth date time from people where birth date time between.

132
00:09:39,800 --> 00:09:53,030
And if we just do this 2000 or would I say 1980 dash 0101 and 2000.

133
00:09:53,030 --> 00:09:54,770
Dash 0101.

134
00:09:55,400 --> 00:10:00,890
If I just do this on its own, first thing that we're doing here is these are strings, right?

135
00:10:00,890 --> 00:10:01,910
These aren't even dates.

136
00:10:01,910 --> 00:10:04,460
Technically it's text.

137
00:10:04,460 --> 00:10:07,730
But if I do this, it still works.

138
00:10:07,730 --> 00:10:09,380
And that's what I was mentioning.

139
00:10:09,380 --> 00:10:13,070
That's kind of weird is that it works fine enough because my was smart enough.

140
00:10:13,070 --> 00:10:14,870
It figures out, okay, this is a date time.

141
00:10:14,870 --> 00:10:17,990
We're trying to compare it, see if it's between these two things.

142
00:10:17,990 --> 00:10:18,740
What are these?

143
00:10:18,740 --> 00:10:22,220
Well, they're not date times, but we still can handle it.

144
00:10:22,220 --> 00:10:28,550
There's some contingency code written back in my SQL to make that work, but it's much smarter and more

145
00:10:28,550 --> 00:10:39,440
fail proof to do this, cast this as date time, and I'll do this second part on a new line and cast

146
00:10:39,440 --> 00:10:47,180
this as a date time as well, so that we're working with date time, date time, date time.

147
00:10:48,260 --> 00:10:50,030
And you won't see a difference here.

148
00:10:50,890 --> 00:10:55,930
But you can rest easier, can sleep better at night knowing that you're doing the right thing.

149
00:10:57,070 --> 00:10:57,580
We're done.