1
00:00:00,090 --> 00:00:03,930
We have one more type of joint to cover, which is the right joint.

2
00:00:04,320 --> 00:00:06,630
So one more beautiful diagram.

3
00:00:06,630 --> 00:00:08,700
We've got our left table and our right table.

4
00:00:08,730 --> 00:00:16,470
When we do a join where we have the left, the right and we do a right join, we will take all the rows

5
00:00:16,470 --> 00:00:24,150
from the right side and then include them, plus any overlap from the left side, meaning that we would

6
00:00:24,150 --> 00:00:26,520
also maybe have null values.

7
00:00:26,850 --> 00:00:32,790
The problem that we have right now, if I do customers on the left and orders on the right as a right

8
00:00:32,790 --> 00:00:38,250
joint, it's not really a problem, but it's a problem from a teaching perspective.

9
00:00:38,910 --> 00:00:40,920
The problem is that we won't see anything different.

10
00:00:40,920 --> 00:00:49,710
So if I write a basic inner join one more time, let's do first name, last name, date.

11
00:00:49,710 --> 00:00:51,450
I think it's order date, isn't it?

12
00:00:51,450 --> 00:00:52,680
And amount.

13
00:00:54,220 --> 00:00:55,270
From.

14
00:00:55,300 --> 00:01:00,460
And we'll go with customers as the left join borders on the right.

15
00:01:00,460 --> 00:01:08,050
And this is an inner join by default on and then customers that ID equals orders DOT customer ID I'm

16
00:01:08,050 --> 00:01:12,370
sure you're sick of that right now or by now we run it.

17
00:01:12,760 --> 00:01:13,810
Here's our inner join.

18
00:01:13,900 --> 00:01:14,590
Great.

19
00:01:14,590 --> 00:01:15,940
No surprises.

20
00:01:15,940 --> 00:01:23,320
We've done this a bunch of times already, but if I tweak this to be a right join, let's see.

21
00:01:24,520 --> 00:01:26,440
Oh, it's the same exact thing.

22
00:01:26,680 --> 00:01:33,130
So the problem, of course, you kind of got into this in the previous video is that every single order

23
00:01:33,130 --> 00:01:38,860
has a customer ID that is by the nature of our schema.

24
00:01:38,860 --> 00:01:44,290
The two tables are set up so that you could have a customer with no orders, but you can't have an order

25
00:01:44,290 --> 00:01:47,770
without customers or a customer, right?

26
00:01:47,770 --> 00:01:49,690
Every single order.

27
00:01:50,830 --> 00:01:59,350
Select Starr from orders has a customer ID, But if I were to try and let's say I had a row in here

28
00:01:59,350 --> 00:02:03,490
that didn't have a customer ID, then things would look different.

29
00:02:03,760 --> 00:02:12,340
So I guess I could do this insert into orders and I'm going to insert an ID that doesn't exist, but

30
00:02:12,340 --> 00:02:14,210
we know that that is not allowed.

31
00:02:14,230 --> 00:02:16,090
It's not going to happen if I try that.

32
00:02:16,930 --> 00:02:19,180
I'll just show you what happens if we do a mount.

33
00:02:19,920 --> 00:02:20,970
Order date.

34
00:02:21,660 --> 00:02:25,290
Customer ID values.

35
00:02:25,590 --> 00:02:27,550
Amount will be $100.

36
00:02:27,570 --> 00:02:33,000
We'll just do the current date and then ID will be customer ID is 99.

37
00:02:33,060 --> 00:02:34,590
We get this error right?

38
00:02:34,620 --> 00:02:36,570
It says foreign key constraint fails.

39
00:02:36,570 --> 00:02:44,580
However, with the way that our schema is set up, if we go back to describe orders, NULL is allowed

40
00:02:44,580 --> 00:02:46,170
for customer ID.

41
00:02:46,320 --> 00:02:48,210
So what if I did that?

42
00:02:48,210 --> 00:02:56,460
What if I said insert a new order that has a value of 100 for amount current date for order date and

43
00:02:56,460 --> 00:02:59,430
let's not do a customer ID.

44
00:03:00,360 --> 00:03:01,260
And what did I.

45
00:03:01,260 --> 00:03:04,430
Oh, I still have this 99 in there that works.

46
00:03:04,440 --> 00:03:12,300
So now if I select star from orders, I have an order in here that does not have a customer ID.

47
00:03:12,330 --> 00:03:18,870
Now, I probably in the real world would set this up so that you were required to have a customer ID

48
00:03:18,900 --> 00:03:21,960
because how can you just have an order with no customer ID?

49
00:03:21,960 --> 00:03:24,300
Who made the order in the real world?

50
00:03:24,360 --> 00:03:25,530
That doesn't really happen.

51
00:03:25,530 --> 00:03:27,210
I mean, I guess you could design it that way.

52
00:03:27,210 --> 00:03:36,570
But this does help us because now if I go back to my right join, which was all the way back here,

53
00:03:36,570 --> 00:03:38,160
here's my right join.

54
00:03:39,290 --> 00:03:47,480
Now what we have is our left side still is customers are right side is orders, and we're taking every

55
00:03:47,480 --> 00:03:51,320
order and including it automatically every single order.

56
00:03:51,350 --> 00:03:59,540
The right side of this diagram and any overlapping information from the left which is going to be customers.

57
00:03:59,540 --> 00:04:03,530
But in the case of this order right here, there is no customer.

58
00:04:03,560 --> 00:04:06,110
So we see no and no.

59
00:04:06,410 --> 00:04:12,200
So simple example, probably not that useful, but it does demonstrate how this works, right?

60
00:04:12,260 --> 00:04:14,150
We have our right side of orders.

61
00:04:14,150 --> 00:04:17,089
We are joining using a right join.

62
00:04:17,089 --> 00:04:22,760
We take every row from orders the right side and then match up anything on the left where we can.

63
00:04:22,760 --> 00:04:25,700
And if we can't match it up, we just get null.

64
00:04:25,880 --> 00:04:28,520
So that's an example of a right join.

