WEBVTT 00:00.090 --> 00:02.380 OK I hope you're still with us. 00:02.430 --> 00:05.020 I really hope you sleep at the last video. 00:05.280 --> 00:08.680 These are getting a little too long for my taste. 00:08.970 --> 00:13.420 Significantly too long so I apologize for that but there's a lot to cover here. 00:13.620 --> 00:16.030 And there's no great place to break it up. 00:16.080 --> 00:19.910 However we are done with kind of the long slog of explaining joints. 00:19.920 --> 00:25.280 Now I'm just going to show you another type so INNER JOIN is what most people end up doing. 00:25.290 --> 00:28.620 That's a big generalization but it's what I end up doing most of the time. 00:28.620 --> 00:30.210 But there are other types. 00:30.540 --> 00:33.500 And the next one that I'll show you is called the LEFT JOIN. 00:33.860 --> 00:35.200 There's the left and right. 00:35.280 --> 00:39.150 They worked very similarly So once you know understand how one works it makes it easier to understand 00:39.150 --> 00:39.850 the other. 00:40.110 --> 00:46.170 So what we saw so far was the inner join where we take one table and another table B and then we select 00:46.230 --> 00:50.850 all the records from A and B where the join condition is met. 00:51.120 --> 00:52.530 But there's another type of choice. 00:52.800 --> 00:55.530 Before we get there I want to show one other thing. 00:56.070 --> 01:03.020 If we go back to any of our joints that we had let's take this one here. 01:03.270 --> 01:08.260 So this gives us a nice little table with Boy George and his orders. 01:08.280 --> 01:09.890 We have George Michael in his orders. 01:09.900 --> 01:16.470 And Patty Davis in her orders I can do just any regular ask you well functions and things that you're 01:16.470 --> 01:18.150 used to with this data. 01:18.150 --> 01:20.580 So I didn't make that very clear in the last video. 01:20.580 --> 01:22.330 This isn't some special table. 01:22.440 --> 01:23.490 We can do whatever we want. 01:23.490 --> 01:26.840 So I could do something like Group By. 01:27.120 --> 01:28.860 Or let's start even simpler. 01:29.550 --> 01:32.920 I could do an order by let's do an order by. 01:33.080 --> 01:35.260 And let me actually move this. 01:35.300 --> 01:37.200 So we have a record of everything we've done. 01:37.380 --> 01:39.310 So I'll scroll down. 01:39.570 --> 01:41.200 Getting fancier. 01:41.700 --> 01:42.300 Here we go. 01:42.540 --> 01:45.000 So we can do an order by 01:48.750 --> 01:52.680 amount if you want it to. 01:52.810 --> 02:00.520 And now we have from the lowest order up to the highest order or we could order a pie order date. 02:02.020 --> 02:03.730 So that's one thing we can do. 02:04.510 --> 02:07.800 But we can also do things like group buy. 02:08.260 --> 02:15.610 So maybe let's say this is something we actually might want to do is take all of our orders and group 02:15.610 --> 02:17.380 them by who ordered them. 02:17.820 --> 02:18.460 OK. 02:18.640 --> 02:25.120 Groups and by who ordered them and then take that and find the average amount they spent or the total 02:25.120 --> 02:25.440 amount. 02:25.470 --> 02:32.050 But we start there let's identify the biggest spender in our entire database which is pretty easy to 02:32.050 --> 02:34.050 do if you just look here and add things up. 02:34.060 --> 02:36.570 But let's say we had you know ten thousand records. 02:36.610 --> 02:39.610 How do we find the biggest spender. 02:39.610 --> 02:41.400 So Will group buy. 02:42.310 --> 02:44.090 And how do we group them. 02:44.290 --> 02:50.700 Well we want to group by our customer right place the order first name could work. 02:50.830 --> 02:56.410 But that assumes that we only have one beti or one George and we might have George Michael and George 02:56.410 --> 02:57.500 Foreman or something. 02:57.550 --> 02:58.990 So that's not going to work. 02:59.050 --> 03:03.490 We could do first name Khama last name but we still could have people with the same name. 03:03.490 --> 03:09.640 So it's better to just group buy something we know is unique like customer id just like that. 03:09.640 --> 03:18.030 And let's be explicit orders dot customer id so gripped by that and if we hit enter. 03:18.350 --> 03:23.320 I remember when we do group high and we don't actually do any math or any functions on it it looks like 03:23.380 --> 03:29.350 it just cut data out because that's just how it prints things to us but now you can see that we have 03:29.350 --> 03:32.060 grouped by the customer ID. 03:32.560 --> 03:38.770 So you know it's hard to see because we're not displaying customer ID but remember that customer ID 03:38.770 --> 03:42.470 is unique relative to every user. 03:42.490 --> 03:44.850 So we're basically grouping by customer. 03:45.190 --> 03:46.810 Hopefully that makes sense. 03:47.140 --> 03:51.800 You could make it a bit clearer if you did something like this. 03:51.860 --> 03:52.550 What we've done. 03:52.560 --> 04:00.140 And let me get rid of the group by briefly what we've done is take these customer ideas here that we 04:00.140 --> 04:06.590 weren't showing and just grouping these two together these two together and this one on its own. 04:08.050 --> 04:09.610 We're not actually displaying that. 04:09.790 --> 04:10.100 OK. 04:10.140 --> 04:17.550 So we can group by that like we did and then we want to do is totally together the amount. 04:17.580 --> 04:29.250 So rather than just amount to do some amount and I'm going to clean this up a little bit and just give 04:29.250 --> 04:32.530 it an alias as total spent. 04:33.090 --> 04:35.530 Just like that. 04:35.540 --> 04:46.190 Now let's try it cool so we can see boy George spent 135 George Michael 813 and Bette Davis 450. 04:46.190 --> 04:52.090 And then lastly if we could order by total spent. 04:52.250 --> 04:55.980 Move our semi-colon. 04:56.250 --> 05:00.020 Now you can see and actually let's do descending. 05:03.320 --> 05:07.890 Now you can see OK George Michael is definitely our biggest spender. 05:07.910 --> 05:11.450 Thirteen point seventeen cents perfect. 05:11.450 --> 05:13.860 Now order date doesn't really make sense to have in there. 05:13.870 --> 05:19.630 So because we have multiple orders and it doesn't make sense just to show one. 05:19.670 --> 05:21.290 So maybe this makes more sense. 05:21.290 --> 05:22.070 There we go. 05:22.390 --> 05:22.600 OK. 05:22.610 --> 05:28.190 So I just want to show that you can still do typical things that we're used to with the results of a 05:28.190 --> 05:29.230 JOIN query. 05:29.240 --> 05:32.840 It's not some special alien type of a table. 05:32.900 --> 05:35.380 Now that brings us on to the left join. 05:35.540 --> 05:37.630 So another way of joining our data. 05:38.390 --> 05:41.080 Here's the ugly diagram I tried to make a little better. 05:41.080 --> 05:43.040 The colors are still awful I'm sorry. 05:43.040 --> 05:44.280 So what does that do. 05:44.750 --> 05:48.260 Well it's going to take everything from the first table. 05:48.260 --> 05:54.350 So if customers is our table on the left it will take every customer and then on the right it will try 05:54.350 --> 05:59.870 and match or it will match any or it will add any matching records from orders. 05:59.870 --> 06:00.910 In our case. 06:01.220 --> 06:05.080 So these diagrams just talking about it like this I find isn't that helpful. 06:05.090 --> 06:11.030 But basically think of it as it takes the union of where they match just like enter. 06:11.450 --> 06:15.110 But then it also does everything from the left table from customers. 06:15.110 --> 06:16.200 In our case. 06:16.550 --> 06:18.960 So let's test it out over in cloud 9. 06:19.040 --> 06:21.010 Have a comment here for my join. 06:21.320 --> 06:27.560 And let's go take one of our joint that we did earlier like let's do this first one. 06:27.560 --> 06:29.700 This was an inner join. 06:30.530 --> 06:36.980 And one thing I didn't mention is you can explicitly write in or join and I'll show you it does the 06:36.980 --> 06:37.860 exact same thing. 06:37.880 --> 06:43.110 If you leave it off it's implied that it's going to be an inner join it's most common type. 06:43.160 --> 06:50.000 What it does is we know it takes customers where the I.D. matches customer I.D. and that's all we get. 06:50.000 --> 06:52.340 And that's the inner part that matches. 06:52.340 --> 07:01.510 Now if we change this to be left and select everything you'll see we get a couple of differences. 07:01.540 --> 07:04.570 So we still have to kind of ugly to look at. 07:04.570 --> 07:05.410 I'm sorry. 07:05.410 --> 07:07.960 We have Boy George in the order he placed. 07:07.990 --> 07:12.970 And Boy George another order he placed George Michael two orders that match. 07:12.970 --> 07:15.430 But then we have two users who never placed orders. 07:15.460 --> 07:18.660 David Bowie in Blue Steel do not have orders. 07:18.760 --> 07:22.560 And so we end up with David Bowie but still no no no no. 07:22.570 --> 07:27.440 For the orders portion and then Bette Davis and the order that she placed. 07:27.940 --> 07:33.160 So we have customers on the left orders on the right. 07:33.250 --> 07:35.240 We're taking everything from customers. 07:35.260 --> 07:39.620 So that's why we end up with David Bowie and blue steel. 07:39.940 --> 07:45.340 And then we take the intersection as well where there is an impeccable match. 07:45.460 --> 07:47.880 So for David Bowie and blue still there isn't one. 07:47.920 --> 07:53.470 And it fills in NO but for Boy George and George Michael and Bette Davis. 07:53.470 --> 08:00.970 There is a matching order so I know it's kind of difficult to look at and understand but think of it 08:00.970 --> 08:01.520 again. 08:01.600 --> 08:05.330 We have customers and we go through with it beginning. 08:05.530 --> 08:08.060 Boy George is there a matching order. 08:08.350 --> 08:09.070 Yes. 08:09.250 --> 08:11.810 This one here right here and this one. 08:12.100 --> 08:13.940 So put those together. 08:14.140 --> 08:15.790 George Michael Id have to. 08:15.790 --> 08:17.050 Is there any marching orders. 08:17.170 --> 08:17.490 Yes. 08:17.500 --> 08:18.450 There's two. 08:19.000 --> 08:22.840 Put those two together next to George Michael David Bowie. 08:22.840 --> 08:24.280 Is there a matching order. 08:24.280 --> 08:24.920 No. 08:24.980 --> 08:27.900 So just put David Bowie because it's a left join. 08:28.240 --> 08:32.150 If it was an inner join then David Bowie and blues still don't show up. 08:32.290 --> 08:36.320 But it's a left join which means we're taking everything from the left table. 08:36.760 --> 08:41.370 And it works perfectly well depending on what you're trying to do. 08:41.710 --> 08:44.460 So that brings us to the question why would you do this. 08:44.650 --> 08:49.690 And before we get there I'm going to just clean this one up again so that we can see it a little bit 08:50.050 --> 08:50.730 easier. 08:50.740 --> 09:01.280 Let's only select first name last name order date and amount just like before. 09:01.330 --> 09:03.970 So that brings us again to the question why would you do this. 09:03.970 --> 09:08.500 Why would you want to have access to information like David Bowie and blue steel. 09:08.980 --> 09:10.720 And it depends on what you're doing. 09:10.870 --> 09:17.350 And in this case maybe I previously I wanted to see OK all the orders that have been placed and names 09:17.350 --> 09:18.060 next to them. 09:18.340 --> 09:22.800 But now what if I want to tabulate the high spenders on our site. 09:22.900 --> 09:25.540 But I want to include people who haven't spent anything. 09:25.690 --> 09:31.480 So I just want to be able to see for every user how much have they spent and I'd be able to go through 09:31.480 --> 09:34.810 and say you know maybe send an e-mail to the people who have spent a lot. 09:35.020 --> 09:39.000 OK thanks for being a loyal customer versus people who haven't spent. 09:39.160 --> 09:40.600 I could go through and send an e-mail. 09:40.600 --> 09:46.270 Here's a coupon for your first purchase 10 percent off something like that right where I want to basically 09:46.270 --> 09:53.020 get some insight where it involves knowing everything about all users and some of them have corresponding 09:53.020 --> 09:55.350 orders and some of them don't. 09:55.360 --> 10:00.070 Now that brings us to another topic which is OK but we have Knoll's here. 10:00.070 --> 10:03.250 So is there a way to change that. 10:03.610 --> 10:05.520 Let's say I want to do the same thing. 10:05.570 --> 10:07.510 We had a period where we had our high spenders. 10:07.510 --> 10:08.160 Right. 10:08.530 --> 10:15.660 So we selected and we summed all the orders based off of grouping them by customer IDs so it's a lot 10:15.660 --> 10:16.020 again. 10:16.030 --> 10:21.220 But let's do that here with a left join and there's going to be a slight difference right because we 10:21.220 --> 10:22.870 have NOLs. 10:22.870 --> 10:28.630 So if we do this it's like first name last name order date and amount from customers left join orders 10:29.050 --> 10:32.250 and we do a group by customers at this time. 10:32.560 --> 10:39.250 So that will group by customers that we have these two grouped these two groups on his own on his own 10:39.400 --> 10:40.040 on her. 10:40.140 --> 10:42.580 And actually it is a girl I'm sorry. 10:43.320 --> 10:43.810 OK. 10:44.170 --> 10:47.140 So if we do this we won't see much difference. 10:47.140 --> 10:49.700 Well we will see a difference but it's not very meaningful. 10:49.750 --> 10:50.600 What am I missing. 10:50.950 --> 10:58.580 Extra semi-colon not that useful because it just condenses our data and hides stuff. 10:58.770 --> 11:01.300 But if we go through and format this again 11:04.100 --> 11:05.650 put some together everything. 11:05.710 --> 11:06.500 Right. 11:06.550 --> 11:14.880 So will some and inside of here we want to some amount and we don't actually need to have a mount there 11:14.940 --> 11:16.240 and we can get rid of order date too. 11:16.240 --> 11:20.910 So let's just have the first last name and then some. 11:20.920 --> 11:23.360 OK so this is accurate. 11:23.360 --> 11:24.020 That's accurate. 11:24.020 --> 11:25.130 That's accurate. 11:25.150 --> 11:31.790 We still have no hair which it can work but it would be nice if instead of just said 0 because they've 11:31.790 --> 11:33.280 spent $0. 11:33.560 --> 11:35.030 So how do we do that. 11:35.060 --> 11:39.340 And this is not an energy is not a join specific thing in any way. 11:39.410 --> 11:41.490 This is just an interesting problem. 11:41.510 --> 11:45.100 How do we get instead of no how do I replace that with zero. 11:45.590 --> 11:46.760 And there's a couple of ways. 11:46.760 --> 11:52.310 The first really length the awful way is to use a case statement so I could have a case statement where 11:52.310 --> 12:01.070 I checked is some amount is no number I can't just say equals nil but if I said it's is null then do 12:01.070 --> 12:04.160 0 otherwise do some amount. 12:04.160 --> 12:05.990 But that's that's kind of long. 12:06.380 --> 12:14.870 So it's going to be much easier if I use what's known as if no and haven't shown this to you yet because 12:14.870 --> 12:17.410 we didn't really have many cases where we had no data. 12:17.570 --> 12:18.540 But here's one. 12:18.760 --> 12:21.230 So what if does it takes two arguments. 12:21.230 --> 12:25.190 The first one is the thing you want it to check if it is known or not. 12:25.190 --> 12:27.910 So we want to check is some of amount. 12:27.920 --> 12:28.690 No. 12:29.120 --> 12:31.430 So here it's not right it's not. 12:31.430 --> 12:32.330 No it's not. 12:32.330 --> 12:32.870 No. 12:32.870 --> 12:34.310 Here it is. 12:34.310 --> 12:40.320 So then the second argument is what we want to replace it with if it is null and we'll just say zero 12:41.600 --> 12:42.980 so it's very simple. 12:43.010 --> 12:44.840 It checks if the first argument is no. 12:44.870 --> 12:50.540 If it is it replaces it with the second argument if it's not it leaves it as the first. 12:50.540 --> 12:55.300 So now if I try it perfect we get zero. 12:55.670 --> 12:57.180 We should definitely add an alias. 12:57.320 --> 12:58.690 Let's call it total spend. 12:58.690 --> 13:02.000 Again OK. 13:02.240 --> 13:11.050 One other thing that would be nice is to order by total spent and this time let's do it from least. 13:11.210 --> 13:15.680 So we'll just do order by total spent and that should work. 13:15.680 --> 13:16.660 There we go. 13:16.670 --> 13:18.990 Now you can see we have zero. 13:19.640 --> 13:20.240 OK. 13:20.690 --> 13:22.310 So we kind of talked about a lot. 13:22.310 --> 13:24.470 There's a couple of topics that touch on each other. 13:24.470 --> 13:31.310 The first thing just to recap was that I wanted to show you when we do joints any joints. 13:31.310 --> 13:33.050 They're not fancy special tables. 13:33.050 --> 13:35.300 They work just the same way as any other table. 13:35.300 --> 13:42.790 So we can do things like grouped by an order by then the next thing was that we saw inner joints. 13:42.920 --> 13:47.490 I go back we saw this and we also saw a left join. 13:47.640 --> 13:48.690 And the key difference. 13:48.690 --> 13:53.050 They both join data from two tables with the key differences what data they join. 13:53.220 --> 13:54.410 So we give him a condition. 13:54.420 --> 14:02.760 But even given the same condition in our case it was a join on customer ID equals orders customer ID. 14:02.910 --> 14:08.600 This middle part in an inner join That's all we get is the exact overlap on a left join. 14:08.700 --> 14:15.570 We also get the left part in addition so every customer in our case which can be useful if we wanted 14:15.570 --> 14:19.050 to see things like which customers hadn't ordered things. 14:19.620 --> 14:21.080 Which is what we got here. 14:21.120 --> 14:24.020 So hopefully that explanation made some sense. 14:24.030 --> 14:25.860 I totally understand if it didn't. 14:26.040 --> 14:30.200 This is a a tough thing to really wrap your head around at first. 14:30.240 --> 14:36.180 Now it's not terribly difficult once you get going with it but understanding it it can be super intimidating. 14:36.180 --> 14:42.290 So if you're in that situation we will have exercises coming up in just one more video after this. 14:42.630 --> 14:44.510 And I recommend you try and go through those. 14:44.880 --> 14:48.800 And if that's not enough then absolutely ask for help in the forums here. 14:48.810 --> 14:54.580 This is one of the crucial things that in my experience sometimes take students a couple of hours a 14:54.580 --> 14:55.520 couple of days. 14:55.560 --> 14:58.020 Not not like 12 20 hour days. 14:58.020 --> 15:03.870 I just mean sleeping on it once or twice to help kind of get some space in your brain. 15:04.110 --> 15:08.370 Anyways hopefully you're able to kind of get where I'm going with this. 15:08.400 --> 15:11.520 Next up we're going to see our last type of join which is right join. 15:11.520 --> 15:17.160 Very simple can so simple and very easy to grasp if you understand left joints.