WEBVTT 00:00.060 --> 00:00.460 OK. 00:00.560 --> 00:04.050 So we hopefully survived the last couple of videos unjoined. 00:04.200 --> 00:09.720 We had CROSS JOIN use this one then we have INNER JOIN AND THEN LEFT JOIN. 00:09.720 --> 00:14.930 Now we have the sister brother of LEFT JOIN which is RIGHT JOIN. 00:15.150 --> 00:22.320 So it's actually this should be a pretty short video it's very similar to left join in that it takes 00:22.380 --> 00:28.230 everything from one column along with this excuse me it takes everything from one table along with the 00:28.230 --> 00:32.380 intersection along with wherever the join condition is met. 00:32.890 --> 00:35.660 But it just does it from the other table. 00:35.850 --> 00:40.220 So in this case we've been doing customers and then orders. 00:40.590 --> 00:46.770 So if we did a right join it will take every record from orders along with any matching records from 00:46.780 --> 00:49.640 Card Table A which is our customers. 00:49.890 --> 00:53.720 Now the thing is and you may have maybe felt about this already. 00:53.910 --> 00:58.330 We don't have any orders that don't have an existing customer. 00:58.650 --> 01:03.150 So we're actually not going to see a difference but I'm going to come up with a way I'll show you a 01:03.150 --> 01:04.620 way for us to see a difference. 01:04.950 --> 01:07.810 But to start let me just show you the syntax. 01:07.940 --> 01:08.400 OK. 01:08.550 --> 01:16.950 So let's hop over to cloud 9 and here's our simplest left join that we wrote member does this here where 01:16.950 --> 01:21.070 it includes everything from customers David Bowie and blue steel included. 01:21.150 --> 01:26.340 Even though they don't have any matching records and then it also includes all matching records. 01:26.730 --> 01:33.990 But rather than that I'm now going to go and retype it down here as a RIGHT JOIN. 01:35.040 --> 01:49.560 So we'll do a select Well just to star from customers and then we'll do join orders on customers ID 01:49.680 --> 01:54.780 equals orders dot customer id just like that. 01:54.780 --> 01:57.030 However if we leave it like this. 01:57.030 --> 02:03.690 Remember if we don't explicitly say what type of join it's implied to be an inner join so we can actually 02:03.690 --> 02:09.720 start with that because it will be good to see what that looks like and compare it to what we get when 02:09.720 --> 02:11.240 we do a RIGHT JOIN. 02:11.400 --> 02:19.300 So when we did left which is right here we have these NOLs and we have 1 2 3 4 5 6 7 entries. 02:20.110 --> 02:24.340 First when we do an inner join We have five. 02:25.180 --> 02:30.380 And if we make it right Joe I know we have to do is change that if I can type right. 02:30.430 --> 02:35.030 You go let's try this one. 02:35.180 --> 02:36.830 And we have five again. 02:36.890 --> 02:39.030 One two three four five. 02:39.230 --> 02:43.510 And actually it's identical to what we got when we did the energy. 02:43.910 --> 02:48.200 And the reason that's happening I kind of already went over this briefly but the reason it's happening 02:48.500 --> 02:54.310 is that we don't have any orders that don't have a match with that customer. 02:54.350 --> 03:01.870 So everything we have if we go back here everything we have in the Orders table has a matching customer 03:01.880 --> 03:03.840 so there is something to join it on. 03:04.160 --> 03:10.850 So for every order we actually have five festival and it has four but we have five in our database and 03:10.880 --> 03:15.560 there's only going to be five joints when you're just going to be five records that are joined when 03:15.560 --> 03:17.190 we do a right join. 03:17.310 --> 03:20.450 When we did a left we had a bunch of them not a bunch. 03:20.450 --> 03:24.040 We had a couple of users who didn't have any records. 03:24.110 --> 03:29.870 Didn't have any orders so customers like David Bowie and blue steel didn't have a match but we still 03:29.870 --> 03:31.040 included them. 03:31.610 --> 03:34.730 So how can we actually see something different. 03:34.730 --> 03:40.890 How can we tweak our data so that a right join actually results in something distinct. 03:41.000 --> 03:46.370 Well there's a simple thing we can do which is to screw with our data or excuse me to mess with our 03:46.370 --> 03:47.220 data. 03:47.240 --> 03:48.910 So that's what I'm going to do here. 03:49.040 --> 03:51.290 I don't necessarily recommend that you follow along. 03:51.290 --> 03:52.150 Exactly. 03:52.280 --> 03:55.470 Because I'm going to be messing up some of the data. 03:55.590 --> 03:59.930 I'll fix it towards the end of the video but I don't want you to have to go through that process if 03:59.930 --> 04:00.600 you don't want to. 04:00.710 --> 04:01.840 So it's non-essential. 04:01.940 --> 04:05.180 What is essential is how the join will work which we'll get to. 04:05.510 --> 04:12.430 So the way that we would have a difference with We'd have a unique result between right and inare is 04:12.470 --> 04:19.240 if there's something that we have on the right table which for us has been orders in the Orders table. 04:19.310 --> 04:24.580 If there's something in the Orders table that doesn't have a match over on the left there's no join. 04:24.770 --> 04:27.860 So that would mean that there's no customer. 04:28.190 --> 04:32.540 And right now it doesn't make sense honestly to have an order without a customer. 04:32.930 --> 04:41.540 But maybe let's say someone accidentally deleted stuff from our database or maybe there's some a wrong 04:41.600 --> 04:48.900 I.D. wrong customer id in our data we would be able to spot that using a right join. 04:49.400 --> 04:55.370 So what what I'm going to do is hop back into cloud 9 and before I do anything I'm just going to rerun 04:56.420 --> 04:58.780 this early inner join we did. 04:58.940 --> 05:05.120 So as we can see of course on the left we have customers on the right we have orders and we have Boy 05:05.120 --> 05:06.780 George who has to. 05:07.220 --> 05:12.660 Let's say I were to delete Boy George and then I ran an inner join. 05:12.890 --> 05:19.290 Well these would be gone and we would only have three customer orders right. 05:19.370 --> 05:27.350 But if I did a right join we would have order order and no anole over here. 05:27.350 --> 05:33.440 So it takes all orders regardless as to whether they have a matching customer or not. 05:33.440 --> 05:34.710 And no fill in all. 05:34.730 --> 05:39.380 If there isn't a customer and then if there is the appropriate information will be filled in just like 05:39.380 --> 05:40.420 it was with left. 05:40.580 --> 05:42.670 Except you know the opposite direction. 05:42.770 --> 05:44.000 So let's try that. 05:44.120 --> 05:47.300 Let's try it a leading user or a customer. 05:47.300 --> 05:48.570 Let's delete Boy George. 05:48.770 --> 05:49.700 It's been a while. 05:49.870 --> 05:57.510 That's going to be deleted from customers who keep calling it users where we could do it based off of 05:57.510 --> 06:02.920 ready let's do it based off of the first name first name equals boy. 06:03.410 --> 06:04.780 Just like that. 06:04.790 --> 06:06.640 Now what do you think will happen. 06:07.340 --> 06:07.950 Oh no. 06:07.970 --> 06:08.810 An error. 06:09.050 --> 06:15.670 And you may have anticipated it being a syntax error based off of my typing proficiency but it's not. 06:15.830 --> 06:20.080 It's a deeper error it's an error that has to do with that foreign key constraint. 06:20.090 --> 06:27.050 We set up because if we tried to lead a customer that an order is referencing we have two orders referencing 06:27.050 --> 06:27.690 it. 06:27.860 --> 06:32.190 That's a problem according to our foreign key constraint that we set up a peer. 06:32.210 --> 06:38.240 Remember that this doesn't let us create an order with a faulty or nonexistent customer ID. 06:38.420 --> 06:42.060 So it also won't let us delete a customer up here. 06:42.440 --> 06:48.560 If it's depended on down here and I kind of did this on purpose here to show you a solution to this 06:48.950 --> 06:51.220 not to let us delete things willy nilly. 06:51.380 --> 06:58.190 But how could we if we deleted a customer how could we automatically have it delete all orders associated 06:58.190 --> 07:00.040 with it which is a pretty common thing. 07:00.050 --> 07:03.370 If you have data that's dependent on another piece of data. 07:03.560 --> 07:06.990 Let's say we have books and reviews. 07:07.370 --> 07:12.920 Well if that book is deleted for some reason we don't want all these reviews that are just pointing 07:12.920 --> 07:15.180 to an empty or gone book. 07:15.470 --> 07:17.030 So we would want to link them. 07:17.030 --> 07:21.990 So if I delete the book all associated reviews get deleted and I'll show you how to do that. 07:22.040 --> 07:24.920 But the first thing I'm going to do is delete these two tables. 07:24.920 --> 07:29.250 I'm going to remake them and this is the part I was saying you do not need to follow along. 07:29.540 --> 07:31.040 So let's do that now. 07:31.070 --> 07:37.320 Drop table and we have another little surprise is in store for us if we try and draw table and let's 07:37.320 --> 07:40.860 just do customers what do you think will happen. 07:40.890 --> 07:42.030 Same issue. 07:42.030 --> 07:46.380 We can't delete customers because orders depends on it. 07:46.410 --> 07:51.090 So what we can do is do the orders first and then customers. 07:51.360 --> 07:53.790 Or we can do it in one line like this. 07:53.790 --> 07:55.610 Orders come in customers. 07:55.750 --> 08:02.260 Well if I put an X there and now of course they missed it enough because orders is already gone. 08:02.340 --> 08:05.570 So if I had typed that correctly but it worked. 08:05.590 --> 08:10.600 But just to the customer separately now if I do show tables there's nothing there. 08:10.830 --> 08:11.110 OK. 08:11.130 --> 08:15.450 The reason I did that is I want to remake our two tables very simply. 08:15.480 --> 08:23.190 So I'm going to copy this and this is why I was saying you do not need to do this yourself necessarily. 08:23.640 --> 08:25.880 And I'm going to change one line. 08:26.130 --> 08:31.650 I'm going to get rid of the foreign key constraint just like that everything else should work exactly 08:31.650 --> 08:32.100 the same. 08:32.100 --> 08:37.200 So I'm going to make the customers table make the orders table. 08:37.200 --> 08:44.760 I'm going to insert our customers and I'm going to insert our orders just like this. 08:45.490 --> 08:46.210 OK. 08:46.650 --> 08:50.820 So if we do a select star from customers let's say we have the same data. 08:50.940 --> 08:52.830 Same thing from orders. 08:53.340 --> 09:00.090 But now what I'm going to do that I couldn't do before is I'm going to insert a new order to insert 09:00.120 --> 09:11.850 into orders and I'm going to insert what we have ordered date amounts and customer ID and I'll do two 09:11.850 --> 09:13.050 of them. 09:13.050 --> 09:25.710 And so the first one will be something ordered that say 2017 slash Lebon slash 0 5 and the amount was 09:26.060 --> 09:26.870 $23. 09:26.870 --> 09:32.910 Forty five cents and the customer id This is the important part that I'm trying to highlight here. 09:32.970 --> 09:36.150 I'm going to put something in that doesn't exist in our table. 09:36.510 --> 09:41.510 So let's say forty five and I'll do one more 09:45.020 --> 09:47.560 I'll do rather than this. 09:47.900 --> 09:49.370 Let's get a little practice. 09:49.400 --> 09:54.300 Remember using curred date gives us the current day. 09:54.590 --> 09:56.880 Let's do that. 09:57.300 --> 10:05.240 So it would do curred date here comma and this purchase was four seventy seven seven seven hundred seventy 10:05.240 --> 10:07.210 seven point seventy seven. 10:07.220 --> 10:12.610 And that was made by someone with ID 1 0 9 which also doesn't exist. 10:12.740 --> 10:13.700 Perfect. 10:13.720 --> 10:14.260 So knowledge. 10:14.270 --> 10:15.010 Insert those 10:18.240 --> 10:19.610 didn't seem to have any problems. 10:19.800 --> 10:21.420 Let's look at orders. 10:23.080 --> 10:24.450 Yeah it's here right. 10:24.460 --> 10:27.170 We have customer id a 45 in one or nine. 10:27.190 --> 10:32.230 And the reason this works is we got rid of that foreign key constraint which I'm not saying you should 10:32.230 --> 10:32.500 do. 10:32.500 --> 10:36.690 By the way I'm doing this to illustrate and make a difference in our data.