WEBVTT 00:00.230 --> 00:07.140 OK so in the last video we saw our first join our first example which is actually something known as 00:07.140 --> 00:14.010 an implicit join which you'll see for a moment where it's called implicit and it's also in particular 00:14.310 --> 00:17.890 it is a CROSS JOIN useless basically. 00:18.030 --> 00:24.020 So it just think of it as multiplying our data across multiplying it takes every possible combination. 00:24.060 --> 00:26.880 So we have Boy George Well I don't need to go over that again. 00:26.910 --> 00:28.450 Bore bored to death. 00:28.470 --> 00:32.310 So now we want to focus on whittling it down to what's actually relevant. 00:32.310 --> 00:38.610 I want to see the name of the name first name my surname and email of every user who actually placed 00:38.610 --> 00:45.860 an order next to that order not just every arbitrary order I want the ones they placed. 00:46.060 --> 00:49.950 And so we're getting sort of close if we have all of it next to each other. 00:49.950 --> 00:58.530 Now we just want to only select the rows where what what condition is true where our user ID is equal 00:58.530 --> 00:59.320 to the order. 00:59.340 --> 01:00.760 Customer ID. 01:01.080 --> 01:06.320 So it's just like any other kind of select we've done where we just can add in a where. 01:06.360 --> 01:17.790 So it will be a select star from customers comma borders where and where what is true where ID from 01:17.790 --> 01:20.650 the customer is equal to customer ID. 01:20.910 --> 01:26.200 So we could do this where a customer equals customer underscore ID. 01:26.550 --> 01:29.300 But there's a problem and maybe you've identified it. 01:29.400 --> 01:35.860 We have two IDs remember we're just taking the customers table in the Orders table and putting them 01:35.860 --> 01:36.530 together. 01:36.580 --> 01:41.260 So we have I.T. from customers and ID from orders. 01:41.390 --> 01:43.290 So what does this refer to. 01:44.570 --> 01:45.420 That's a problem. 01:45.590 --> 01:47.120 Fortunately there's a workaround. 01:47.120 --> 01:48.950 It's very very simple. 01:48.950 --> 01:52.210 All we have to do is prepared the name at the tables. 01:52.460 --> 01:57.330 So this should be our customers idea right. 01:59.300 --> 02:01.060 It's coming from here. 02:01.230 --> 02:08.460 And just to make that clear what's happening we're saying where the customers table Id So where this 02:09.090 --> 02:11.310 is equal to customer ID. 02:11.580 --> 02:17.850 Now customer underscore ID and I realize there's a lot of saying customer and Id over and over but these 02:17.850 --> 02:18.880 are different. 02:18.960 --> 02:19.360 Right. 02:19.380 --> 02:25.050 This is the customers table ID that's referring to this customers dot. 02:25.080 --> 02:34.110 That dot means in the Customers table and this is customer underscore ID which is referring to this 02:34.110 --> 02:35.460 right here. 02:35.670 --> 02:43.210 So we dont have to say orders dot customer ID but its a good convention to follow to do that just to 02:43.230 --> 02:49.500 whenever you're working with two or more tables to be explicit about which table were which row. 02:49.520 --> 02:52.470 Richard excuse me which column belongs to which table. 02:53.320 --> 02:53.840 OK. 02:54.150 --> 02:55.770 So we have this line. 02:56.410 --> 02:57.820 Let's copy it over. 02:57.930 --> 03:06.390 So what this will do is select it's going to do this cross join but only where the customer's ID customer 03:06.390 --> 03:10.570 table ID is equal to the customer ID of the order. 03:10.780 --> 03:15.280 So now if we do this take a look at what we get. 03:15.430 --> 03:16.720 We have five orders. 03:16.780 --> 03:24.630 So we get these five different rows and we have a corresponding customer who placed it. 03:24.730 --> 03:28.140 So Boy George placed this order for $99. 03:28.210 --> 03:30.460 Boy George placed another order for $35. 03:30.460 --> 03:36.740 George Michael placed these two orders and Bette Davis placed this one order for $450. 03:37.240 --> 03:44.260 So you can see we have customer id over here and its matching the customer's ID and of course we could 03:44.260 --> 03:53.170 condense this a bit to maybe we dont want to start maybe we want first name and last name and then order 03:53.170 --> 03:56.560 date and amount and that's it. 03:56.560 --> 03:58.180 Just like that. 03:58.210 --> 04:06.630 So let's try it and now we get a more condensed table that has had information Boy George or George 04:07.290 --> 04:12.870 tourers George Michael one order George Michael another order Bette Davis another order. 04:13.320 --> 04:21.520 So what we've just done there we have joined them together using what's known as an implicit inner join. 04:21.630 --> 04:23.860 So I'll write that here. 04:27.090 --> 04:27.630 OK. 04:27.960 --> 04:29.820 And that's what we did appear as well. 04:29.830 --> 04:33.130 So this is just a more refined one. 04:33.160 --> 04:38.430 Kids we're not selecting star we're only citing a couple of things but we've joined them where they 04:38.430 --> 04:40.810 match if that makes sense. 04:41.130 --> 04:46.620 So I have a simple diagram it's very very ugly but a simple diagram to show you and I'm sorry it's so 04:46.620 --> 04:46.970 ugly. 04:46.980 --> 04:49.060 It was very difficult. 04:49.110 --> 04:51.430 It's filling in this shape. 04:51.450 --> 04:57.750 It's not a circle and the slides tool I used doesn't let you draw weird shapes you have to do circles. 04:57.750 --> 04:59.480 So I'm sorry I really am. 04:59.650 --> 05:05.280 But what's happening here is that we have two tables right customers and orders and what we did at first 05:05.910 --> 05:07.540 was select everything. 05:07.800 --> 05:09.670 Combine them and just take everything together. 05:09.810 --> 05:16.860 But all that we want to do with an inner join is take it inside where they overlap and that's what we 05:16.860 --> 05:17.530 did. 05:17.550 --> 05:27.110 We went from this monstrosity and we will down to where the customer's ID is equal to customer ID in 05:27.110 --> 05:30.490 the orders table and we get this right here. 05:31.700 --> 05:32.340 OK. 05:32.640 --> 05:34.380 Now I said this is implicit. 05:34.500 --> 05:37.970 And that's because there is an actual explicit syntax. 05:38.010 --> 05:40.040 There's a better way of doing this. 05:40.050 --> 05:44.550 And when I say better it really means that it is more conventional If you're not familiar with that 05:44.770 --> 05:51.220 it means it is they give it as like an unwritten rule that developers follow. 05:51.360 --> 05:57.490 They think it's cleaner it's more easily understood to do it this way which I'm about to show you. 05:57.600 --> 06:00.790 So I'm going to write that I do. 06:00.830 --> 06:01.570 Here you go. 06:01.620 --> 06:09.390 And when I write a comment explicit inner join and this will be the first time we see a new keyword 06:09.420 --> 06:11.000 called join. 06:11.550 --> 06:14.120 So we're going to do our same select star. 06:14.130 --> 06:18.230 We'll start with this one from customers. 06:18.480 --> 06:23.790 However we're not just going to do come orders we're going to start from customers and then we're going 06:23.790 --> 06:27.330 to add joyn orders. 06:27.420 --> 06:31.950 So we have some new syntax I'm just going to type it all first and then we're going to tell it where 06:32.440 --> 06:42.820 we're going to join on customers thought Id equals orders dot customer ID. 06:42.980 --> 06:44.140 OK. 06:44.190 --> 06:51.140 So if I copy this and I run this it does the exact same thing. 06:51.630 --> 06:56.490 And if we want to of course we could refine this more just like we did earlier so instead of selecting 06:56.490 --> 07:05.380 star let's do the same thing or we select first name last name order date and amount. 07:05.640 --> 07:06.350 OK. 07:06.750 --> 07:15.850 Copy this over and now we get this the same table in just to prove to you that it's the same. 07:15.930 --> 07:17.670 The ego can even see the difference when I. 07:17.690 --> 07:20.490 I just hit enter and it looks like the same thing is here. 07:20.890 --> 07:21.160 OK. 07:21.160 --> 07:23.440 So this is still an inner join. 07:23.800 --> 07:29.060 But this time it's explicit and it's really only explicit because we're writing the word join. 07:29.110 --> 07:36.030 So when we say join order so we say from customers join orders on this join condition. 07:36.190 --> 07:42.790 So take the left and the right to which customers and orders these two circles basically enjoying them 07:42.820 --> 07:50.580 create the Union table where customer ID is equal to orders customer ID. 07:50.680 --> 07:57.880 So where the ID from here matches the customer ID from here take that overlap and make this table for 07:57.880 --> 07:59.360 us. 07:59.530 --> 08:02.460 So I know it's a lot and I'm talking about here. 08:02.580 --> 08:07.280 Now the term inner join an explicit inner join implicit inner join. 08:07.280 --> 08:10.410 And we're going to next I'm talking about right joints left. 08:10.520 --> 08:12.140 It's can be overwhelming. 08:12.320 --> 08:20.360 So I want you to focus on is the key idea here that we are joining data together based off of a condition. 08:20.800 --> 08:21.380 Right. 08:21.400 --> 08:26.280 And we could join that off of some meaningless condition to just to show you. 08:26.280 --> 08:37.430 So if we go back to this this really ugly cross join where it takes everything and joins it together. 08:38.230 --> 08:45.880 So if I wanted to there's no meaning to be garnered from this but I could join it where the two ideas 08:45.940 --> 08:49.730 are the same or not customer ID but I can say where customers. 08:49.750 --> 08:51.860 Id is the same as orders that ID. 08:51.880 --> 08:54.280 So let me just show you that now. 08:55.300 --> 09:01.760 And I'll just comment this as arbitrary join. 09:02.470 --> 09:13.510 Don't do this but we could do this we could select star from customers join orders on not to say we're 09:13.510 --> 09:15.520 customers ID equals orders. 09:15.550 --> 09:20.270 Heidi there's nothing stopping me from doing this. 09:20.380 --> 09:27.540 And as you can see I just end up with this new table that has a matching IDs so we get. 09:27.820 --> 09:30.940 Let's take an example like George Michael. 09:30.940 --> 09:36.600 So George Michael is now matched he has an idea of two he's matched with the order of I.D. two. 09:36.700 --> 09:41.940 It doesn't matter that it was placed by customer with ID of one which is boy George. 09:42.010 --> 09:46.190 So I'm saying that we can just join things on arbitrary conditions. 09:46.240 --> 09:50.860 However you're typically what goes in here is you're filling in a foreign key. 09:50.860 --> 09:53.440 Matching is a primary key in another table. 09:53.500 --> 09:59.510 So primary key is a customer's I.D. a foreign key is the customer underscore I.D. in the Orders table 10:00.610 --> 10:05.660 and then the last thing that I'll wrap up with here is does the order matter. 10:06.160 --> 10:09.370 So if we go back to our cross join. 10:10.150 --> 10:16.420 What if I reverse order so I started with customers comp orders and so we get customers here and then 10:16.450 --> 10:18.850 orders tacked on on the right. 10:18.850 --> 10:30.520 So what if I switch it orders Khama customers and you can see well it looks very similar except it doesn't 10:30.520 --> 10:33.710 look similar we had the same data but it's been switched. 10:33.730 --> 10:40.300 So we start with every order first and then one customer tacked onto the end and then every order again 10:40.330 --> 10:41.670 and then the same customer. 10:41.890 --> 10:47.840 So the order does matter in a sense as to how your data is presented to you but it doesn't matter in 10:47.840 --> 10:54.430 the in the case of a cross join or in the case of an inner join it won't actually affect the result. 10:54.430 --> 10:57.250 It just affects what is printed out what it looks like. 10:57.250 --> 11:01.580 So let me show you if we take this same We've got a lot of them going on here. 11:01.600 --> 11:03.450 Let's take the same one here. 11:03.490 --> 11:10.390 This is kind of the most useful join we had right where we had the name and the order date and the amount. 11:10.390 --> 11:15.430 If I switch I'm just going to duplicate it so that we have the original Oswell. 11:15.580 --> 11:24.620 If I say from orders join customers on and we can leave this either way. 11:24.880 --> 11:25.960 But let's say do that. 11:25.960 --> 11:31.260 So I'm selecting from orders with joining customers onto it. 11:32.340 --> 11:33.640 And I hit enter. 11:33.640 --> 11:40.360 Nothing changes and that's because we are picking our data you know individually from a peer. 11:40.360 --> 11:41.890 Now if I just did a star 11:44.630 --> 11:49.450 it will look a little different because you can see we're actually getting order first so order ID order 11:49.450 --> 11:51.950 date amount and then customer. 11:52.170 --> 11:53.290 And if I switch that 11:57.160 --> 11:58.500 it would be the other way around. 11:58.610 --> 11:59.840 That doesn't really matter. 11:59.990 --> 12:08.160 It's just orders but you can see now we get customers and then orders. 12:08.160 --> 12:08.750 All right. 12:08.790 --> 12:16.290 So this has been long and a little difficult I imagine especially some of the syntax or keeping that 12:16.290 --> 12:17.600 jargon. 12:17.600 --> 12:23.700 What I do want to say is that basically when you're joining things don't do implicit joins which is 12:23.700 --> 12:24.690 what we have here. 12:24.930 --> 12:27.780 It's better to do it explicitly this way. 12:28.050 --> 12:34.920 So something like this or this where we have select something from a table join on another table on 12:35.090 --> 12:37.690 a condition OK. 12:37.730 --> 12:40.750 So that is kind of the bread and butter of joints doing an inner join. 12:40.760 --> 12:44.810 But I'm going to show you a couple of other examples that will be up next.