WEBVTT 00:00.390 --> 00:04.830 OK so we've got this data in our database we have two separate tables that are distinct. 00:04.920 --> 00:06.650 They have a foreign key. 00:06.690 --> 00:12.810 The orders has a foreign key referencing customer ID but so far we haven't actually done any queries 00:12.810 --> 00:14.190 you haven't worked with our data. 00:14.370 --> 00:15.910 So let's try it now. 00:16.500 --> 00:18.190 So back over in cloud 9. 00:18.600 --> 00:20.670 Let's start with a simple question. 00:20.940 --> 00:27.920 Let's look at our customers actually look at our orders and I have five different orders here. 00:28.230 --> 00:33.920 Let's say I want to find the orders that have been placed by boy by Boy George. 00:34.590 --> 00:37.770 So how do we do it and what the orders placed by Boy George. 00:38.070 --> 00:40.510 Well we could do it as a two step process. 00:40.560 --> 00:48.170 So I would first say basically find the customer find the ID of the customer whose name is boy George. 00:48.210 --> 00:57.540 So we could do select start from customers where and then we would do something like where the last 00:57.540 --> 01:01.010 name equals George. 01:02.010 --> 01:08.570 OK so we get Boy George George Jim calm and his e-mail exceeded his ID is one. 01:08.820 --> 01:09.170 OK. 01:09.180 --> 01:15.960 Then we take that and we go to our orders table and we try and find OK where is in the Orders table 01:15.990 --> 01:19.700 is customer id referencing one. 01:19.830 --> 01:30.900 So we're just going to do a select star from customers where excuse me it's like start from orders where 01:31.140 --> 01:34.760 customer ID equals one. 01:35.670 --> 01:36.450 And there we go. 01:36.510 --> 01:39.320 These are the two orders placed by Boy George. 01:39.690 --> 01:46.740 So that was a two step process and we could simplify that or we could at least do it all at once actually 01:46.740 --> 01:48.560 using something that we touched on. 01:48.720 --> 01:51.400 You could use a subquery if you'd like to try and do that. 01:51.560 --> 01:57.690 Can take a moment to figure that out but I'm just going to write it down here. 01:58.140 --> 01:59.120 We would do something like this. 01:59.130 --> 02:03.430 The two things we just did we we let's just copy this one. 02:04.650 --> 02:05.780 We did this right here. 02:05.930 --> 02:14.060 So let's start from orders were customer ideas one and then we also had like start from customers. 02:14.180 --> 02:21.900 So to put them together what we basically want to do is find the the idea of the customer where last 02:21.900 --> 02:23.070 name is George. 02:23.190 --> 02:25.350 Then plug that into here. 02:25.680 --> 02:29.770 So rather than hard coding it us one so it will look it's going to be a bit long. 02:29.930 --> 02:39.180 It's going to be something like select star from orders where and then where is going to be long. 02:39.540 --> 02:53.430 So it's going to be a sub query and we want to do where select ID from customers where last name equals 02:54.150 --> 02:55.570 George. 02:55.900 --> 02:57.990 OK so let's take a look. 02:58.030 --> 03:05.410 We need to fix one thing actually like star from orders where customer ID equals the result of this. 03:05.750 --> 03:07.750 OK so let's step through it. 03:07.750 --> 03:09.460 Start with this middle part. 03:09.460 --> 03:13.910 This runs it selects ID from customers where last name is George. 03:14.050 --> 03:15.540 Which is what we did up here. 03:15.670 --> 03:24.330 So when we run that we're only selecting ID so actually we could change that because we're on the sighting 03:24.330 --> 03:28.630 ID and it gives us one so you could think of this whole thing. 03:28.800 --> 03:30.070 It's replaced with one. 03:30.330 --> 03:35.640 Actually it's more like this whole thing is replaced with one which leaves us with select star from 03:35.640 --> 03:38.770 orders where customer ID equals one. 03:39.210 --> 03:45.700 So let's test it out and you can see we get the same result but this is still not ideal it's a bit cumbersome 03:46.050 --> 03:49.020 and this is only if we're working with one user anyways. 03:49.110 --> 03:55.090 What if I wanted to see a synthesis of my data if I wanted to see next to every order. 03:55.230 --> 03:58.510 So something like this go up. 03:58.620 --> 04:02.700 Let's do so next start from orders next to every order. 04:02.700 --> 04:08.500 I wanted it not just to say customer id I wanted it to say the name of who made the order. 04:08.790 --> 04:10.020 So boy George. 04:10.020 --> 04:14.570 George Michael Bette Davis and I wanted that to be printed here. 04:15.180 --> 04:16.480 So how do we do that. 04:16.680 --> 04:22.500 And this leads us to our next topic which is joint so joins allow us to accomplish that. 04:22.620 --> 04:29.160 The whole point of joints which is sort of this buzz word the maybe intimidating mysterious thing to 04:29.160 --> 04:30.100 some of you. 04:30.300 --> 04:35.660 The whole point of it is that it takes two tables and we can can join them in a couple different configurations 04:35.660 --> 04:37.100 that will talk about what those are. 04:37.170 --> 04:38.460 That's the next few videos. 04:38.640 --> 04:43.560 But we can can join them basically take the data from one and take the data from another and stick them 04:43.560 --> 04:47.120 together in usually a meaningful way. 04:47.130 --> 04:50.990 But I'm going to show you one way that is not meaningful to start. 04:51.150 --> 04:56.080 And this will be our first most basic join that you probably will never use. 04:56.130 --> 04:59.440 I never have used it but it's good to know. 04:59.850 --> 05:01.060 And it just looks like this. 05:01.140 --> 05:04.010 Select star from orders. 05:04.500 --> 05:05.650 We can do customers first. 05:05.660 --> 05:06.870 Doesn't matter. 05:06.990 --> 05:10.860 Customers comma orders and that's it. 05:10.860 --> 05:18.690 So if we just select from both of them what do you think will happen when I hit enter here and we get 05:18.690 --> 05:19.830 this monstrosity. 05:19.860 --> 05:20.860 It's very long. 05:22.450 --> 05:25.230 And what we're actually looking at is called a cross join. 05:25.340 --> 05:28.890 I think I've also heard it called a cartesian join. 05:29.150 --> 05:35.740 Like I said it's pretty useless but it's interesting to see what it does is it takes every customer. 05:35.930 --> 05:40.430 So we have boy boy George George Michael David Bowie blue steel. 05:40.430 --> 05:41.600 Bette Davis. 05:41.840 --> 05:46.070 But then notice that let's ignore the right half just just the customers. 05:46.070 --> 05:49.030 It goes from one to five and then it repeats. 05:49.040 --> 05:49.640 Boy George. 05:49.640 --> 05:50.720 David Bowie. 05:50.750 --> 05:51.640 Blue Steel. 05:51.650 --> 05:52.390 Bette Davis. 05:52.430 --> 05:54.230 And then boy George George Michael David Bowie. 05:54.230 --> 06:03.710 And it keeps going and what it's doing is taking every customer and conjoining it with every order they 06:03.710 --> 06:05.570 give it is almost multiplying them. 06:05.840 --> 06:15.080 So it's taking the first order which is $99 with an order ID of one and it's sticking that next to Boy 06:15.080 --> 06:17.250 George Order ID one. 06:17.540 --> 06:19.690 George Michael or her ID one. 06:19.910 --> 06:26.600 David Bowie same order blue steel same order Bette Davis same order and then it moves on to the next 06:26.630 --> 06:27.650 order. 06:27.890 --> 06:28.880 So then we go back. 06:28.880 --> 06:34.060 Boy George stuck with the second order second order second order it basically. 06:34.060 --> 06:38.240 Hopefully you can see what's happening and a lot of text to look at but it's taking these two tables 06:38.510 --> 06:44.600 and just jamming every row every possible combination next to each other which is meaningless. 06:44.600 --> 06:50.120 There's no reason to do it but I just wanted to show you it because it's the first and most basic type 06:50.120 --> 06:50.770 of joint. 06:50.900 --> 06:53.680 So don't make much of the fact that you can do it. 06:53.740 --> 06:55.170 It's just good to know that you can. 06:55.250 --> 07:01.360 But we're much more interested in basically whittling this down to the stuff that we care about so the 07:01.360 --> 07:01.750 more. 07:01.790 --> 07:06.680 Maybe the most obvious and meaningful way would be to whittle it down so that we only see where there's 07:06.680 --> 07:08.160 actually overlap. 07:08.240 --> 07:16.430 So where the idea of the customer is the same as the customer ID so this would be a match right. 07:16.430 --> 07:18.880 This is an order placed by Boy George. 07:18.880 --> 07:23.830 But then none of these are actually placed by the customer they're next to. 07:24.320 --> 07:27.340 But then we get down here and we have. 07:27.360 --> 07:31.160 OK here's George Michael with an idea of two and he placed this order. 07:31.370 --> 07:33.180 So how do we whittle it down to that. 07:33.530 --> 07:35.090 And that's what we'll see in the next video. 07:35.090 --> 07:36.210 Sorry for the cliffhanger.