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.

