1 00:00:00,120 --> 00:00:00,540 All right. 2 00:00:00,540 --> 00:00:04,560 So we're going to learn our second type of join, which is the left join. 3 00:00:04,710 --> 00:00:09,540 I'd say this is nowhere near as commonly used as an inner join, but it's still important to know about. 4 00:00:09,630 --> 00:00:13,980 And the good news is, once you understand the left join, it makes understanding right joints very 5 00:00:13,980 --> 00:00:14,580 easy. 6 00:00:15,060 --> 00:00:20,970 So if you take a look at my ugly diagram, I've got two circles representing two tables and presumably 7 00:00:20,970 --> 00:00:22,520 they have something in common. 8 00:00:22,530 --> 00:00:24,120 There's some foreign key. 9 00:00:24,630 --> 00:00:31,740 And where we had an inner join which only selected the overlapping rows where there was a match between 10 00:00:31,740 --> 00:00:35,150 customers and orders here with a left join. 11 00:00:35,160 --> 00:00:42,390 If we have customers as our left table and orders as our right table, we are going to take every single 12 00:00:42,390 --> 00:00:49,740 row from the left side, every row from customers, and then if there's any corresponding order, data 13 00:00:49,740 --> 00:00:51,120 will also join that. 14 00:00:51,120 --> 00:00:55,350 But if not, we'll have a bunch of nulls and I'll show you what that looks like. 15 00:00:55,410 --> 00:00:57,300 So keep this visual in mind. 16 00:00:57,310 --> 00:01:01,260 Everything from the left side and then any overlap from the right side. 17 00:01:01,860 --> 00:01:05,220 So let's do another join here. 18 00:01:05,250 --> 00:01:06,450 Select. 19 00:01:06,990 --> 00:01:08,430 Let's just do first name. 20 00:01:09,180 --> 00:01:10,080 Last name. 21 00:01:10,850 --> 00:01:15,110 Order, date and amount from customers. 22 00:01:15,110 --> 00:01:21,710 And when I say from customers, that now is going to be the left side, just like here, customers on 23 00:01:21,710 --> 00:01:22,070 the left. 24 00:01:22,070 --> 00:01:26,810 If I write customers first, then I join orders that would be on the right. 25 00:01:27,500 --> 00:01:34,400 So I'll do select all that stuff from customers and then instead of just join or inter join, which 26 00:01:34,400 --> 00:01:38,150 is equivalent, I'm going to say left join. 27 00:01:38,240 --> 00:01:39,530 And then it's the same thing. 28 00:01:39,530 --> 00:01:46,610 I have to specify the table, the orders table on, and then I'm going to do the same exact thing. 29 00:01:46,640 --> 00:01:47,750 This hasn't changed. 30 00:01:47,750 --> 00:01:55,610 I still want to join where the orders table customer ID matches the actual ID from the customer's table. 31 00:01:55,610 --> 00:02:00,200 I need that overlap there, regardless of the type of joint I'm doing. 32 00:02:00,200 --> 00:02:04,190 The thing I'm joining on in our table is not going to change here. 33 00:02:04,190 --> 00:02:09,830 We have one foreign key and we want to match it up with the ID from customers. 34 00:02:09,830 --> 00:02:15,920 So anyway left join orders on and then we can do orders DOT customer ID wherever that customer ID from 35 00:02:15,920 --> 00:02:22,670 the orders table matches customer's ID, So put a semicolon there. 36 00:02:22,670 --> 00:02:31,430 When I execute this, you'll see that we get a different result if I just go back to plain old join 37 00:02:31,430 --> 00:02:32,420 in or join. 38 00:02:32,690 --> 00:02:33,920 Or just join. 39 00:02:34,860 --> 00:02:39,720 We see we've got, what, five rows here versus seven rows here. 40 00:02:39,990 --> 00:02:43,470 So on both of these examples, we have a left side and a right side. 41 00:02:43,470 --> 00:02:45,930 The left side is customers. 42 00:02:45,930 --> 00:02:47,940 The right side is orders. 43 00:02:48,060 --> 00:02:53,340 But remember this difference in an inner join, we only get the overlap in a left join. 44 00:02:53,340 --> 00:02:55,770 We take every single row from the left side. 45 00:02:55,890 --> 00:02:57,420 So this is our left join. 46 00:02:57,420 --> 00:02:59,390 Every customer is represented here. 47 00:02:59,400 --> 00:03:04,620 Boy George, George Michael, David Bowie, Blue Steel and Bette Davis. 48 00:03:04,800 --> 00:03:11,220 And then if they happen to have any overlap from the right side from orders, then we see that information. 49 00:03:11,220 --> 00:03:13,620 Boy George placed an order here. 50 00:03:13,770 --> 00:03:15,300 Boy George had this order. 51 00:03:15,330 --> 00:03:15,720 George. 52 00:03:15,720 --> 00:03:16,870 Michael, This order. 53 00:03:16,890 --> 00:03:17,220 George. 54 00:03:17,220 --> 00:03:18,600 Michael, Also this order. 55 00:03:18,600 --> 00:03:22,620 But then David Bowie has no orders in our database. 56 00:03:22,830 --> 00:03:25,620 Blue Steel has no orders in our database. 57 00:03:25,620 --> 00:03:27,510 So it's a left join. 58 00:03:27,510 --> 00:03:31,080 We get those names, we get the information from customers. 59 00:03:31,080 --> 00:03:36,480 There's just no corresponding information from orders, so we get null values instead. 60 00:03:36,840 --> 00:03:42,480 Now, if we compare that to the inner join, we only get that intersection where there is an overlap, 61 00:03:42,630 --> 00:03:43,410 a left join. 62 00:03:43,410 --> 00:03:45,930 We take all information from customers. 63 00:03:45,930 --> 00:03:50,880 Every row from customers is here and any corresponding order information is filled in. 64 00:03:50,880 --> 00:03:53,610 But if there isn't any, we just get nulls. 65 00:03:54,420 --> 00:03:56,040 So that's a left join. 66 00:03:56,040 --> 00:03:57,440 Why would we do this? 67 00:03:57,450 --> 00:04:00,870 What is the purpose of a left join compared to an inner join? 68 00:04:01,050 --> 00:04:04,770 Well, sometimes this can reveal important information. 69 00:04:04,770 --> 00:04:12,380 If I wanted to know do we have any users who have never placed an order who are completely inactive? 70 00:04:12,390 --> 00:04:14,460 This would be one way to find that out. 71 00:04:14,550 --> 00:04:18,089 Really, the main way to find it out an inner join is not going to help me. 72 00:04:18,420 --> 00:04:22,920 This only shows users who have placed orders with this. 73 00:04:22,920 --> 00:04:26,070 If I only looked at customers, that's not going to help me either. 74 00:04:26,070 --> 00:04:28,050 I keep saying users, but I mean customers. 75 00:04:28,050 --> 00:04:32,610 If we select star from customers, that tells me nothing about who's placed in order. 76 00:04:32,610 --> 00:04:41,190 So really the only way to find out is to overlap customers or join customers with orders and see where 77 00:04:41,190 --> 00:04:44,490 there is no corresponding order for a customer. 78 00:04:44,910 --> 00:04:49,260 So that shows us David Bowie and Blue Steel have no orders. 79 00:04:49,260 --> 00:04:54,620 So then I could do something like calculate how many nulls there are or Group VI things. 80 00:04:54,630 --> 00:04:59,490 There's other stuff that I could do, but it's just important that, you know, there are real use cases 81 00:04:59,490 --> 00:04:59,880 for this. 82 00:04:59,880 --> 00:05:05,130 There are times we want to know where there is no match up from the right side. 83 00:05:05,340 --> 00:05:06,510 Not always. 84 00:05:06,540 --> 00:05:11,520 Often inner joints are more useful, but that doesn't mean left joints have no purpose at all. 85 00:05:11,520 --> 00:05:17,820 They come up and again, going back to this diagram, we get every single row from the left table and 86 00:05:17,820 --> 00:05:23,310 then the right table will fill in any places where there's a match, where there's overlap. 87 00:05:23,790 --> 00:05:31,470 Now, one thing that's a little confusing if I switch the order here, so if I said select from orders, 88 00:05:31,770 --> 00:05:33,270 that would be my left side. 89 00:05:33,270 --> 00:05:42,210 Join customers and then I'm going to reorder these properties or these columns so that it looks left 90 00:05:42,210 --> 00:05:43,050 to right. 91 00:05:43,620 --> 00:05:46,740 So the order doesn't technically matter here. 92 00:05:46,860 --> 00:05:49,590 This is what makes a left and this makes the right. 93 00:05:49,590 --> 00:05:54,780 But I've put these on the left, the order columns, and I've put the customer columns in the right. 94 00:05:54,840 --> 00:05:57,240 But my point is, if I run this. 95 00:05:58,780 --> 00:06:00,940 We get the same thing as an inner join. 96 00:06:01,600 --> 00:06:05,470 It's the same result that we got when we did our inner join right here. 97 00:06:06,070 --> 00:06:07,000 Why is that? 98 00:06:07,000 --> 00:06:11,690 I mean, excluding the fact that I have order, date and amount first, it's the same matches. 99 00:06:11,710 --> 00:06:18,610 Well, if you think of our data and how it works, if we select star from orders, we don't have any 100 00:06:18,610 --> 00:06:23,260 orders that don't have a customer ID, everything from customer ID. 101 00:06:23,260 --> 00:06:28,660 If this is our starting point, this is our left side, and then we match anything from customers. 102 00:06:28,660 --> 00:06:30,070 Well, they all have a match. 103 00:06:30,070 --> 00:06:31,780 There's a complete overlap. 104 00:06:31,780 --> 00:06:37,030 So the left side is the same in this case as the middle. 105 00:06:37,330 --> 00:06:40,330 So the end result is the same as our inner join. 106 00:06:40,330 --> 00:06:46,330 But if we had any orders that did not have a customer ID, then the end result would be different. 107 00:06:46,330 --> 00:06:47,560 But in this case it's not. 108 00:06:47,560 --> 00:06:49,560 So that's a little confusing. 109 00:06:49,570 --> 00:06:53,560 It's just the way that our data is set up, and that's common with one to many. 110 00:06:54,130 --> 00:07:00,220 We'll have customers who don't have orders, but we definitely won't have orders that have no customer 111 00:07:00,220 --> 00:07:01,660 because how would that make sense? 112 00:07:01,810 --> 00:07:02,620 All right. 113 00:07:02,620 --> 00:07:04,210 I hope you're hanging in there. 114 00:07:04,210 --> 00:07:04,900 It's a lot.