1 00:00:00,630 --> 00:00:02,250 So we've got this data in our database. 2 00:00:02,250 --> 00:00:04,730 We have two separate tables that are distinct. 3 00:00:04,740 --> 00:00:06,660 They have a foreign key. 4 00:00:06,660 --> 00:00:12,840 The orders has a foreign key referencing customer ID, but so far we haven't actually done any queries. 5 00:00:12,840 --> 00:00:15,540 We haven't worked with our data, so let's try it now. 6 00:00:16,350 --> 00:00:20,550 So back over in Cloud nine, let's start with a simple question. 7 00:00:20,880 --> 00:00:22,380 Let's look at our customers. 8 00:00:23,190 --> 00:00:24,720 Well, actually, let's look at our orders. 9 00:00:25,980 --> 00:00:27,810 And I have five different orders here. 10 00:00:28,080 --> 00:00:33,480 Let's say I want to find the orders that have been placed by by Boy George. 11 00:00:34,470 --> 00:00:35,490 So how do I do that? 12 00:00:35,490 --> 00:00:37,680 I want the orders placed by Boy George. 13 00:00:37,920 --> 00:00:40,470 Well, we could do it as a two step process. 14 00:00:40,470 --> 00:00:48,210 So I would first say, basically find the customer, find the ID of the customer whose name is Boy George. 15 00:00:48,210 --> 00:00:58,290 So we could do select star from customers where and then we would do something like where last name 16 00:00:58,290 --> 00:01:00,300 equals George. 17 00:01:02,520 --> 00:01:03,930 So we get Boy George. 18 00:01:03,930 --> 00:01:08,610 George at gmail.com and his email excuse me, his ID is one. 19 00:01:09,090 --> 00:01:16,200 So then we take that and we go to our orders table and we try and find where is in the order table is 20 00:01:16,200 --> 00:01:18,720 customer ID referencing one. 21 00:01:19,740 --> 00:01:31,050 So we're just going to do a select star from customers where excuse me, select star from orders where 22 00:01:31,050 --> 00:01:34,290 customer ID equals one. 23 00:01:35,670 --> 00:01:36,480 And there we go. 24 00:01:36,510 --> 00:01:39,150 These are the two orders placed by Boy George. 25 00:01:39,540 --> 00:01:41,310 So that was a two step process. 26 00:01:42,030 --> 00:01:47,550 And we could simplify that, or we could at least do it all at once, actually using something that 27 00:01:47,550 --> 00:01:48,590 we touched on. 28 00:01:48,600 --> 00:01:49,950 We could use a subquery. 29 00:01:49,950 --> 00:01:54,750 If you'd like to try and do that, you can take a moment to figure that out, but I'm just going to 30 00:01:54,750 --> 00:01:55,770 write it down here. 31 00:01:58,080 --> 00:01:59,070 We would do something like this. 32 00:01:59,070 --> 00:02:02,430 So the two things we just did, we let's just copy this one. 33 00:02:04,650 --> 00:02:08,240 We did this right here, select star from orders where customer ID is one. 34 00:02:08,250 --> 00:02:09,780 And then we also had. 35 00:02:11,650 --> 00:02:13,510 Select start from customers. 36 00:02:14,050 --> 00:02:15,700 So to put them together. 37 00:02:15,730 --> 00:02:23,980 What we basically want to do is find the the ID of the customer where last name is George, then plug 38 00:02:23,980 --> 00:02:25,210 that in to here. 39 00:02:25,510 --> 00:02:29,830 So rather than hard coded as one so it would look it's going to be a bit long. 40 00:02:29,870 --> 00:02:39,070 It's going to be something like select star from orders where and then where is going to be long. 41 00:02:39,370 --> 00:02:43,570 So it's going to be a sub query and we want to do where? 42 00:02:43,690 --> 00:02:54,880 Select ID from customers where last name equals George. 43 00:02:55,860 --> 00:02:56,490 Okay. 44 00:02:56,490 --> 00:02:57,810 So let's take a look. 45 00:02:57,990 --> 00:02:59,220 We need to fix one thing. 46 00:02:59,220 --> 00:03:05,160 Actually select star from orders where customer ID equals the result of this. 47 00:03:05,970 --> 00:03:07,170 So let's step through it. 48 00:03:07,710 --> 00:03:08,910 Start with this middle part. 49 00:03:09,360 --> 00:03:10,500 This runs. 50 00:03:10,620 --> 00:03:12,690 It selects ID from customers. 51 00:03:12,690 --> 00:03:15,540 Where last name is George, which is what we did up here. 52 00:03:15,540 --> 00:03:20,820 So when we run that, we're only selecting ID. 53 00:03:20,850 --> 00:03:26,340 So actually we could change that because we're only selecting ID and it gives us one. 54 00:03:26,790 --> 00:03:29,880 So you can think of this whole thing is replaced with one. 55 00:03:30,270 --> 00:03:35,700 Actually, it's more like this whole thing is replaced with one, which leaves us with select star from 56 00:03:35,700 --> 00:03:38,550 orders where customer ID equals one. 57 00:03:39,060 --> 00:03:43,020 So let's test it out and you can see we get the same result. 58 00:03:43,020 --> 00:03:44,880 But this is still not ideal. 59 00:03:44,880 --> 00:03:48,900 It's a bit cumbersome and this is only if we're working with one user anyways. 60 00:03:48,930 --> 00:03:52,590 What if I wanted to see a synthesis of my data? 61 00:03:52,590 --> 00:03:55,070 If I wanted to see next to every order? 62 00:03:55,080 --> 00:03:56,430 So something like this. 63 00:03:56,430 --> 00:04:00,300 If I go up, let's do select star from orders. 64 00:04:01,700 --> 00:04:02,720 Next to every order. 65 00:04:02,720 --> 00:04:08,000 I wanted it not just to say customer ID, I wanted it to say the name of who made the order. 66 00:04:08,660 --> 00:04:12,110 So, boy, George, George Michael, Bette Davis. 67 00:04:12,110 --> 00:04:14,150 And I wanted that to be printed here. 68 00:04:15,140 --> 00:04:16,310 So how do we do that? 69 00:04:16,550 --> 00:04:20,209 And this leads us to our next topic, which is joints. 70 00:04:20,480 --> 00:04:22,520 So joints allow us to accomplish that. 71 00:04:22,520 --> 00:04:29,060 The whole point of joints, which is sort of this buzzworthy, maybe intimidating, mysterious thing 72 00:04:29,090 --> 00:04:29,960 to some of you. 73 00:04:30,230 --> 00:04:35,630 The whole point of it is that it takes two tables and we can conjoin them in a couple of different configurations. 74 00:04:35,630 --> 00:04:37,070 So we'll talk about what those are. 75 00:04:37,100 --> 00:04:42,350 That's the next few videos, but we can conjoin them, basically take the data from one and take the 76 00:04:42,350 --> 00:04:46,400 data from another and stick them together in usually a meaningful way. 77 00:04:47,000 --> 00:04:51,060 But I'm going to show you one way that is not meaningful to start. 78 00:04:51,080 --> 00:04:56,020 And this will be our first most basic join that you probably will never use. 79 00:04:56,030 --> 00:04:59,150 I never have used it, but it's good to know. 80 00:04:59,720 --> 00:05:03,830 And it just looks like this select star from orders. 81 00:05:04,520 --> 00:05:05,630 We can do customers first. 82 00:05:05,630 --> 00:05:06,470 It doesn't matter. 83 00:05:06,950 --> 00:05:10,160 Customers comma orders and that's it. 84 00:05:10,730 --> 00:05:15,230 So if we just select from both of them, what do you think will happen? 85 00:05:15,920 --> 00:05:19,880 I'm going to hit enter here and we get this monstrosity. 86 00:05:19,880 --> 00:05:20,960 It's very long. 87 00:05:22,370 --> 00:05:24,980 And what we're actually looking at is called a cross join. 88 00:05:25,190 --> 00:05:27,890 I think I've also heard it called a Cartesian join. 89 00:05:29,120 --> 00:05:35,800 Like I said, it's pretty useless, but it's interesting to see what it does is it takes every customer. 90 00:05:35,810 --> 00:05:41,360 So we have boy, boy George, George, Michael, David Bowie, Blue Steel, Bette Davis. 91 00:05:41,690 --> 00:05:44,240 But then notice that let's ignore the right half. 92 00:05:44,240 --> 00:05:45,860 Just just the customers. 93 00:05:46,010 --> 00:05:48,990 It goes from 1 to 5, and then it repeats. 94 00:05:49,010 --> 00:05:49,670 Boy George. 95 00:05:49,670 --> 00:05:52,370 David Bowie, Blue Steel, Bette Davis. 96 00:05:52,370 --> 00:05:53,240 And then Boy George. 97 00:05:53,240 --> 00:05:53,780 George Michael. 98 00:05:53,780 --> 00:05:54,260 David Bowie. 99 00:05:54,260 --> 00:05:55,400 And it keeps going. 100 00:05:56,030 --> 00:06:02,690 And what it's doing is taking every customer and conjoining it with every order. 101 00:06:03,560 --> 00:06:05,420 Think of it as almost multiplying them. 102 00:06:05,660 --> 00:06:14,210 So it's taking the first order, which is $99, with an order ID of one, and it's sticking that next 103 00:06:14,210 --> 00:06:15,660 to Boy George. 104 00:06:15,680 --> 00:06:17,030 Order ID one. 105 00:06:17,510 --> 00:06:18,350 George Michael. 106 00:06:18,380 --> 00:06:19,760 Order ID one. 107 00:06:19,790 --> 00:06:20,780 David Bowie. 108 00:06:20,810 --> 00:06:21,730 Same order. 109 00:06:21,740 --> 00:06:23,350 Blue Steel, the same order. 110 00:06:23,360 --> 00:06:24,830 Bette Davis, same order. 111 00:06:24,830 --> 00:06:27,290 And then it moves on to the next order. 112 00:06:27,920 --> 00:06:28,880 So then we go back. 113 00:06:28,880 --> 00:06:31,940 Boy George stuck with the second order. 114 00:06:31,940 --> 00:06:33,260 Second order, second order. 115 00:06:33,410 --> 00:06:34,070 Basically. 116 00:06:34,070 --> 00:06:35,300 Hopefully you can see what's happening. 117 00:06:35,300 --> 00:06:40,640 It's a lot of text to look at, but it's taking these two tables and just jamming every row, every 118 00:06:40,640 --> 00:06:44,600 possible combination next to each other, which is meaningless. 119 00:06:44,600 --> 00:06:46,280 There's no reason to do it. 120 00:06:46,400 --> 00:06:50,870 But I just wanted to show you it because it's the first and most basic type of join. 121 00:06:50,870 --> 00:06:53,650 So don't make much of the fact that you can do it. 122 00:06:53,660 --> 00:06:55,160 It's just good to know that you can. 123 00:06:55,160 --> 00:07:01,000 But we're much more interested in basically whittling this down to this stuff that we care about. 124 00:07:01,010 --> 00:07:06,380 So the more maybe the most obvious and meaningful way would be to whittle it down so that we only see 125 00:07:06,380 --> 00:07:08,090 where there's actually overlap. 126 00:07:08,090 --> 00:07:13,400 So where the idea of the customer is the same as the customer ID. 127 00:07:14,840 --> 00:07:16,390 So this would be a match, right? 128 00:07:16,400 --> 00:07:18,440 This is an order placed by Boy George. 129 00:07:18,710 --> 00:07:23,660 But then none of these are actually placed by the customer there next to. 130 00:07:24,200 --> 00:07:31,130 But then we get down here and we have here's George Michael with an idea of two and he placed this order. 131 00:07:31,190 --> 00:07:33,110 So how do we whittle it down to that? 132 00:07:33,320 --> 00:07:35,060 And that's what we'll see in the next video. 133 00:07:35,150 --> 00:07:36,350 Sorry for the cliffhanger.