WEBVTT 00:00.060 --> 00:04.980 So let's suppose we're inheriting someone else's database and they decided not to use foreign. 00:05.010 --> 00:08.040 They didn't know about foreign key constraints so they didn't use it. 00:08.190 --> 00:09.690 And then they had users. 00:09.690 --> 00:14.100 They used to have all the way up until a hundred and nine users are customers. 00:14.220 --> 00:19.110 And then a lot of people left they just deleted their accounts that say something bad happened there 00:19.110 --> 00:20.640 was a hack a data breach. 00:20.640 --> 00:27.590 Everyone got scared away and then all we are left with are these customers. 00:27.680 --> 00:28.650 It's pretty sad. 00:28.880 --> 00:36.050 So I mean it's a great bunch of customers to have honestly but still only five compared to 109. 00:36.050 --> 00:38.640 Keep in mind this is all done made up story. 00:38.780 --> 00:46.970 But what we could do is use our right join to basically identify all of our customer and orders and 00:46.970 --> 00:52.680 their associated orders as well as any orders that don't have an associated customer. 00:52.850 --> 00:54.570 So we could fill in No. 00:54.950 --> 01:01.100 So if we do our joint that we had done earlier just you know scroll up to save times or save time if 01:01.100 --> 01:06.070 we do this join here which is an inner join or that actually a left join. 01:06.260 --> 01:09.780 Let's do this one right here. 01:13.680 --> 01:14.610 Nothing changes right. 01:14.610 --> 01:18.270 We still have Boy George and his associated two orders. 01:18.270 --> 01:20.130 George Michael has two orders. 01:20.160 --> 01:28.410 Patty Davis her one order and then if I did a left join which we could just do by simply changing that 01:28.440 --> 01:32.710 one inner to the left or we can just do this here. 01:33.580 --> 01:39.630 And I'm I'm only choosing this one because rather than Star this has a smaller subset of data that's 01:39.690 --> 01:41.390 easier to look at. 01:42.180 --> 01:49.170 OK so now we end up with this which still is unchanged from when we did it in the last video because 01:49.380 --> 01:53.940 there are orders that have no customers but they're not here because we did a left join. 01:54.150 --> 02:00.060 And the left join takes everything from customers and any associated matching data from orders. 02:00.120 --> 02:07.010 But now if we do the same thing down here you can hear my cat me. 02:07.900 --> 02:10.560 And we just change it to be right join. 02:11.290 --> 02:16.790 So we're still just doing first name last name order date and amount from customers on the left or from 02:17.140 --> 02:23.050 on the left as long term from customers first right joined with orders so that we take everything from 02:23.050 --> 02:30.380 orders like this everything from orders and any overlap from customers. 02:30.540 --> 02:36.610 Let's try it and you can see exactly what we expected happened. 02:36.800 --> 02:43.430 So we still have all of her orders that match customers and orders but then we have those two orphans 02:43.430 --> 02:44.460 that we added in. 02:44.690 --> 02:46.940 Where there is no matching customer. 02:46.940 --> 02:51.770 So we end up with no first name no last name no first name no last name. 02:52.190 --> 02:55.000 So that's basically all we want to show you with. 02:55.010 --> 02:55.250 Right. 02:55.250 --> 03:02.210 Joines But like I said we will have actually relevant useful examples later but the last thing I wanted 03:02.210 --> 03:07.580 to do was hit you with a whole bunch of set up brand new data just so that you could see how a right 03:07.580 --> 03:08.350 joining works. 03:08.540 --> 03:11.420 So there is you know this is a hypothetical situation that could happen. 03:11.420 --> 03:13.860 We could have orphan data like this. 03:14.060 --> 03:15.670 So I'm going to show you two things now. 03:15.770 --> 03:21.500 The first one is that we can still just do any you know anything that we want with this data like we 03:21.500 --> 03:24.340 did earlier we can do more complex selections. 03:24.500 --> 03:26.380 And I'm also going to show you a new constraint. 03:26.390 --> 03:32.210 You can add to your schema so that when something is deleted that has a foreign key depending on it 03:32.390 --> 03:35.180 it will automatically delete the dependent record. 03:35.360 --> 03:39.950 But to start I just wanted to show we can still do things like group by. 03:40.490 --> 03:44.500 Or we can do things like order by so we can add that in here. 03:44.670 --> 03:45.870 You order by. 03:46.010 --> 03:49.760 And let's do first name just like that 03:53.140 --> 03:54.260 OK. 03:55.010 --> 03:59.870 What about if we tried to group by but if we did group by. 04:00.500 --> 04:05.040 And what if we tried to group by customer ID. 04:05.090 --> 04:06.020 What happens then. 04:10.110 --> 04:14.470 So it doesn't look like all that much right but we can still do our same. 04:14.490 --> 04:16.240 You know we have Boy George was condensed. 04:16.260 --> 04:26.220 George Michael condensed but we can do a sum for amounts just like we've been doing and we can see how 04:26.220 --> 04:32.090 much everyone has spent or George 135 George Michael 8:13 Bette Davis for 50. 04:32.160 --> 04:34.380 We still have these two Knolles. 04:34.740 --> 04:41.010 So this is where we can start to make decisions and do things like OK if first name is no then let's 04:41.010 --> 04:42.490 replace it with something. 04:43.080 --> 04:50.430 So I'm going to format this a bit as we start to get more and more and more replace first name and last 04:50.430 --> 04:56.250 name with if no first name. 04:56.260 --> 04:57.250 Remember how this works. 04:57.270 --> 04:58.550 First name is no. 04:58.710 --> 05:01.140 Then it will be replaced with whatever we pass in. 05:01.200 --> 05:05.050 So we'll say the same missing. 05:06.410 --> 05:14.790 And then we can do the same thing for last name if no last name comma will say use her. 05:14.790 --> 05:18.610 So we'll end up with missing user if we do it that way. 05:21.530 --> 05:25.990 If we don't have an error extra comma here that would do it. 05:28.360 --> 05:37.780 And let's use aliases as the first as last all write I like going on. 05:38.050 --> 05:46.300 OK so now we have missing user and then say we want to see what the total of our orders are where we 05:46.300 --> 05:47.590 don't have a user. 05:47.590 --> 05:50.800 So basically the orphans we want to group those together as well. 05:50.800 --> 05:53.010 Right now we're grouping right customer ID. 05:53.320 --> 06:01.540 But what if I wanted to group these to what we could do is change customer ID to be a first name and 06:01.540 --> 06:06.190 we'd want to do Khama last name rather than just first name because we may have multiple people with 06:06.190 --> 06:07.560 the same first name. 06:07.630 --> 06:09.100 So if we do this now 06:13.000 --> 06:21.490 you'll see that we end up with missing user has 801 total spent then Bette Davis 450 and so on. 06:21.490 --> 06:25.990 And we can order it I think right now you understand what I'm trying to show that these tables aren't 06:25.990 --> 06:27.160 special. 06:27.220 --> 06:31.720 You just have to be comfortable working with NO and if no it's kind of your best friend when you're 06:31.720 --> 06:32.590 doing that. 06:32.990 --> 06:33.470 OK. 06:33.610 --> 06:37.230 So then the very last thing I wanted to show I keep doing that. 06:37.340 --> 06:37.970 Go away. 06:38.110 --> 06:46.130 The last thing I wanted to show was how we do what's known as on the beat cascade. 06:46.480 --> 06:52.330 So this is what I was talking about when we we if we have a foreign key relationship when we delete 06:52.360 --> 06:53.040 a parent. 06:53.050 --> 06:58.510 So if we delete Customers table not the whole table but let's say we delete Bette Davis. 06:58.900 --> 07:01.180 We want her order to also be deleted. 07:01.180 --> 07:02.790 So it's not just totally on its own. 07:02.800 --> 07:05.100 We don't end up with this situation. 07:05.140 --> 07:06.200 So how do we do that. 07:06.310 --> 07:10.470 Well I'll show you right now if you're not interested then go ahead and skip this. 07:10.470 --> 07:14.120 This isn't about Joines but I figured it's a good place to put it. 07:14.140 --> 07:20.610 I'm going to actually drop our tables again. 07:21.370 --> 07:22.760 I'm going to redo. 07:22.930 --> 07:26.330 So grab our original schema from way up here. 07:27.720 --> 07:29.720 Just copy. 07:29.890 --> 07:33.440 How much do we want down to here. 07:37.310 --> 07:45.650 And we still have our foreign key there and when and done to submit and all we do is add on delete cascade 07:46.830 --> 07:54.120 and all that does is it says when a customer is deleted that has a corresponding order to read the order 07:54.240 --> 07:54.910 as well. 07:56.000 --> 07:57.740 So let's try it. 07:57.980 --> 08:00.530 I'm going to copy this in. 08:01.050 --> 08:03.110 Let's make sure we don't have any tables right now. 08:03.140 --> 08:04.270 Perfect. 08:04.400 --> 08:15.990 So let's copy it then let's copy orders in and then let's insert our data and insert our data again. 08:16.650 --> 08:17.180 OK. 08:17.510 --> 08:25.070 So as we know we have data that is joined I'm not going to spend time going over the joined part let's 08:25.070 --> 08:27.610 just use select start from customers. 08:29.410 --> 08:29.960 Okay. 08:30.380 --> 08:36.860 And then we'll do a select star from orders and let's say we want to delete Boy George. 08:36.860 --> 08:40.170 Boy George has two orders these first two. 08:40.580 --> 08:45.840 So if I had to lead boy george these two should now disappear first. 08:45.890 --> 08:48.310 And before a couple of minutes go on we tried it. 08:48.400 --> 08:49.660 We had a problem. 08:49.690 --> 08:55.660 It's not really a problem but we we ran into trouble that we put in there ourselves because a foreign 08:55.660 --> 08:58.220 key constraint didn't allow us to delete it. 08:58.330 --> 09:02.560 We weren't allowed to delete a customer unless we deleted the order first. 09:02.740 --> 09:04.290 So we're going to do that now. 09:04.600 --> 09:11.410 Just try deleting based off of ID or we could do it based off of first name let's do e-mail. 09:12.240 --> 09:12.700 OK. 09:12.850 --> 09:24.680 So we're going to delete from customers where e-mail equals George at gmail dot com and we don't get 09:24.680 --> 09:26.340 that scary error. 09:26.390 --> 09:34.860 Now if we look at our customers we're missing boy George and if we look at our orders we're only down 09:34.860 --> 09:36.350 to three. 09:36.340 --> 09:39.810 All right now we're about to move on to our exercises. 09:39.990 --> 09:42.220 So you're going to get some practice on your own. 09:42.540 --> 09:43.620 Hopefully that's a little. 09:43.650 --> 09:47.400 We have new data so hopefully it's a little more interesting rather than having to work with the same 09:47.400 --> 09:48.580 old data over and over. 09:48.900 --> 09:50.900 And then we're going to keep moving on. 09:51.180 --> 09:51.540 All right.