1
00:00:00,630 --> 00:00:02,100
I hope you're still with us.

2
00:00:02,370 --> 00:00:04,980
I really hope it didn't put you to sleep with the last video.

3
00:00:05,220 --> 00:00:08,220
These are getting a little too long for my taste.

4
00:00:08,910 --> 00:00:09,990
Significantly too long.

5
00:00:09,990 --> 00:00:11,400
So I apologize for that.

6
00:00:11,730 --> 00:00:15,600
But there's a lot to cover here and there's no great place to break it up.

7
00:00:15,930 --> 00:00:19,890
However, we are done with kind of the long slog of explaining joints.

8
00:00:19,920 --> 00:00:21,930
Now I'm just going to show you another type.

9
00:00:22,200 --> 00:00:25,130
So inner join is what most people end up doing.

10
00:00:25,140 --> 00:00:28,380
That's a big generalization, but it's what I end up doing most of the time.

11
00:00:28,470 --> 00:00:33,450
But there are other types, and the next one that I'll show you is called the Left Joint.

12
00:00:33,900 --> 00:00:35,100
There's left and right.

13
00:00:35,100 --> 00:00:36,510
They work very similarly.

14
00:00:36,510 --> 00:00:39,840
So once you understand how one works, it makes it easier to understand the other.

15
00:00:39,960 --> 00:00:45,870
So what we saw so far was the inner join, where we take one table A and another table B and then we

16
00:00:45,870 --> 00:00:50,950
select all the records from A and B where the joint condition is met.

17
00:00:50,970 --> 00:00:52,640
But there's another type of joint.

18
00:00:52,650 --> 00:00:55,350
Before I get there, I want to show one other thing.

19
00:00:56,070 --> 00:01:01,680
If we go back to any of our joints that we had, let's take this one here.

20
00:01:03,210 --> 00:01:08,250
So this gives us a nice little table with Boy George and his orders.

21
00:01:08,250 --> 00:01:11,760
We have George Michael and his orders and Bette Davis in her orders.

22
00:01:12,360 --> 00:01:18,180
I can do just any regular MySQL functions and things that you're used to with this data.

23
00:01:18,180 --> 00:01:20,460
So I didn't make that very clear in the last video.

24
00:01:20,490 --> 00:01:22,270
This isn't some special table.

25
00:01:22,290 --> 00:01:23,520
We can do whatever we want.

26
00:01:23,520 --> 00:01:28,500
So I could do something like group buy or let's start even simpler.

27
00:01:29,520 --> 00:01:35,220
I could do an order by let's do an order by and let me actually move this.

28
00:01:35,220 --> 00:01:39,270
So we have a record of everything we've done, so I'll scroll down.

29
00:01:39,450 --> 00:01:40,980
Getting fancier.

30
00:01:41,700 --> 00:01:42,380
Here we go.

31
00:01:42,390 --> 00:01:45,210
So we can do an order by.

32
00:01:47,300 --> 00:01:50,900
The amount that if we wanted to.

33
00:01:52,720 --> 00:01:59,410
And now we have from the lowest order up to the highest order or we could order by order date.

34
00:02:01,900 --> 00:02:03,490
So that's one thing we can do.

35
00:02:04,390 --> 00:02:07,600
But we can also do things like group by.

36
00:02:08,199 --> 00:02:15,640
So maybe let's say this is something we actually might want to do is take all of our orders and group

37
00:02:15,640 --> 00:02:23,800
them by who ordered them, group them by who ordered them and then take that and find the average amount

38
00:02:23,800 --> 00:02:25,390
they spent or the total amount.

39
00:02:25,420 --> 00:02:26,340
How about we start there?

40
00:02:26,350 --> 00:02:32,650
Let's identify the biggest spender in our entire database, which it's pretty easy to do if you just

41
00:02:32,650 --> 00:02:34,000
look here and add things up.

42
00:02:34,000 --> 00:02:36,570
But let's say we had 10,000 records.

43
00:02:36,580 --> 00:02:38,710
How do we find the biggest spender?

44
00:02:39,460 --> 00:02:44,110
So we'll group buy and how do we group them?

45
00:02:44,110 --> 00:02:48,710
What we want a group by our customer who place the order.

46
00:02:48,730 --> 00:02:54,940
First name could work, but that assumes that we only have one Bettie or one George, and we might have

47
00:02:54,940 --> 00:02:57,400
George Michael and George Foreman or something.

48
00:02:57,400 --> 00:02:58,750
So that's not going to work.

49
00:02:58,870 --> 00:03:03,460
We could do first name, comma, last name, but we still could have people with the same name.

50
00:03:03,460 --> 00:03:08,980
So it's better to just group buy something we know is unique, like customer ID just like that.

51
00:03:09,490 --> 00:03:15,040
And let's be explicit orders customer ID, so group buy that.

52
00:03:15,280 --> 00:03:22,270
And if we hit enter, remember when we do group buy and we don't actually do any math or any functions

53
00:03:22,270 --> 00:03:27,370
on it, it looks like it just cut data out because that's just how it prints things to us.

54
00:03:27,370 --> 00:03:35,230
But now you can see that we have grouped by the customer ID, so I know it's hard to see because we're

55
00:03:35,230 --> 00:03:42,520
not displaying customer ID, but remember that customer ID is unique relative to every user.

56
00:03:42,520 --> 00:03:44,590
So we're basically grouping by customer.

57
00:03:45,160 --> 00:03:46,420
Hopefully that makes sense.

58
00:03:47,050 --> 00:03:49,750
You could make it a bit clearer if you did something like this.

59
00:03:51,760 --> 00:03:54,190
What we've done and let me get rid of the group by.

60
00:03:55,380 --> 00:03:56,220
Briefly.

61
00:03:57,180 --> 00:04:02,850
What we've done is take these customer IDs here that we weren't showing and just grouping these two

62
00:04:02,850 --> 00:04:06,330
together, these two together, and this one on its own.

63
00:04:07,980 --> 00:04:09,720
But we're not actually displaying that.

64
00:04:10,140 --> 00:04:12,630
So we can group by that like we did.

65
00:04:13,590 --> 00:04:16,920
And then all we want to do is total together the amounts.

66
00:04:17,399 --> 00:04:26,370
So rather than just amount, let's do some amount and I'm going to clean this up a little bit.

67
00:04:28,890 --> 00:04:32,190
And let's give it an alias as total spent.

68
00:04:33,090 --> 00:04:33,990
Just like that.

69
00:04:35,510 --> 00:04:35,750
Now.

70
00:04:35,750 --> 00:04:36,650
Let's try it.

71
00:04:38,130 --> 00:04:45,900
Cool so we can see Boy George spent 135, George Michael 813 and Bette Davis 450.

72
00:04:46,020 --> 00:04:53,430
And then lastly, we could order by total spent move our semicolon.

73
00:04:56,150 --> 00:04:59,900
Now you can see and actually let's do descending.

74
00:05:03,200 --> 00:05:07,100
Now you can see George Michael is definitely our biggest spender.

75
00:05:07,520 --> 00:05:10,100
813.17 cents.

76
00:05:10,550 --> 00:05:11,250
Perfect.

77
00:05:11,270 --> 00:05:13,850
Now, order date doesn't really make sense to have in there.

78
00:05:13,850 --> 00:05:19,670
So because we have multiple orders and it doesn't make sense just to show one.

79
00:05:19,670 --> 00:05:21,210
So maybe this makes more sense.

80
00:05:21,230 --> 00:05:21,980
There we go.

81
00:05:22,640 --> 00:05:28,160
So I just wanted to show that you can still do typical things that we're used to with the results of

82
00:05:28,160 --> 00:05:29,140
a join query.

83
00:05:29,150 --> 00:05:31,970
It's not some special alien type of a table.

84
00:05:32,750 --> 00:05:35,420
Now that brings us on to the left, join.

85
00:05:35,420 --> 00:05:37,280
So another way of joining our data.

86
00:05:38,270 --> 00:05:39,500
Here's the ugly diagram.

87
00:05:39,500 --> 00:05:41,120
I tried to make it a little better.

88
00:05:41,120 --> 00:05:42,140
The colors are still awful.

89
00:05:42,140 --> 00:05:42,860
I'm sorry.

90
00:05:42,890 --> 00:05:44,120
So what does it do?

91
00:05:44,570 --> 00:05:47,480
Well, it's going to take everything from the first table.

92
00:05:48,200 --> 00:05:52,370
So if customers is our table on the left, it will take every customer.

93
00:05:52,580 --> 00:05:55,070
And then on the right, it will try and match.

94
00:05:55,100 --> 00:06:00,620
Or it will match any or it will add any matching records from orders in our case.

95
00:06:01,100 --> 00:06:05,090
So these diagrams, just talking about it like this, I find, isn't that helpful.

96
00:06:05,090 --> 00:06:10,700
But basically think of it as it takes the union of where they match just like inter.

97
00:06:11,330 --> 00:06:15,890
But then it also does everything from the left table, from customers in our case.

98
00:06:16,430 --> 00:06:18,710
So let's test it out over in cloud nine.

99
00:06:18,980 --> 00:06:24,380
I have a comment here for left join and let's go take one of our joints that we did earlier.

100
00:06:24,950 --> 00:06:26,960
Like let's do this first one.

101
00:06:27,470 --> 00:06:29,360
And this was an inner join.

102
00:06:30,440 --> 00:06:37,010
And one thing I didn't mention is you can explicitly write in or join and I'll show you it does the

103
00:06:37,010 --> 00:06:38,720
exact same thing if you leave it off.

104
00:06:38,720 --> 00:06:41,570
It's implied that it's going to be an inner join.

105
00:06:41,570 --> 00:06:42,800
It's the most common type.

106
00:06:42,980 --> 00:06:50,030
What it does is we know it takes customers where the ID matches customer ID and that's all we get.

107
00:06:50,030 --> 00:06:52,100
That's the inner part that matches.

108
00:06:52,130 --> 00:07:01,430
Now, if we change this to be left and select everything, you'll see we get a couple of differences.

109
00:07:01,430 --> 00:07:04,580
So we still have to kind of ugly to look at.

110
00:07:04,580 --> 00:07:05,270
I'm sorry.

111
00:07:05,270 --> 00:07:11,240
We have Boy George in the order he placed in Boy George, another order, he placed George Michael to

112
00:07:11,240 --> 00:07:12,680
orders that match.

113
00:07:12,830 --> 00:07:15,410
But then we have two users who never placed orders.

114
00:07:15,410 --> 00:07:18,260
David Bowie and Blue Steel do not have orders.

115
00:07:18,620 --> 00:07:20,620
And so we end up with David Bowie.

116
00:07:20,630 --> 00:07:21,440
Blue Steel.

117
00:07:21,440 --> 00:07:22,610
No, no, no, no.

118
00:07:22,610 --> 00:07:27,260
For the orders portion and then Bette Davis and the order that she placed.

119
00:07:27,800 --> 00:07:32,300
So we have customers on the left, orders on the right.

120
00:07:33,140 --> 00:07:35,240
We're taking everything from customers.

121
00:07:35,240 --> 00:07:42,320
So that's why we end up with David Bowie and Blue Steel, and then we take the intersection as well

122
00:07:42,320 --> 00:07:45,140
where there is an applicable match.

123
00:07:45,290 --> 00:07:49,820
So for David Bowie and Blue Steel, there isn't one, and it fills in null.

124
00:07:49,970 --> 00:07:55,070
But for Boy George and George Michael and Betty Davis, there is a matching order.

125
00:07:55,820 --> 00:08:01,520
So I know it's kind of difficult to look at and understand, but think of it again.

126
00:08:01,520 --> 00:08:05,270
We have customers and we go through at the beginning.

127
00:08:05,570 --> 00:08:07,970
Boy George, is there a matching order?

128
00:08:08,240 --> 00:08:10,130
Yes, this one here.

129
00:08:10,130 --> 00:08:10,850
Right here.

130
00:08:10,850 --> 00:08:11,810
And this one.

131
00:08:11,930 --> 00:08:13,580
So put those together.

132
00:08:14,090 --> 00:08:15,800
George Michael ID of two.

133
00:08:15,830 --> 00:08:17,050
Is there any matching orders?

134
00:08:17,060 --> 00:08:18,350
Yes, there's two.

135
00:08:18,920 --> 00:08:21,350
Put those two together next to George Michael.

136
00:08:21,590 --> 00:08:22,670
David Bowie.

137
00:08:22,700 --> 00:08:24,050
Is there a matching order?

138
00:08:24,080 --> 00:08:24,820
No.

139
00:08:24,830 --> 00:08:27,830
So just put David Bowie because it's a left join.

140
00:08:28,100 --> 00:08:32,179
If it was an inner join, then David Bowie and Blue still don't show up.

141
00:08:32,179 --> 00:08:38,059
But it's a left join, which means we're taking everything from the left table and it works perfectly

142
00:08:38,990 --> 00:08:41,270
well, depending on what you're trying to do.

143
00:08:41,659 --> 00:08:44,480
So that brings us to the question why would you do this?

144
00:08:44,480 --> 00:08:49,940
And before we get there, I'm going to just clean this one up again so that we can see it a little bit

145
00:08:49,940 --> 00:08:50,690
easier.

146
00:08:50,780 --> 00:09:00,110
Let's only select first name, last name, order, date and amount, just like before.

147
00:09:01,250 --> 00:09:03,980
So that brings us again to the question why would you do this?

148
00:09:03,980 --> 00:09:08,420
Why would you want to have excess information like David Bowie and Blue Steel?

149
00:09:08,870 --> 00:09:10,820
And it depends on what you're doing.

150
00:09:10,820 --> 00:09:17,390
And in this case, maybe I previously I wanted to see all the orders that have been placed and names

151
00:09:17,390 --> 00:09:18,170
next to them.

152
00:09:18,170 --> 00:09:24,380
But now what if I want to tabulate the high spenders on our site, but I want to include people who

153
00:09:24,380 --> 00:09:25,550
haven't spent anything.

154
00:09:25,550 --> 00:09:29,390
So I just want to be able to see for every user how much have they spent.

155
00:09:29,660 --> 00:09:34,850
And I'd be able to go through and say, maybe send an email to the people who have spent a lot.

156
00:09:34,970 --> 00:09:39,050
Och, thanks for being a loyal customer versus people who haven't spent.

157
00:09:39,050 --> 00:09:43,970
I could go through and send an email like Here's a coupon for your first purchase, 10% off or something

158
00:09:43,970 --> 00:09:44,270
like that.

159
00:09:44,270 --> 00:09:44,690
Right?

160
00:09:44,690 --> 00:09:51,620
Where I want to basically get some insight where it involves knowing everything about all users and

161
00:09:51,620 --> 00:09:54,500
some of them have corresponding orders and some of them don't.

162
00:09:55,220 --> 00:09:59,570
Now that brings us to another topic, which is okay, but we have nulls here.

163
00:09:59,960 --> 00:10:00,950
So.

164
00:10:01,570 --> 00:10:03,190
Is there a way to change that?

165
00:10:03,520 --> 00:10:07,510
Let's say I wanted to do the same thing that we had up here where we had our high spenders.

166
00:10:07,510 --> 00:10:08,110
Right?

167
00:10:08,320 --> 00:10:14,940
So we selected and we summed all the orders based off of grouping them by customer ID.

168
00:10:14,950 --> 00:10:19,420
So it's a lot again, but let's do that here with a left join.

169
00:10:19,420 --> 00:10:20,860
And there's going to be a slight difference, right?

170
00:10:20,890 --> 00:10:22,330
Because we have nulls.

171
00:10:22,720 --> 00:10:28,270
So if we do this select first name, last name, order, date and amount from customers left, join

172
00:10:28,270 --> 00:10:32,380
orders and we do a group by customer state ID this time.

173
00:10:32,380 --> 00:10:38,830
So that will group by customers so that we have these two grouped these two grouped on its own, on

174
00:10:38,830 --> 00:10:41,410
its own, on her own and actually blue as a girl.

175
00:10:41,410 --> 00:10:47,110
I'm sorry, blue OC So if we do this we won't see much difference.

176
00:10:47,110 --> 00:10:48,040
But we will see a difference.

177
00:10:48,040 --> 00:10:49,570
But it's not very meaningful.

178
00:10:49,630 --> 00:10:50,680
What am I missing?

179
00:10:50,890 --> 00:10:52,240
Extra semicolon.

180
00:10:53,620 --> 00:10:58,600
Not that useful because it just condenses our data and hides stuff.

181
00:10:58,600 --> 00:11:01,570
But if we go through and I'll format this again.

182
00:11:04,210 --> 00:11:05,200
But some together.

183
00:11:05,200 --> 00:11:06,250
Everything, right?

184
00:11:06,460 --> 00:11:07,630
So will some.

185
00:11:07,840 --> 00:11:15,160
And inside of here, we want to some amount and we don't actually need to have a mount there and we

186
00:11:15,160 --> 00:11:16,260
can get rid of order date too.

187
00:11:16,270 --> 00:11:19,540
So let's just have the first last name and then some.

188
00:11:20,830 --> 00:11:21,500
Okay.

189
00:11:21,640 --> 00:11:23,380
So this is accurate.

190
00:11:23,380 --> 00:11:24,040
That's accurate.

191
00:11:24,040 --> 00:11:24,950
That's accurate.

192
00:11:24,970 --> 00:11:31,630
We still have Noel here, which it can work, but it would be nice if instead it just said zero because

193
00:11:31,630 --> 00:11:33,250
they've spent $0.

194
00:11:33,400 --> 00:11:34,900
So how do we do that?

195
00:11:34,900 --> 00:11:36,470
And this is not an integer.

196
00:11:36,660 --> 00:11:39,220
This is not a join specific thing in any way.

197
00:11:39,250 --> 00:11:41,470
This is just an interesting problem.

198
00:11:41,470 --> 00:11:43,060
How do we get instead of null?

199
00:11:43,060 --> 00:11:44,920
How do I replace that with zero?

200
00:11:45,430 --> 00:11:46,810
And there's a couple of ways.

201
00:11:46,810 --> 00:11:52,330
The first really lengthy, awful way is to use a case statement so I could have a case statement where

202
00:11:52,330 --> 00:11:55,360
I checked is some amount is null.

203
00:11:55,360 --> 00:12:01,810
Remember, I can't just say equals null, but if I said it's is null, then do zero.

204
00:12:02,440 --> 00:12:04,210
Otherwise do some amount.

205
00:12:04,210 --> 00:12:05,860
But that's that's kind of long.

206
00:12:06,220 --> 00:12:14,170
So it's going to be much easier if I use what's known as if NULL And I haven't shown this to you yet

207
00:12:14,560 --> 00:12:17,440
because we didn't really have many cases where we had null data.

208
00:12:17,440 --> 00:12:18,610
But here's one.

209
00:12:18,610 --> 00:12:19,810
So what if NULL does?

210
00:12:19,810 --> 00:12:21,160
It takes two arguments.

211
00:12:21,160 --> 00:12:25,030
The first one is the thing you want it to check if it is null or not.

212
00:12:25,030 --> 00:12:28,480
So we want to check is sum of amount null.

213
00:12:28,960 --> 00:12:31,000
So here it's not right?

214
00:12:31,000 --> 00:12:31,450
It's not.

215
00:12:31,450 --> 00:12:32,770
No, it's not null.

216
00:12:32,800 --> 00:12:33,820
Here it is.

217
00:12:34,180 --> 00:12:40,600
So then the second argument is what we want to replace it with, if it is null and we'll just say zero.

218
00:12:41,500 --> 00:12:42,760
So it's very simple.

219
00:12:42,940 --> 00:12:47,350
It checks if the first argument is null, if it is, it replaces it with the second argument.

220
00:12:47,350 --> 00:12:49,810
If it's not, it leaves it as the first.

221
00:12:50,410 --> 00:12:54,730
So now if I try it perfect, we get zero.

222
00:12:55,540 --> 00:12:57,250
We should definitely add an alias.

223
00:12:57,250 --> 00:12:59,230
Let's call it total spent again.

224
00:13:02,090 --> 00:13:07,610
One other thing that would be nice is to order by total spent.

225
00:13:08,600 --> 00:13:10,970
And this time let's do it from least.

226
00:13:11,090 --> 00:13:15,530
So we'll just do order by total spent and that should work.

227
00:13:15,560 --> 00:13:16,440
There we go.

228
00:13:16,460 --> 00:13:18,680
And now you can see we have zero.

229
00:13:20,540 --> 00:13:22,310
So we kind of talked about a lot.

230
00:13:22,340 --> 00:13:24,530
There's a couple topics that touch on each other.

231
00:13:24,560 --> 00:13:30,620
The first thing just to recap was that I wanted to show you when we do joints.

232
00:13:30,650 --> 00:13:31,310
Any joints?

233
00:13:31,310 --> 00:13:33,050
They're not fancy special tables.

234
00:13:33,050 --> 00:13:35,180
They work just the same way as any other table.

235
00:13:35,180 --> 00:13:38,030
So we can do things like group by an order by.

236
00:13:38,510 --> 00:13:41,900
Then the next thing was that we saw inner joints.

237
00:13:42,920 --> 00:13:47,510
If I go back, we saw this and then we also saw a left joint.

238
00:13:47,510 --> 00:13:52,580
And the key difference, they both join data from two tables, but the key difference is what data they

239
00:13:52,580 --> 00:13:53,090
join.

240
00:13:53,090 --> 00:13:54,410
So we give them a condition.

241
00:13:54,410 --> 00:14:01,580
But even given the same condition, in our case it was join on customer ID equals orders.

242
00:14:01,790 --> 00:14:04,850
Customer ID this middle part and on inner join.

243
00:14:04,850 --> 00:14:07,340
That's all we get is the exact overlap.

244
00:14:07,370 --> 00:14:08,510
On a left join.

245
00:14:08,510 --> 00:14:11,480
We also get the left part in addition.

246
00:14:11,480 --> 00:14:18,170
So every customer in our case which can be useful if we wanted to see things like which customers hadn't

247
00:14:18,170 --> 00:14:20,840
ordered things, which is what we got here.

248
00:14:20,930 --> 00:14:23,300
So hopefully that explanation made some sense.

249
00:14:23,930 --> 00:14:24,830
I totally understand.

250
00:14:24,830 --> 00:14:30,080
If it didn't, this is a a tough thing to really wrap your head around at first.

251
00:14:30,110 --> 00:14:35,330
Now, it's not terribly difficult once you get going with it, but understanding it, it can be super

252
00:14:35,330 --> 00:14:36,050
intimidating.

253
00:14:36,050 --> 00:14:40,040
So if you're in that situation, we will have exercises coming up.

254
00:14:40,040 --> 00:14:41,930
And just one more video after this.

255
00:14:42,530 --> 00:14:44,510
And I recommend you try and go through those.

256
00:14:44,840 --> 00:14:48,720
And if that's not enough, then absolutely ask for help in the forums here.

257
00:14:48,740 --> 00:14:54,170
This is one of the crucial things that, in my experience, sometimes take students a couple of maybe

258
00:14:54,170 --> 00:14:58,040
hours, a couple of days, not not like 12, 20 hour days.

259
00:14:58,040 --> 00:15:03,770
I just mean sleeping on it once or twice to help kind of get some space in your brain.

260
00:15:03,860 --> 00:15:08,810
Anyways, hopefully you're able to kind of get where I'm going with this next step.

261
00:15:08,810 --> 00:15:11,480
We're going to see our last type of join, which is a right join.

262
00:15:11,480 --> 00:15:12,260
Very simple.

263
00:15:12,800 --> 00:15:17,360
Well, it's not simple, but very easy to grasp if you understand left joints.