1
00:00:00,060 --> 00:00:04,950
So let's suppose we're inheriting someone else's database and they decided not to use foreign.

2
00:00:04,980 --> 00:00:07,980
They didn't know about foreign key constraints, so they didn't use it.

3
00:00:07,980 --> 00:00:14,100
And then they had users they used to have all the way up until 109 users or customers.

4
00:00:14,100 --> 00:00:15,960
And then a lot of people left.

5
00:00:15,960 --> 00:00:17,630
They just deleted their accounts.

6
00:00:17,640 --> 00:00:18,990
Let's say something bad happened.

7
00:00:18,990 --> 00:00:20,550
There was a hack, a data breach.

8
00:00:20,550 --> 00:00:21,930
Everyone got scared away.

9
00:00:21,990 --> 00:00:24,960
And then all we were left with are these customers.

10
00:00:27,690 --> 00:00:28,680
It's pretty sad.

11
00:00:28,680 --> 00:00:35,980
So, I mean, it's a great bunch of customers to have, honestly, but still only five compared to 109.

12
00:00:36,000 --> 00:00:38,160
Keep in mind, this is all a dumb made up story.

13
00:00:38,640 --> 00:00:47,130
But what we could do is use a right join to basically identify all of our customer and orders and their

14
00:00:47,130 --> 00:00:52,710
associated orders as well as any orders that don't have an associated customer.

15
00:00:52,710 --> 00:00:54,330
So we could fill in null.

16
00:00:54,840 --> 00:01:00,800
So if we do our join that we had done earlier, I'm just going to scroll up to save times or save time.

17
00:01:00,810 --> 00:01:06,120
If we do this, join here, which is an inner join or that's actually a left join.

18
00:01:06,120 --> 00:01:09,810
Let's do this one right here.

19
00:01:13,590 --> 00:01:14,640
Nothing changes, right?

20
00:01:14,640 --> 00:01:18,270
We still have Boy George and his associated two orders.

21
00:01:18,270 --> 00:01:21,780
George Michael, his two orders, Bette Davis, her one order.

22
00:01:22,440 --> 00:01:30,870
And then if I did a left join, which we could just do by simply changing that one inner to be left.

23
00:01:30,870 --> 00:01:38,820
Or we can just do this here and I'm only choosing this one because rather than Star, this has a smaller

24
00:01:38,820 --> 00:01:40,830
subset of data that's easier to look at.

25
00:01:43,020 --> 00:01:49,290
So now we end up with this, which still is unchanged from when we did it in the last video, because

26
00:01:49,290 --> 00:01:54,570
there are orders that have no customers, but they're not here because we did a left join and the left

27
00:01:54,570 --> 00:01:59,610
join takes everything from customers and any associated matching data from orders.

28
00:02:00,000 --> 00:02:08,850
But now if we do the same thing down here, if you can hear my cat meowing and we just change it to

29
00:02:08,850 --> 00:02:10,080
be right, join.

30
00:02:11,190 --> 00:02:15,930
So we're still just doing first name, last name, order, date and amount from customers on the left

31
00:02:16,410 --> 00:02:18,120
or from on the left is the wrong term.

32
00:02:18,120 --> 00:02:25,830
But from customer's first right, join with orders so that we take everything from orders like this.

33
00:02:25,830 --> 00:02:29,340
Everything from orders in any overlap from customers.

34
00:02:30,510 --> 00:02:31,320
Let's try it.

35
00:02:33,620 --> 00:02:36,670
And you can see exactly what we expected happened.

36
00:02:36,680 --> 00:02:41,210
So we still have all of our orders that match customers and orders.

37
00:02:41,210 --> 00:02:46,790
But then we have those two orphans that we added in where there is no matching customer.

38
00:02:46,790 --> 00:02:48,800
So we end up with null firstname.

39
00:02:48,800 --> 00:02:51,410
NULL Last name null first name, no last name.

40
00:02:52,070 --> 00:02:55,700
So that's basically all I wanted to show you with right joints.

41
00:02:55,700 --> 00:03:01,250
But like I said, we will have actually relevant, useful examples later.

42
00:03:01,250 --> 00:03:06,550
But the last thing I wanted to do was hit you with a whole bunch of setup brand new data just so that

43
00:03:06,560 --> 00:03:08,360
you could see how a write join works.

44
00:03:08,360 --> 00:03:11,390
So there is this is a hypothetical situation that could happen.

45
00:03:11,390 --> 00:03:13,700
We could have orphan data like this.

46
00:03:14,030 --> 00:03:15,350
So I'm going to show you two things.

47
00:03:15,350 --> 00:03:21,950
Now, the first one is that we can still just do any anything that we want with this data like we did

48
00:03:21,950 --> 00:03:22,430
earlier.

49
00:03:22,430 --> 00:03:24,320
We can do more complex selections.

50
00:03:24,320 --> 00:03:30,200
And I'm also going to show you a new constraint you can add to your schema so that when something is

51
00:03:30,200 --> 00:03:35,240
deleted that has a foreign key, depending on it, it will automatically delete the dependent record.

52
00:03:35,240 --> 00:03:37,250
But to start I just wanted to show it.

53
00:03:37,250 --> 00:03:42,710
We can still do things like group by or we can do things like order by.

54
00:03:42,710 --> 00:03:44,330
So we can add that in here.

55
00:03:44,360 --> 00:03:49,940
Let's do order by and let's do first name just like that.

56
00:03:53,180 --> 00:03:53,660
Okay.

57
00:03:54,890 --> 00:03:56,690
What about if we tried to group buy?

58
00:03:57,110 --> 00:03:59,840
What if we did group buy?

59
00:04:00,350 --> 00:04:04,400
And what if we tried to group buy customer ID?

60
00:04:04,910 --> 00:04:06,110
What happens then?

61
00:04:09,990 --> 00:04:12,060
So it doesn't look like all that much.

62
00:04:12,060 --> 00:04:12,540
Right.

63
00:04:12,960 --> 00:04:14,490
But we can still do our same.

64
00:04:14,670 --> 00:04:16,220
We have Boy George was condensed.

65
00:04:16,230 --> 00:04:17,610
George Michael condensed.

66
00:04:17,910 --> 00:04:24,120
But we can do a sum for amount just like we've been doing.

67
00:04:25,500 --> 00:04:27,480
And we can see how much everyone has spent.

68
00:04:27,510 --> 00:04:28,140
Boy George.

69
00:04:28,140 --> 00:04:28,890
135.

70
00:04:28,920 --> 00:04:30,510
George Michael 813.

71
00:04:30,540 --> 00:04:31,960
Bette Davis 450.

72
00:04:31,980 --> 00:04:33,750
We still have these two nulls.

73
00:04:34,620 --> 00:04:39,990
So this is where we can start to make decisions and do things like, okay, if first name is null,

74
00:04:39,990 --> 00:04:42,210
then let's replace it with something.

75
00:04:43,020 --> 00:04:47,610
So I'm going to format this a bit and as we start to get more and more.

76
00:04:48,300 --> 00:04:53,340
And we'll replace first name and last name with if null.

77
00:04:53,490 --> 00:04:54,420
First name.

78
00:04:56,130 --> 00:04:57,180
And remember how this works.

79
00:04:57,180 --> 00:05:01,110
If first name is null, then it will be replaced with whatever we pass in.

80
00:05:01,110 --> 00:05:04,320
So we'll say we'll just say missing.

81
00:05:06,330 --> 00:05:09,810
And then we can do the same thing for last name if null.

82
00:05:11,430 --> 00:05:14,190
Last name, comma, we'll say user.

83
00:05:14,640 --> 00:05:18,600
So we'll end up with missing user if we do it that way.

84
00:05:21,510 --> 00:05:26,010
If we don't have an error or extra comma here, now we do it.

85
00:05:28,270 --> 00:05:34,330
And let's use aliases as first as last.

86
00:05:35,800 --> 00:05:36,640
All right.

87
00:05:36,730 --> 00:05:37,690
Got a lot going on.

88
00:05:38,380 --> 00:05:40,540
So now we have missing user.

89
00:05:40,900 --> 00:05:47,620
And let's say we want to see what the total of our orders are where we don't have a user.

90
00:05:47,620 --> 00:05:50,860
So basically the orphans, we want to group those together as well.

91
00:05:50,860 --> 00:05:57,430
Right now we're grouping by customer ID, but what if I wanted to group these to what we could do is

92
00:05:57,430 --> 00:06:04,660
change customer ID to be first name and we'd want to do comma last name rather than just first name

93
00:06:04,660 --> 00:06:07,180
because we may have multiple people with the same first name.

94
00:06:07,480 --> 00:06:09,370
So if we do this now.

95
00:06:12,990 --> 00:06:21,750
You'll see that we end up with missing user has 801 total spent than Bette Davis 450 and so on and we

96
00:06:21,750 --> 00:06:26,730
could order it, I think by now you understand what I'm trying to show, that these tables aren't special.

97
00:06:27,210 --> 00:06:31,770
You just have to be comfortable working with NULL and if NULL is kind of your best friend when you're

98
00:06:31,770 --> 00:06:32,430
doing that.

99
00:06:32,940 --> 00:06:33,300
Okay.

100
00:06:33,450 --> 00:06:37,200
So then the very last thing I wanted to show, keep doing that.

101
00:06:37,350 --> 00:06:37,960
Go away.

102
00:06:37,980 --> 00:06:45,990
The last thing I wanted to show was how we do what's known as on Delete Cascade.

103
00:06:46,350 --> 00:06:48,360
So this is what I was talking about.

104
00:06:48,360 --> 00:06:52,980
When we delete, if we have a foreign key relationship, when we delete a parent.

105
00:06:52,980 --> 00:06:59,460
So if we delete customer's table, not the whole table, but let's say we delete Bette Davis, we want

106
00:06:59,460 --> 00:07:01,200
her order to also be deleted.

107
00:07:01,200 --> 00:07:02,790
So it's not just totally on its own.

108
00:07:02,790 --> 00:07:04,710
We don't end up with this situation.

109
00:07:04,980 --> 00:07:06,180
So how do we do that?

110
00:07:06,180 --> 00:07:07,620
Well, I'll show you right now.

111
00:07:07,650 --> 00:07:10,230
If you're not interested, then go ahead and Skip.

112
00:07:10,230 --> 00:07:13,770
This isn't about joins, but I figured it's a good place to put it.

113
00:07:13,980 --> 00:07:16,500
I'm going to actually drop our tables again.

114
00:07:21,260 --> 00:07:22,850
I'm going to redo.

115
00:07:22,850 --> 00:07:26,210
So grab our original schema from way up here.

116
00:07:27,770 --> 00:07:28,790
Just copy.

117
00:07:30,070 --> 00:07:33,280
How much do we want down to here?

118
00:07:37,220 --> 00:07:39,500
And we still have our foreign key there.

119
00:07:40,160 --> 00:07:45,860
I'm going to indent this a bit and all we do is add on delete cascade.

120
00:07:46,760 --> 00:07:53,540
And all that that does is it says when a customer is deleted, that has a corresponding order to delete

121
00:07:53,540 --> 00:07:54,890
the order as well.

122
00:07:55,880 --> 00:07:57,050
So let's try it.

123
00:07:57,920 --> 00:08:00,320
I'm going to copy this in.

124
00:08:01,070 --> 00:08:03,030
Let's make sure we don't have any tables right now.

125
00:08:03,050 --> 00:08:03,800
Perfect.

126
00:08:04,250 --> 00:08:05,450
So let's copy it.

127
00:08:06,290 --> 00:08:08,720
Then let's copy orders in.

128
00:08:09,710 --> 00:08:15,590
And then let's insert our data and insert our data again.

129
00:08:17,390 --> 00:08:22,190
So as we know, we have data that is joined.

130
00:08:22,490 --> 00:08:24,750
I'm not going to spend time going over the joint part.

131
00:08:24,770 --> 00:08:27,590
Let's just do select star from customers.

132
00:08:29,450 --> 00:08:29,960
Okay.

133
00:08:30,230 --> 00:08:33,740
And then we'll do a select star from orders.

134
00:08:34,159 --> 00:08:36,620
And let's say we want to delete Boy George.

135
00:08:36,799 --> 00:08:40,039
Boy George has two orders, these first two.

136
00:08:40,460 --> 00:08:42,799
So if I delete Boy George.

137
00:08:43,990 --> 00:08:47,440
These two should now disappear versus before a couple of minutes ago.

138
00:08:47,440 --> 00:08:48,210
And we tried it.

139
00:08:48,220 --> 00:08:49,360
We had a problem.

140
00:08:49,600 --> 00:08:50,560
It's not really a problem.

141
00:08:50,560 --> 00:08:57,010
But we we ran into trouble that we put in there ourselves because the foreign key constraint didn't

142
00:08:57,010 --> 00:08:58,180
allow us to delete it.

143
00:08:58,210 --> 00:09:01,570
We weren't allowed to delete a customer unless we deleted the order first.

144
00:09:02,620 --> 00:09:04,240
So we're going to do that now.

145
00:09:04,660 --> 00:09:07,760
Just try deleting based off of ID.

146
00:09:07,780 --> 00:09:09,840
Or we could do it based off of first name.

147
00:09:09,850 --> 00:09:10,780
Let's do email.

148
00:09:12,280 --> 00:09:12,760
Okay.

149
00:09:12,760 --> 00:09:22,900
So we're going to delete from customers where email equals George at gmail.com.

150
00:09:24,110 --> 00:09:25,820
And we don't get that scary error.

151
00:09:26,270 --> 00:09:28,670
Now, if we look at all our customers.

152
00:09:29,880 --> 00:09:31,200
We're missing Boy George.

153
00:09:31,710 --> 00:09:35,520
And if we look at our orders, we're only down to three.

154
00:09:36,330 --> 00:09:41,670
All right, now we're about to move on to our exercises, so you're going to get some practice on your

155
00:09:41,670 --> 00:09:42,150
own.

156
00:09:42,540 --> 00:09:46,380
Hopefully, it's a little we have new data, so hopefully it's a little more interesting rather than

157
00:09:46,380 --> 00:09:48,660
having to work with the same old data over and over.

158
00:09:48,690 --> 00:09:50,850
And then we're going to keep moving on.

159
00:09:51,120 --> 00:09:51,600
All right.