1 00:00:00,270 --> 00:00:00,900 All right. 2 00:00:00,900 --> 00:00:04,230 So there are three main types of joints we're going to cover. 3 00:00:04,230 --> 00:00:10,950 Ignoring the cross joint we briefly saw we're going to see inner joints, left joints and right joints. 4 00:00:10,950 --> 00:00:14,100 And this stuff can seem a little intimidating when you're starting out. 5 00:00:14,100 --> 00:00:18,540 But I hope by the end of this video you'll feel more comfortable, at least with inner joints. 6 00:00:18,870 --> 00:00:20,160 So I have a diagram here. 7 00:00:20,160 --> 00:00:21,420 It's not particularly attractive. 8 00:00:21,420 --> 00:00:26,100 It's hard to make these this overlapping area in the middle using my slide software. 9 00:00:26,430 --> 00:00:27,420 So it's not perfect. 10 00:00:27,420 --> 00:00:34,560 But what we have are two circles representing our two tables, and the middle is the overlap. 11 00:00:34,560 --> 00:00:37,800 That is what we would target using an inner join. 12 00:00:38,010 --> 00:00:42,720 So again, when we see a left join, which is coming up later, it's slightly different, right? 13 00:00:42,720 --> 00:00:45,090 We have the middle and the left where we have a right joint. 14 00:00:45,090 --> 00:00:47,130 It's the middle and the right, but we're not there. 15 00:00:47,130 --> 00:00:52,110 We're talking about inner joints only and this is the most intuitive join. 16 00:00:52,680 --> 00:01:00,720 If we think about our customers and our orders, where if I go back and we select star from customers 17 00:01:01,140 --> 00:01:08,970 and we select star from orders, we know that we have this customer ID 112253. 18 00:01:09,150 --> 00:01:14,730 And if I was looking at this as a human and I wanted to figure out who placed this order, well, I 19 00:01:14,730 --> 00:01:20,370 would look at this customer ID, then I'd come over here and find the customer with that ID right there, 20 00:01:20,370 --> 00:01:22,080 which is George Michael. 21 00:01:22,080 --> 00:01:22,890 And then I see. 22 00:01:22,890 --> 00:01:24,570 All right, George Michael placed this order. 23 00:01:24,570 --> 00:01:26,220 George Michael placed this order. 24 00:01:26,220 --> 00:01:28,050 We found the overlap. 25 00:01:28,050 --> 00:01:34,590 Right where the customer ID in this row matches the actual customer's ID in this table. 26 00:01:34,590 --> 00:01:40,380 And then we look at the first name or the last name so we can write a join to do that for us. 27 00:01:40,380 --> 00:01:42,990 We'll write a join that says select. 28 00:01:42,990 --> 00:01:49,770 I don't know, first name and last name alongside the order date in the amount, but only do this where 29 00:01:49,770 --> 00:01:55,320 the customer ID from the customer's table matches the ID from the orders table. 30 00:01:55,620 --> 00:01:57,720 So I'm going to write this index out first. 31 00:01:57,750 --> 00:01:59,220 Actually have a slide to show it. 32 00:01:59,880 --> 00:02:07,620 It looks like this select whatever we want from customers, but then we get some new syntax join and 33 00:02:07,620 --> 00:02:08,370 on. 34 00:02:09,000 --> 00:02:10,710 So let's just start with join. 35 00:02:10,710 --> 00:02:14,340 We have to specify what table we're joining customers with. 36 00:02:14,340 --> 00:02:17,040 So if we start with customers, we'll join orders. 37 00:02:17,040 --> 00:02:21,420 We also could go the other way around, but we'll start with customers join orders and then we tell 38 00:02:21,420 --> 00:02:23,220 it how to do the joining. 39 00:02:23,220 --> 00:02:32,310 We want to join on where the customer's ID is equal to the orders Table DOT, Customer ID, let's just 40 00:02:32,310 --> 00:02:39,990 type it out, start from scratch, so we'll select star from customers if that's what we start with. 41 00:02:41,460 --> 00:02:48,270 And then we add our join and the table that we're trying to join, which is orders. 42 00:02:48,600 --> 00:02:49,580 So we're halfway there. 43 00:02:49,590 --> 00:02:54,900 Now we have to add in the en portion saying how to do the actual joining. 44 00:02:54,990 --> 00:03:02,550 And we want to perform the join that I kind of described using my mouse where the customer ID from the 45 00:03:02,550 --> 00:03:08,040 orders table matches, the ID from the customer's table. 46 00:03:08,610 --> 00:03:16,440 And the way we do that is by referencing we can't just say ID equals ID or ID equals customer ID, we 47 00:03:16,440 --> 00:03:22,020 have to tell my SQL which table the ID comes from because now we have two tables. 48 00:03:22,020 --> 00:03:31,500 They both have an ID, right orders ID, customers ID, so we're going to say join on where the orders 49 00:03:31,740 --> 00:03:39,090 DOT customer ID column is equal to the customer's dot ID column. 50 00:03:39,960 --> 00:03:47,700 So where the customer ID column is equal to the customer's table ID column and that should do it for 51 00:03:47,700 --> 00:03:48,240 us. 52 00:03:48,600 --> 00:03:49,920 I'll run this query. 53 00:03:51,620 --> 00:03:53,780 And we said select star. 54 00:03:53,860 --> 00:03:54,040 Right. 55 00:03:54,080 --> 00:03:59,270 So we get everything, both IDs, we get first name, last name, email. 56 00:03:59,270 --> 00:04:04,370 So this on the left hand side ID, first name, last name, email is all from the customers table. 57 00:04:04,400 --> 00:04:10,280 On the right hand side, we have ID order date amount, customer ID that is all coming from the orders 58 00:04:10,280 --> 00:04:10,760 table. 59 00:04:10,760 --> 00:04:12,800 Now here's the most important part. 60 00:04:12,830 --> 00:04:19,329 Take a look at the ID for the customer and the customer ID they both match. 61 00:04:19,339 --> 00:04:27,020 So we are seeing who placed each order and what their email is and what their ID is alongside each order, 62 00:04:27,020 --> 00:04:28,730 date and order amount. 63 00:04:29,030 --> 00:04:30,680 But we might want to slim it down. 64 00:04:30,680 --> 00:04:33,110 This is more information than we probably want. 65 00:04:33,110 --> 00:04:41,450 We could say select first name, last name, and then maybe what is it? 66 00:04:41,450 --> 00:04:42,530 Date Order. 67 00:04:42,530 --> 00:04:43,010 Underscore. 68 00:04:43,010 --> 00:04:43,700 Date. 69 00:04:44,880 --> 00:04:45,860 And amount. 70 00:04:45,870 --> 00:04:49,080 Maybe those are the four things we might be most interested in. 71 00:04:49,500 --> 00:04:50,340 There we are. 72 00:04:50,550 --> 00:04:51,360 So we can see. 73 00:04:51,360 --> 00:04:51,720 All right. 74 00:04:51,720 --> 00:04:54,750 Boy, George ordered on whatever this is. 75 00:04:54,750 --> 00:04:58,340 February 10th, 2016, $99.99. 76 00:04:58,380 --> 00:05:01,920 He also ordered 2017 3550. 77 00:05:02,190 --> 00:05:04,890 So we just slimmed down the columns that we're selecting. 78 00:05:04,890 --> 00:05:06,390 But the joining is still happening. 79 00:05:06,390 --> 00:05:08,970 We just aren't looking at the actual IDs anymore. 80 00:05:09,060 --> 00:05:11,310 But it's still all happening behind the scenes. 81 00:05:11,880 --> 00:05:14,340 Now, what I want to show is we can also go the other direction. 82 00:05:14,340 --> 00:05:17,610 So we started with customers and we joined orders. 83 00:05:17,730 --> 00:05:19,770 I could have said select. 84 00:05:21,120 --> 00:05:31,380 Let's just do Starr from orders join customers on and then the order of this part doesn't matter. 85 00:05:31,380 --> 00:05:35,100 I could say customer's dot ID is equal to orders. 86 00:05:35,100 --> 00:05:36,780 DOT customer ID. 87 00:05:37,730 --> 00:05:39,110 And if I run this. 88 00:05:40,550 --> 00:05:47,440 We see the same information, but the order versus what's on left versus what's on right is different, 89 00:05:47,450 --> 00:05:47,810 Right? 90 00:05:47,810 --> 00:05:52,250 We see the order information first and then we see first name, last name, email. 91 00:05:52,310 --> 00:05:54,530 But it's the same information. 92 00:05:54,530 --> 00:05:56,350 We have six rows. 93 00:05:56,360 --> 00:05:57,960 We have six rows. 94 00:05:57,980 --> 00:06:03,290 So all that changed is the order that things are printed out, the order of those columns, which is 95 00:06:03,290 --> 00:06:05,870 confusing because we also have a table called orders. 96 00:06:06,830 --> 00:06:08,300 But I'm talking about this sequence. 97 00:06:08,300 --> 00:06:09,530 Let's call it the sequence. 98 00:06:09,530 --> 00:06:15,200 The sequence is different ID order, date amount versus here we started with ID, first name, last 99 00:06:15,200 --> 00:06:19,340 name, email, but the information is the same so we can join either way. 100 00:06:19,670 --> 00:06:26,360 But what matters the most is we use this join keyword to bring in information from another table and 101 00:06:26,360 --> 00:06:32,570 we have to specify how to connect the to what are the arrows going to be that are connecting one table 102 00:06:32,570 --> 00:06:33,350 to the other. 103 00:06:33,350 --> 00:06:40,640 And the answer is using the on clause we specify from the customer's table, take ID and whatever. 104 00:06:40,640 --> 00:06:42,590 That's the same as the orders table. 105 00:06:42,830 --> 00:06:46,880 Customer ID, We have a match now this is a useful join. 106 00:06:46,880 --> 00:06:49,700 I could just do whatever arbitrary joint I wanted to. 107 00:06:49,700 --> 00:06:50,750 I could do this. 108 00:06:50,750 --> 00:06:59,570 Select everything from orders where the customer ID, meaning the ID from the customer's table is the 109 00:06:59,570 --> 00:07:02,060 same as the ID from the orders table. 110 00:07:02,330 --> 00:07:03,440 Why would we do that? 111 00:07:03,440 --> 00:07:04,580 It doesn't make sense. 112 00:07:04,580 --> 00:07:06,380 There's no meaning to be garnered from that. 113 00:07:06,380 --> 00:07:09,740 But I'll just show you that there's nothing stopping us from it. 114 00:07:09,830 --> 00:07:18,740 So now what we've done is selected all of the rows from order where the corresponding row from customer 115 00:07:18,740 --> 00:07:20,210 has the same ID. 116 00:07:20,240 --> 00:07:25,250 So this customer, Boy George has ID of one, This order has ID of one. 117 00:07:25,250 --> 00:07:28,370 This customer, Bette Davis, has ID of five. 118 00:07:28,370 --> 00:07:33,260 Well, she's matched up with this one that has ID of five this order. 119 00:07:33,380 --> 00:07:36,320 It doesn't make sense to do that, but we can do it. 120 00:07:36,440 --> 00:07:41,900 Generally though, we would actually make a join that told us something that connected information that 121 00:07:41,900 --> 00:07:48,530 is not arbitrarily connected by the order of their IDs, but rather by a foreign key. 122 00:07:48,530 --> 00:07:50,870 And remember that this is an inner join. 123 00:07:50,870 --> 00:07:56,900 We are getting the overlap between customers and orders or orders and customers. 124 00:07:57,230 --> 00:08:04,790 Technically, the inner here is implicit, but I can write it out if I wanted to explicitly use an inner 125 00:08:04,790 --> 00:08:05,390 join. 126 00:08:05,390 --> 00:08:06,500 It's just the default. 127 00:08:06,500 --> 00:08:08,960 If you say join is going to be an inner join. 128 00:08:08,960 --> 00:08:13,700 But once we learn left and right, we have to specify left and right because those are not the default. 129 00:08:14,000 --> 00:08:17,930 So even though we're not writing inner, it is truly an inner join. 130 00:08:17,930 --> 00:08:22,910 So I'm going to get rid of this arbitrary join that makes no sense and leave you with this one here. 131 00:08:22,910 --> 00:08:29,030 That is something we would actually do, although more often than not, I would narrow down what I'm 132 00:08:29,030 --> 00:08:32,990 trying to select because when you join two tables together, these are very small tables. 133 00:08:32,990 --> 00:08:35,299 They have four columns or three columns each. 134 00:08:35,299 --> 00:08:40,880 It already adds up to quite a lot of information, but with real complicated tables, we might have 135 00:08:40,880 --> 00:08:42,049 dozens and dozens. 136 00:08:42,049 --> 00:08:48,290 So when we join them together, we often will slim down what we select something like this. 137 00:08:48,830 --> 00:08:49,160 All right. 138 00:08:49,160 --> 00:08:50,840 So that's our first example of a join. 139 00:08:50,840 --> 00:08:52,040 We have more to cover.