1 00:00:00,180 --> 00:00:00,770 Okay. 2 00:00:00,780 --> 00:00:07,170 So in the last video we saw our first join, our first example, which is actually something known as 3 00:00:07,170 --> 00:00:11,640 an implicit join, which you'll see for a moment why it's called implicit. 4 00:00:12,270 --> 00:00:17,430 And it's also in particular it is a cross join useless basically. 5 00:00:17,880 --> 00:00:21,810 So it just think of it as multiplying our data cross multiplying. 6 00:00:21,810 --> 00:00:24,510 It takes every possible combination so we have. 7 00:00:24,510 --> 00:00:26,880 Boy George Well, I don't need to go over that again. 8 00:00:26,880 --> 00:00:27,870 I'll bore you to death. 9 00:00:28,440 --> 00:00:32,310 So now we want to focus on whittling it down to what's actually relevant. 10 00:00:32,310 --> 00:00:38,640 I want to see the name of the name, first name, last name and email of every user who actually placed 11 00:00:38,640 --> 00:00:45,390 an order next to that order, not just every arbitrary order I want the ones that they placed. 12 00:00:45,960 --> 00:00:49,800 And so we're getting sort of close if we have all of it next to each other. 13 00:00:49,800 --> 00:00:58,200 Now, we just want to only select the rows where what, what condition is true, where our user ID is 14 00:00:58,200 --> 00:01:00,540 equal to the order customer ID. 15 00:01:00,930 --> 00:01:06,330 So it's just like any other kind of select we've done where we just can add in aware. 16 00:01:06,330 --> 00:01:14,220 So it will be a select star from customers comma orders, where and where. 17 00:01:14,250 --> 00:01:15,360 What is true? 18 00:01:16,020 --> 00:01:25,200 Where ID from the customer is equal to customer ID so we could do this where customer equals customer 19 00:01:25,200 --> 00:01:28,980 underscore ID, but there's a problem and maybe you've identified it. 20 00:01:29,190 --> 00:01:30,900 We have two IDs. 21 00:01:32,070 --> 00:01:36,450 Remember, we're just taking the customers table and the orders table and putting them together. 22 00:01:36,450 --> 00:01:40,410 So we have ID from customers and ID from orders. 23 00:01:41,390 --> 00:01:43,370 So what does this refer to? 24 00:01:44,500 --> 00:01:45,460 That's a problem. 25 00:01:45,490 --> 00:01:47,140 Fortunately, there's a workaround. 26 00:01:47,140 --> 00:01:48,370 It's very, very simple. 27 00:01:48,910 --> 00:01:52,270 All we have to do is pretend the name of the tables. 28 00:01:52,270 --> 00:01:56,980 So this should be our customer's ID, right? 29 00:01:59,310 --> 00:02:00,540 It's coming from here. 30 00:02:01,050 --> 00:02:08,000 And just to make that clear what's happening, we're saying where the customers table dot ID so we're 31 00:02:08,009 --> 00:02:15,900 this is equal to customer ID now customer underscore ID and I realize there's a lot of saying customer 32 00:02:15,900 --> 00:02:19,320 and ID over and over, but these are different, right? 33 00:02:19,320 --> 00:02:27,000 This is the customer's table ID that's referring to this customer's ID that dot means in the customer's 34 00:02:27,000 --> 00:02:35,130 table and this is customer underscore ID, which is referring to this right here. 35 00:02:35,520 --> 00:02:42,660 So we don't have to say orders dot customer ID, but it's a good convention to follow to do that. 36 00:02:42,660 --> 00:02:49,050 Just to whenever you're working with two or more tables to be explicit about which table with which 37 00:02:49,050 --> 00:02:52,470 row excuse me, which column belongs to which table. 38 00:02:53,340 --> 00:02:53,770 Okay. 39 00:02:53,940 --> 00:02:55,620 So we have this line. 40 00:02:56,430 --> 00:02:57,570 Let's copy it over. 41 00:02:57,780 --> 00:03:05,520 So what this will do is select or it's going to do this cross join, but only where the customer's ID 42 00:03:05,850 --> 00:03:10,560 customer table ID is equal to the customer ID of the order. 43 00:03:10,590 --> 00:03:12,210 So now if we do this. 44 00:03:13,420 --> 00:03:14,740 Take a look at what we get. 45 00:03:15,250 --> 00:03:23,650 We have five orders, so we get these five different rows and we have the corresponding customer who 46 00:03:23,650 --> 00:03:24,460 placed it. 47 00:03:24,580 --> 00:03:27,970 So Boy George placed this order for $99. 48 00:03:28,090 --> 00:03:30,480 Boy George placed another order for $35. 49 00:03:30,490 --> 00:03:36,610 George Michael plays these two orders and Bette Davis placed this one order for $150. 50 00:03:37,090 --> 00:03:42,310 So you can see we have customer ID over here and it's matching the customer's ID. 51 00:03:43,150 --> 00:03:50,560 And of course, we could condense this a bit, so maybe we don't want Star, maybe we want first name 52 00:03:50,650 --> 00:03:55,540 and last name and then order, date and amount and that's it. 53 00:03:56,480 --> 00:03:57,530 Just like that. 54 00:03:58,070 --> 00:03:59,570 So let's try it. 55 00:04:01,580 --> 00:04:04,700 And now we get a more condensed table that has that information. 56 00:04:04,910 --> 00:04:05,930 Boy George. 57 00:04:05,930 --> 00:04:06,860 Boy George. 58 00:04:07,130 --> 00:04:08,090 Two orders. 59 00:04:08,120 --> 00:04:09,200 George Michael. 60 00:04:09,200 --> 00:04:09,830 One order. 61 00:04:09,860 --> 00:04:10,460 George Michael. 62 00:04:10,460 --> 00:04:11,180 Another order. 63 00:04:11,180 --> 00:04:12,650 Bette Davis Another order. 64 00:04:13,190 --> 00:04:21,140 So what we've just done there, we have joined them together using what's known as an implicit inner 65 00:04:21,140 --> 00:04:21,560 join. 66 00:04:21,560 --> 00:04:23,060 So I'll write that here. 67 00:04:27,100 --> 00:04:27,630 Okay. 68 00:04:27,880 --> 00:04:29,860 And that's what we did up here as well. 69 00:04:29,860 --> 00:04:35,470 So this is just a more refined one because we're not selecting Star, we're only selecting a couple 70 00:04:35,470 --> 00:04:39,940 of things, but we've joined them where they match, if that makes sense. 71 00:04:40,990 --> 00:04:42,550 So I have a simple diagram. 72 00:04:42,550 --> 00:04:45,790 It's very, very ugly, but a simple diagram to show you. 73 00:04:45,790 --> 00:04:46,990 And I'm sorry it's so ugly. 74 00:04:46,990 --> 00:04:48,400 It was very difficult. 75 00:04:49,840 --> 00:04:52,540 Filling in this shape here is not a circle. 76 00:04:52,540 --> 00:04:56,440 And the slide tool I use doesn't let you draw weird shapes. 77 00:04:56,440 --> 00:04:57,670 You have to do circles. 78 00:04:57,670 --> 00:04:58,720 So I'm sorry. 79 00:04:58,720 --> 00:04:59,470 I really am. 80 00:04:59,470 --> 00:05:02,050 But what's happening here is that we have two tables, right? 81 00:05:02,110 --> 00:05:03,370 Customers and orders. 82 00:05:03,430 --> 00:05:09,670 And what we did at first was select everything, combine them and just take everything together. 83 00:05:09,670 --> 00:05:15,580 But all that we want to do with an inner join is take the inside where they overlap. 84 00:05:16,240 --> 00:05:17,380 And that's what we did. 85 00:05:17,380 --> 00:05:20,350 We went from this monstrosity. 86 00:05:21,380 --> 00:05:28,150 And we will down to where the customer's ID is equal to customer ID in the orders table. 87 00:05:28,160 --> 00:05:30,500 And we get this right here. 88 00:05:31,710 --> 00:05:32,140 Okay. 89 00:05:32,490 --> 00:05:37,980 Now, I said this is implicit and that's because there's an actual explicit syntax. 90 00:05:37,980 --> 00:05:40,020 There's a better way of doing this. 91 00:05:40,020 --> 00:05:43,140 And when I say better, it really means that it's more conventional. 92 00:05:43,470 --> 00:05:50,580 If you're not familiar with that, it means that it's think of it as like an unwritten rule that developers 93 00:05:50,580 --> 00:05:51,240 follow. 94 00:05:51,270 --> 00:05:52,320 They think it's cleaner. 95 00:05:52,320 --> 00:05:55,020 It's more easily understood. 96 00:05:55,320 --> 00:05:57,480 To do it this way, which I'm about to show you. 97 00:05:57,480 --> 00:05:58,740 So I'm going to write. 98 00:05:59,190 --> 00:06:00,300 Oh, what did I do? 99 00:06:00,810 --> 00:06:01,490 Here we go. 100 00:06:01,500 --> 00:06:06,630 I'm going to write a comment, explicit inner join. 101 00:06:06,960 --> 00:06:10,770 And this will be the first time we see a new keyword called Join. 102 00:06:11,430 --> 00:06:14,130 So we're going to do our same select star. 103 00:06:14,130 --> 00:06:16,680 We'll start with this one from customers. 104 00:06:18,330 --> 00:06:21,210 However, we're not just going to do common orders. 105 00:06:21,210 --> 00:06:27,270 We're going to select start from customers and then we're going to add join orders. 106 00:06:27,270 --> 00:06:28,500 So we have some new syntax. 107 00:06:28,500 --> 00:06:36,090 I'm just going to type it all first and then we're going to tell it where we're going to join on customers. 108 00:06:36,090 --> 00:06:39,510 Dot ID equals orders. 109 00:06:39,510 --> 00:06:41,640 Dot Customer ID. 110 00:06:43,020 --> 00:06:43,650 Okay. 111 00:06:44,010 --> 00:06:51,060 So if I copy this and I run this, it does the exact same thing. 112 00:06:51,600 --> 00:06:55,590 And if we want to, of course, we could refine this more, just like we did earlier. 113 00:06:55,590 --> 00:07:03,180 So instead of selecting Star, let's do the same thing where we select first name, last name, order, 114 00:07:03,180 --> 00:07:04,500 date and amount. 115 00:07:05,640 --> 00:07:06,240 Okay. 116 00:07:06,660 --> 00:07:07,350 Copy this. 117 00:07:07,350 --> 00:07:08,010 Over. 118 00:07:10,060 --> 00:07:13,960 And now we get this the same table just to prove to you that it's the same. 119 00:07:15,820 --> 00:07:16,420 There you go. 120 00:07:16,450 --> 00:07:20,350 Can't even see a difference when I just hit enter and it looks like the same thing is here. 121 00:07:21,160 --> 00:07:27,460 So this is still an inner join, but this time it's explicit and it's really only explicit because we're 122 00:07:27,460 --> 00:07:28,690 writing the word join. 123 00:07:28,960 --> 00:07:36,040 So when we say join order, so we say from customers, join orders on this join condition. 124 00:07:36,040 --> 00:07:38,530 So take the left and the right. 125 00:07:38,530 --> 00:07:42,850 So which customers in orders these two circles basically and join them. 126 00:07:42,850 --> 00:07:50,440 Create the union table where customers ID is equal to orders customer ID. 127 00:07:50,470 --> 00:07:55,120 So where the ID from here matches the customer ID from here? 128 00:07:55,150 --> 00:07:58,330 Take that overlap and make this table for us. 129 00:07:59,410 --> 00:08:06,670 So I know it's a lot that I'm talking about here and the term inner join and explicit inner join, implicit 130 00:08:06,670 --> 00:08:07,270 inner join. 131 00:08:07,270 --> 00:08:12,160 And we're going to next talking about right joints and left join it's can't be overwhelming. 132 00:08:12,160 --> 00:08:19,300 So all I want you to focus on is the key idea here that we are joining data together based off of a 133 00:08:19,300 --> 00:08:20,080 condition. 134 00:08:20,710 --> 00:08:21,250 Right. 135 00:08:21,250 --> 00:08:25,540 And we could join it off of some meaningless condition to just to show you. 136 00:08:26,200 --> 00:08:32,049 So if we go back to this this really ugly cross, join. 137 00:08:34,330 --> 00:08:36,820 Where it takes everything and joins it together. 138 00:08:38,150 --> 00:08:40,909 So if I wanted to. 139 00:08:40,940 --> 00:08:46,430 There's no meaning to be garnered from this, but I could join it where the two IDs are the same. 140 00:08:46,430 --> 00:08:51,860 So not customer ID, but I could say where customers ID is the same as orders ID. 141 00:08:51,860 --> 00:09:00,050 So let me just show you that now and I'll just comment this as arbitrary. 142 00:09:00,440 --> 00:09:01,310 Join. 143 00:09:02,420 --> 00:09:03,530 Don't do this. 144 00:09:04,070 --> 00:09:05,390 But we could do this. 145 00:09:05,390 --> 00:09:15,320 We could select star from customers join orders on and I'll just say we're customers ID equals orders 146 00:09:15,560 --> 00:09:16,160 ID. 147 00:09:17,750 --> 00:09:19,880 There's nothing stopping me from doing this. 148 00:09:20,180 --> 00:09:25,760 And as you can see, I just end up with this new table that has matching IDs. 149 00:09:25,760 --> 00:09:30,320 So we get let's take an example like George Michael. 150 00:09:30,800 --> 00:09:33,230 So George Michael is now matched. 151 00:09:33,230 --> 00:09:34,040 He has an idea of two. 152 00:09:34,070 --> 00:09:36,290 He is matched with the order of ID two. 153 00:09:36,560 --> 00:09:41,240 It doesn't matter that it was placed by customer with ID of one, which is Boy George. 154 00:09:41,900 --> 00:09:46,060 So I'm saying that we can just join things on arbitrary conditions. 155 00:09:46,070 --> 00:09:52,100 However, you're typically what goes in here is you're filling in a foreign key, matching a primary 156 00:09:52,100 --> 00:09:53,300 key in another table. 157 00:09:53,300 --> 00:09:59,720 So primary key is a customer's ID, the foreign key is the customer underscore ID in the orders table. 158 00:10:00,440 --> 00:10:05,360 And then the last thing that I'll wrap up with here is, does the order matter? 159 00:10:06,020 --> 00:10:08,420 So if we go back to our cross, join. 160 00:10:10,230 --> 00:10:11,370 What if I reverse the order? 161 00:10:11,370 --> 00:10:17,550 So I started with customers, comma, orders, and so we get customers here and then orders tacked on 162 00:10:17,550 --> 00:10:18,390 on the right. 163 00:10:18,690 --> 00:10:20,010 So what if I switch it? 164 00:10:22,090 --> 00:10:24,100 Orders to customers. 165 00:10:26,730 --> 00:10:30,960 And you can see well, it looks very similar except well, it doesn't look similar. 166 00:10:30,960 --> 00:10:33,600 We have the same data, but it's been switched. 167 00:10:33,600 --> 00:10:40,350 So we start with every order first and then one customer tacked on to the end and then every order again 168 00:10:40,350 --> 00:10:41,700 and then the same customer. 169 00:10:41,700 --> 00:10:46,530 So the order does matter in a sense as to how your data is presented to you. 170 00:10:46,530 --> 00:10:53,070 But it doesn't matter in in the case of a cross join or in the case of an inner join, it won't actually 171 00:10:53,070 --> 00:10:54,300 affect the result. 172 00:10:54,330 --> 00:10:57,270 It just affects what is printed out, what it looks like. 173 00:10:57,270 --> 00:11:01,530 So let me show you, if we take this same we've got a lot of them going on here. 174 00:11:01,530 --> 00:11:03,210 Let's take this same one here. 175 00:11:03,360 --> 00:11:09,270 This is kind of the most useful join we had right where we had the name and the order date and the amount 176 00:11:10,320 --> 00:11:15,480 if I switch and I'm just going to duplicate it so that we have the original as well. 177 00:11:15,480 --> 00:11:21,150 If I say from orders, join customers. 178 00:11:22,240 --> 00:11:24,670 On and we can leave this either way. 179 00:11:24,670 --> 00:11:26,020 But let's say I do that. 180 00:11:26,020 --> 00:11:30,070 So I'm selecting from orders with joining customers onto it. 181 00:11:32,360 --> 00:11:33,290 And I hit enter. 182 00:11:33,470 --> 00:11:34,760 Nothing changes. 183 00:11:35,030 --> 00:11:40,370 And that's because we are picking our data individually from up here. 184 00:11:40,400 --> 00:11:42,110 Now, if I just did a star. 185 00:11:44,650 --> 00:11:47,880 It would look a little different because you can see we're actually getting order first. 186 00:11:47,890 --> 00:11:51,550 So order, ID, order, date, amount and then customer. 187 00:11:52,090 --> 00:11:53,620 And if I switch that. 188 00:11:57,110 --> 00:11:58,520 It would be the other way around. 189 00:11:58,520 --> 00:11:59,880 That doesn't really matter. 190 00:11:59,900 --> 00:12:00,920 It's just. 191 00:12:00,920 --> 00:12:02,720 Oh, orders. 192 00:12:04,640 --> 00:12:07,370 But you can see now we get customers and then orders. 193 00:12:08,120 --> 00:12:08,660 All right. 194 00:12:08,660 --> 00:12:16,220 So this has been long and a little difficult, I imagine, especially some of the syntax or keeping 195 00:12:16,220 --> 00:12:17,060 the jargon. 196 00:12:17,450 --> 00:12:23,630 What I do want to say is that basically when you're joining, things don't do implicit points, which 197 00:12:23,630 --> 00:12:24,650 is what we have here. 198 00:12:24,680 --> 00:12:27,740 It's better to do it explicitly this way. 199 00:12:27,890 --> 00:12:35,060 So something like this or this where we have select something from a table, join on another table on 200 00:12:35,060 --> 00:12:35,930 a condition. 201 00:12:37,700 --> 00:12:40,760 So that is kind of the bread and butter of joints doing an inner join. 202 00:12:40,760 --> 00:12:44,900 But I'm going to show you a couple of other examples that will be up next.