1 00:00:00,120 --> 00:00:06,480 Okay so we hopefully survived the last couple of videos on joins we had cross join the use this one 2 00:00:06,900 --> 00:00:14,130 then we have inner join and then left join now we have the sister or brother of left join which is right 3 00:00:14,130 --> 00:00:14,820 to join. 4 00:00:15,000 --> 00:00:17,640 So it's actually this would be a pretty short video. 5 00:00:17,640 --> 00:00:25,230 It's very similar to left join in that it takes everything from one column along with excuse me it takes 6 00:00:25,230 --> 00:00:31,500 everything with from one table along with the intersection, along with wherever the join condition 7 00:00:31,500 --> 00:00:32,159 is met. 8 00:00:32,700 --> 00:00:35,670 But it just does it from the other table. 9 00:00:35,670 --> 00:00:40,050 So in this case, we've been doing customers and then orders. 10 00:00:40,410 --> 00:00:46,830 So if we did a right join, it will take every record from orders along with any matching records from 11 00:00:47,160 --> 00:00:49,660 Table A, which is our customers. 12 00:00:49,680 --> 00:00:56,070 Now the thing is, and you may have maybe thought about this already, we don't have any orders that 13 00:00:56,070 --> 00:01:00,990 don't have an existing customer, so we're actually not going to see a difference. 14 00:01:00,990 --> 00:01:04,680 But I'm going to come up with a way I'll show you a way for us to see a difference. 15 00:01:04,890 --> 00:01:07,890 But to start, let me just show you the syntax. 16 00:01:08,400 --> 00:01:12,840 So let's hop over to Cloud nine and here's our simplest left join that we wrote. 17 00:01:13,890 --> 00:01:19,150 Remember, it does this here where it includes everything from customers. 18 00:01:19,170 --> 00:01:23,160 David Bowie and Blue still included, even though they don't have any matching records. 19 00:01:23,160 --> 00:01:25,590 And then it also includes all matching records. 20 00:01:26,580 --> 00:01:33,330 But rather than that, I'm now going to go and retype it down here as a right join. 21 00:01:34,950 --> 00:01:41,280 So we'll do a select and we'll just do star from customers. 22 00:01:42,130 --> 00:01:54,550 And then we'll do join orders on customers ID equals orders dot customer ID just like that. 23 00:01:54,580 --> 00:02:00,040 However, if we leave it like this, remember, if we don't explicitly say what type of join, it's 24 00:02:00,040 --> 00:02:01,660 implied to be an inner join. 25 00:02:02,830 --> 00:02:09,100 So we can actually start with that because it will be good to see what that looks like and compare it 26 00:02:09,100 --> 00:02:11,260 to what we get when we do a right to join. 27 00:02:11,260 --> 00:02:16,630 So when we did left, which is right here, we have these nulls and we have let's see, one, two, 28 00:02:16,630 --> 00:02:19,300 three, four, five, six, seven entries. 29 00:02:20,110 --> 00:02:23,950 Versus when we do an inner join, we have five. 30 00:02:25,060 --> 00:02:28,030 And if we make it a right joint, all we have to do is change that. 31 00:02:29,200 --> 00:02:29,860 If I can type. 32 00:02:29,860 --> 00:02:30,250 Right. 33 00:02:30,250 --> 00:02:30,940 There we go. 34 00:02:31,780 --> 00:02:32,830 Let's try this one. 35 00:02:35,070 --> 00:02:36,420 And we have five again. 36 00:02:36,840 --> 00:02:38,670 One, two, three, four, five. 37 00:02:39,120 --> 00:02:43,500 And actually, it's identical to what we got when we did the inner join. 38 00:02:43,710 --> 00:02:48,390 And the reason that's happening, I kind of already went over this briefly, but the reason it's happening 39 00:02:48,390 --> 00:02:53,820 is that we don't have any orders that don't have a match with a customer. 40 00:02:54,180 --> 00:03:01,920 So everything we have, if we go back here, everything we have in the orders table has a matching customer. 41 00:03:01,920 --> 00:03:03,780 So there is something to join it on. 42 00:03:03,990 --> 00:03:10,440 So for every order we actually have five this table and it has four, but we have five in our database. 43 00:03:10,590 --> 00:03:14,970 Then there's only going to be five joints when we are going to be five records that are joined. 44 00:03:15,210 --> 00:03:17,180 When we do a right join. 45 00:03:17,190 --> 00:03:20,490 When we did a left, we had a bunch of well, not a bunch. 46 00:03:20,490 --> 00:03:25,080 We had a couple of users who didn't have any records, who didn't have any orders. 47 00:03:25,080 --> 00:03:30,750 So these customers, like David Bowie and Blue Steel didn't have a match, but we still included them. 48 00:03:31,470 --> 00:03:34,740 So how can we actually see something different? 49 00:03:34,740 --> 00:03:40,890 How can we tweak our data so that a right join actually results in something distinct? 50 00:03:40,920 --> 00:03:46,290 Well, there's a simple thing we can do, which is to screw with our data or excuse me to mess with 51 00:03:46,290 --> 00:03:46,950 our data. 52 00:03:47,040 --> 00:03:48,910 So that's what I'm going to do here. 53 00:03:48,930 --> 00:03:54,570 I don't necessarily recommend that you follow along exactly, because I'm going to be messing up some 54 00:03:54,570 --> 00:03:55,320 of the data. 55 00:03:55,530 --> 00:03:57,390 I'll fix it towards the end of the video. 56 00:03:57,390 --> 00:04:00,550 But I don't want you to have to go through that process if you don't want to. 57 00:04:00,570 --> 00:04:01,760 So it's non-essential. 58 00:04:01,770 --> 00:04:05,100 What is essential is how the join will work, which we'll get to. 59 00:04:05,370 --> 00:04:12,900 So the way that we would have a difference, we'd have a unique result between right and inner is if 60 00:04:12,900 --> 00:04:19,170 there's something that we have on the right table, which for us has been orders in the orders table. 61 00:04:19,170 --> 00:04:24,600 If there's something in the order table that doesn't have a match over on the left that there's no join. 62 00:04:24,600 --> 00:04:27,420 So that would mean that there's no customer. 63 00:04:27,990 --> 00:04:34,230 And right now it doesn't make sense, honestly, to have an order without a customer, but maybe let's 64 00:04:34,230 --> 00:04:42,750 say someone accidentally deleted stuff from our database or maybe there's some a wrong ID, a wrong 65 00:04:42,750 --> 00:04:48,690 customer ID in in our data, we would be able to spot that using a right join. 66 00:04:49,260 --> 00:04:55,620 So what I'm going to do is hop back to cloud nine and before I do anything, I'm just going to rerun 67 00:04:56,340 --> 00:04:58,350 this early inner join we did. 68 00:04:58,860 --> 00:05:02,340 So as you can see, of course, on the left we have customers. 69 00:05:02,340 --> 00:05:06,690 On the right we have orders and we have Boy George who has two. 70 00:05:07,080 --> 00:05:12,680 Let's say I were to delete Boy George and then I ran an inner join. 71 00:05:12,690 --> 00:05:18,450 Well, these would be gone and we would only have three customer orders, right? 72 00:05:19,260 --> 00:05:27,360 But if I did a right join, we would have order, order and null and null over here. 73 00:05:27,360 --> 00:05:34,200 So it takes all orders regardless as to whether they have a matching customer or not, and they'll fill 74 00:05:34,200 --> 00:05:38,460 in null if there isn't a customer and then if there is, the appropriate information will be filled 75 00:05:38,460 --> 00:05:42,690 in just like it was with left except the opposite direction. 76 00:05:42,690 --> 00:05:43,950 So let's try that. 77 00:05:43,950 --> 00:05:47,010 Let's try deleting a user or a customer. 78 00:05:47,130 --> 00:05:53,550 Let's delete Boy George It's been a while, but that's going to be delete from customers. 79 00:05:53,550 --> 00:05:58,200 You keep calling it users where and we could do it based off of ID. 80 00:05:58,230 --> 00:06:00,390 Let's do it based off of first name. 81 00:06:01,200 --> 00:06:02,790 First name equals boy. 82 00:06:03,270 --> 00:06:04,320 Just like that. 83 00:06:04,680 --> 00:06:06,150 Now, what do you think will happen? 84 00:06:07,320 --> 00:06:08,850 Oh, no, an error. 85 00:06:08,850 --> 00:06:15,300 And he may have anticipated it being a syntax error based off of my typing proficiency, but it's not. 86 00:06:15,660 --> 00:06:17,400 It's a deeper error. 87 00:06:17,400 --> 00:06:20,790 It's an error that has to do with that foreign key constraint we set up. 88 00:06:20,940 --> 00:06:27,090 Because if we try and delete a customer that in order is referencing, we have two orders referencing 89 00:06:27,090 --> 00:06:27,450 it. 90 00:06:27,690 --> 00:06:31,740 That's a problem according to our foreign key constraint that we set up up here. 91 00:06:32,130 --> 00:06:39,300 Remember that this doesn't let us create an order with a faulty or nonexistent customer ID, so it also 92 00:06:39,300 --> 00:06:44,310 won't let us delete a customer up here if it's dependent on down here. 93 00:06:44,640 --> 00:06:50,520 And I kind of did this on purpose here to show you a solution to this, not to let us delete things 94 00:06:50,520 --> 00:06:56,790 willy nilly, but how could we if we deleted a customer, how could we automatically have it delete 95 00:06:56,790 --> 00:07:00,060 all orders associated with it, which is a pretty common thing. 96 00:07:00,060 --> 00:07:06,870 If you have data that's dependent on another piece of data, let's say we have books and reviews. 97 00:07:07,200 --> 00:07:12,960 Well, if that book is deleted, for some reason we don't want all these reviews that are just pointing 98 00:07:12,960 --> 00:07:17,070 to an empty or gone book, so we would want to link them. 99 00:07:17,070 --> 00:07:21,420 So if I delete the book, all associated reviews get deleted and I'll show you how to do that. 100 00:07:21,930 --> 00:07:24,930 But the first thing I'm going to do is delete these two tables. 101 00:07:24,930 --> 00:07:25,920 I'm going to remake them. 102 00:07:25,920 --> 00:07:29,190 And this is the part I was saying you do not need to follow along. 103 00:07:29,370 --> 00:07:30,690 So let's do that now. 104 00:07:30,960 --> 00:07:34,890 Drop table and we have another little surprise in store for us. 105 00:07:34,890 --> 00:07:39,930 If we try and drop table and let's just do customers, what do you think will happen? 106 00:07:40,830 --> 00:07:41,880 Same issue. 107 00:07:41,910 --> 00:07:45,840 We can't delete customers because orders depends on it. 108 00:07:46,290 --> 00:07:53,820 So what we can do is delete orders first and then customers, or we can do it in one line like this 109 00:07:53,820 --> 00:07:55,350 orders common customers. 110 00:07:55,530 --> 00:08:01,830 Well, if I put an SE there and now of course I messed it up because orders is already gone. 111 00:08:02,190 --> 00:08:05,550 So if I had type that correctly, it would have worked. 112 00:08:05,550 --> 00:08:07,410 But we'll just delete customers separately. 113 00:08:07,590 --> 00:08:10,620 Now, if I do show tables, there's nothing there. 114 00:08:11,070 --> 00:08:15,480 So the reason I did that is I want to remake our two tables very simply. 115 00:08:15,480 --> 00:08:16,890 So I'm going to copy this. 116 00:08:18,060 --> 00:08:24,600 And this is why I was saying you do not need to do this yourself necessarily, and I'm going to change 117 00:08:24,600 --> 00:08:25,740 one line. 118 00:08:26,040 --> 00:08:29,910 I'm going to get rid of the foreign key constraint just like that. 119 00:08:29,910 --> 00:08:32,100 Everything else should work exactly the same. 120 00:08:32,100 --> 00:08:39,600 So I'm going to remake the customers table, remake the orders table, I'm going to insert our customers 121 00:08:40,470 --> 00:08:44,250 and I'm going to insert our orders just like this. 122 00:08:46,500 --> 00:08:52,230 So if we do a select star from customers, let's say we have the same data, same thing from orders, 123 00:08:53,250 --> 00:08:59,190 but now what I'm going to do that I couldn't do before is I'm going to insert a new order. 124 00:08:59,430 --> 00:09:11,340 So insert into orders and I'm going to insert what we have order, date, amount and customer ID and 125 00:09:11,340 --> 00:09:12,450 I'll do two of them. 126 00:09:12,990 --> 00:09:25,020 And so the first one will be something ordered, let's say 2017 slash 11, slash oh five and the amount 127 00:09:25,020 --> 00:09:27,990 was $23.45. 128 00:09:28,680 --> 00:09:32,730 And the customer ID, this is the important part that I'm trying to highlight here. 129 00:09:32,820 --> 00:09:35,880 I'm going to put something in that doesn't exist in our table. 130 00:09:36,330 --> 00:09:41,730 So let's say 45 and I'll do one more. 131 00:09:44,970 --> 00:09:47,580 I'll do rather than this. 132 00:09:47,850 --> 00:09:49,320 Let's get a little practice. 133 00:09:49,320 --> 00:09:54,210 Remember, using cur date gives us the current date. 134 00:09:54,480 --> 00:09:55,440 Let's do that. 135 00:09:57,230 --> 00:10:00,020 So we'll do a date here, comma. 136 00:10:00,020 --> 00:10:11,540 And this purchase was 477 seven 777.77 and that was made by someone with ID 109, which also doesn't 137 00:10:11,540 --> 00:10:12,200 exist. 138 00:10:12,680 --> 00:10:13,400 Perfect. 139 00:10:13,670 --> 00:10:15,230 So now let's insert those. 140 00:10:18,250 --> 00:10:19,700 Didn't seem to have any problems. 141 00:10:19,720 --> 00:10:20,920 Let's look at orders. 142 00:10:23,020 --> 00:10:23,520 Yeah. 143 00:10:23,530 --> 00:10:24,140 It's here. 144 00:10:24,160 --> 00:10:24,460 Right. 145 00:10:24,460 --> 00:10:27,030 We have customer ID of 45 and 109. 146 00:10:27,040 --> 00:10:32,290 And the reason this works is we got rid of that foreign key constraint, which I'm not saying you should 147 00:10:32,290 --> 00:10:33,190 do, by the way. 148 00:10:33,190 --> 00:10:36,790 I'm doing this to illustrate and make a difference in our data.