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.