1
00:00:00,240 --> 00:00:00,720
All right.

2
00:00:00,720 --> 00:00:02,310
So we have our two tables set up.

3
00:00:02,310 --> 00:00:03,900
We have customers, we have orders.

4
00:00:03,900 --> 00:00:10,470
We've got a primary key in each and then a foreign key customer ID references the customer's table ID

5
00:00:10,560 --> 00:00:11,860
inside of orders.

6
00:00:11,880 --> 00:00:15,600
Now, what I want to do is show you how we can work with these tables together.

7
00:00:15,750 --> 00:00:17,580
And we're going to start simple.

8
00:00:17,910 --> 00:00:24,390
I want to write a query that selects all of the orders placed by Boy George.

9
00:00:24,810 --> 00:00:26,910
So Boy George right there, let's say.

10
00:00:26,910 --> 00:00:33,150
I don't know the ID, I don't know Boy George's ID, So if you think you remember it, I mean, it's

11
00:00:33,150 --> 00:00:35,040
only one, two, three, four or five.

12
00:00:35,070 --> 00:00:36,600
I'm pretty sure it's one or two.

13
00:00:36,600 --> 00:00:40,830
But let's ignore the fact that we know it because it's such a simple data set.

14
00:00:40,830 --> 00:00:43,020
We can memorize it, but let's pretend we don't.

15
00:00:43,560 --> 00:00:48,030
Well, there's a couple of options I'm going to show you the simplest, most naive one first, which

16
00:00:48,030 --> 00:00:49,860
is to write two separate queries.

17
00:00:49,860 --> 00:00:56,910
Basically, we're going to try and find the ID of Boy George from the table customers first, and then

18
00:00:56,910 --> 00:01:04,590
after that we'll take that ID and try and find the orders that have that as their customer ID So that

19
00:01:04,590 --> 00:01:06,750
would look something like this.

20
00:01:07,710 --> 00:01:16,590
Our first query would be select and we could do select ID from customers.

21
00:01:19,140 --> 00:01:20,100
Where.

22
00:01:20,490 --> 00:01:24,090
And then we can do let's just do it based off of last name.

23
00:01:24,450 --> 00:01:27,660
Technically, of course, we could have multiple people at the last name of George.

24
00:01:27,660 --> 00:01:32,310
But just to keep our logic short, I'll say where last name is exactly equal to George.

25
00:01:32,310 --> 00:01:37,080
I won't worry about first name, although it probably would be more unique to say where first name is

26
00:01:37,080 --> 00:01:43,080
Boy, But we'll leave that alone so I could run this query first and stay with me here.

27
00:01:43,080 --> 00:01:49,290
This is the basic naive version where I run that and we get back an ID of one.

28
00:01:49,560 --> 00:01:54,210
All right, So then I can take that number one and go select.

29
00:01:54,480 --> 00:01:57,570
Let's just select star from orders.

30
00:01:57,720 --> 00:02:00,420
Where and then what am I looking for?

31
00:02:00,420 --> 00:02:05,910
Where ID is equal to one because we just found out Boy George's ID is one.

32
00:02:05,910 --> 00:02:12,750
So select orders where customer ID is one and that can be a little annoying.

33
00:02:12,750 --> 00:02:16,200
You can mess that up easily because we've got so many IDs, right?

34
00:02:16,200 --> 00:02:21,810
We have the ID on the customer's table, the actual ID on the order table, and the customer ID on the

35
00:02:21,810 --> 00:02:22,650
orders table.

36
00:02:23,010 --> 00:02:30,900
So I want to say where the customer ID in this order's table is set to one which is Boy George's ID,

37
00:02:30,930 --> 00:02:32,070
We just found that out.

38
00:02:32,190 --> 00:02:38,250
So then I would run this OC and these are the orders belonging to Boy George and I can do whatever I

39
00:02:38,250 --> 00:02:38,400
want.

40
00:02:38,400 --> 00:02:45,180
I can sum them up, I can average whatever, but this is clunky and this is only really going to work

41
00:02:45,180 --> 00:02:47,310
if I am looking for one customer.

42
00:02:47,310 --> 00:02:52,830
But what if I wanted to see every single customer next to their orders?

43
00:02:52,830 --> 00:03:00,570
So something like let's just say we see the order date or the order amount and then I want to see the

44
00:03:00,570 --> 00:03:02,790
first name right next to one another.

45
00:03:02,790 --> 00:03:10,200
So we would see like 1050, and then next to that we'd see George and then we see 450.

46
00:03:10,200 --> 00:03:14,040
And next to that we see whoever else we have in there.

47
00:03:14,820 --> 00:03:18,690
I guess if this is first name, this would be boy and this would be David.

48
00:03:18,840 --> 00:03:23,070
But my point is, what if I wanted to see all that information together?

49
00:03:23,460 --> 00:03:25,020
This is not going to get me there.

50
00:03:25,020 --> 00:03:29,370
This will work to get me information about one particular user.

51
00:03:29,700 --> 00:03:31,320
And there is another option.

52
00:03:31,320 --> 00:03:34,410
Instead of doing two separate queries, I could use a sub query.

53
00:03:34,410 --> 00:03:35,820
If you think back to how that works.

54
00:03:35,820 --> 00:03:42,390
Where we use parentheses, I could combine these two and say select ID from customers where the last

55
00:03:42,390 --> 00:03:46,320
name is George and use that as a subquery.

56
00:03:46,320 --> 00:03:48,000
So I would do something like this.

57
00:03:48,000 --> 00:03:56,130
Select star from orders where the customer ID is equal to the result of this other query.

58
00:03:56,780 --> 00:04:04,160
So when we have those perrins, my SCL will run this first and that's going to evaluate to a number

59
00:04:04,160 --> 00:04:05,030
which we've already seen.

60
00:04:05,030 --> 00:04:08,180
It turns into one and then this query runs.

61
00:04:08,450 --> 00:04:10,490
So this is another way of, of running it.

62
00:04:10,490 --> 00:04:16,040
That's or of writing it that is more succinct and we can run it in one step and we should still get

63
00:04:16,040 --> 00:04:21,640
the same two orders that have a customer ID of one, which is Boy George's customer ID.

64
00:04:21,680 --> 00:04:22,730
So there's that.

65
00:04:22,730 --> 00:04:23,810
That's simple.

66
00:04:23,810 --> 00:04:25,100
I guess that's cool.

67
00:04:25,100 --> 00:04:30,350
But we're still not seeing any information from the customers table.

68
00:04:30,380 --> 00:04:33,470
Alongside the information from the orders table.

69
00:04:33,500 --> 00:04:38,030
We have to know that customer ID here, one belongs to Boy George.

70
00:04:38,030 --> 00:04:43,370
But as I was getting it, it would be nice to have that information joined together.

71
00:04:43,370 --> 00:04:49,340
And the focus of the rest of the section is all about joints, which is a particular clause in my SQL.

72
00:04:49,340 --> 00:04:51,710
It's an actual word we write join.

73
00:04:51,710 --> 00:04:54,290
We'll get to that shortly in the next video.

74
00:04:54,290 --> 00:05:01,760
But what I want to show at the end of this video is one type of a SQL join that is particularly, I

75
00:05:01,760 --> 00:05:06,650
won't say useless, but very much less so useful than the other ones.

76
00:05:06,650 --> 00:05:10,880
I'm going to show you this type of joint is something called a cross join.

77
00:05:11,030 --> 00:05:13,970
And it's all I think it's also called a Cartesian join.

78
00:05:13,970 --> 00:05:20,930
A cross join is simply going to take every single row from customers and combine it with every single

79
00:05:20,930 --> 00:05:21,950
row from orders.

80
00:05:21,950 --> 00:05:24,140
We'll get every combination possible.

81
00:05:24,140 --> 00:05:26,420
There's no logic really at all.

82
00:05:26,420 --> 00:05:34,310
It is a dumb join meaning it's not going to take a boy George and combine him with his actual orders.

83
00:05:34,310 --> 00:05:41,600
Instead, it's just going to combine everything it looks like this select star from and then customers

84
00:05:41,600 --> 00:05:43,100
comma orders.

85
00:05:43,430 --> 00:05:51,350
So two different tables that I'm selecting Star from when I run this, this is the end result.

86
00:05:52,040 --> 00:05:56,810
It's a combination of every single customer with every single order.

87
00:05:57,080 --> 00:06:04,430
So you'll see we have, I think I have six rows for orders and I have five rows for customers.

88
00:06:04,430 --> 00:06:06,410
We get 30 total rows.

89
00:06:07,280 --> 00:06:08,720
So take a look here.

90
00:06:09,200 --> 00:06:15,620
In this first chunk, we have every user, Betty Davis or every customer Betty Davis, Blue Steel,

91
00:06:15,620 --> 00:06:17,960
David Bowie, George Michael, Boy George.

92
00:06:17,960 --> 00:06:19,430
And then it repeats again.

93
00:06:19,880 --> 00:06:22,490
And then it repeats again and it repeats again.

94
00:06:22,490 --> 00:06:29,780
But what's different is if you look at the right half of this result, this is the first order order

95
00:06:29,780 --> 00:06:30,800
ID of one.

96
00:06:30,800 --> 00:06:38,540
So we have a combination of Betty Davis with order ID one, Blue Steel Order ID one, David Bowie Order

97
00:06:38,540 --> 00:06:39,380
ID one.

98
00:06:39,470 --> 00:06:41,000
Again, this is not useful.

99
00:06:41,000 --> 00:06:46,490
This doesn't tell us anything, but it is something we can do and it is technically a join.

100
00:06:46,490 --> 00:06:50,360
What we're going to see in the next video is how we can make joints that help us.

101
00:06:50,360 --> 00:06:53,660
That actually makes sense where we join together.

102
00:06:53,690 --> 00:07:01,370
Bette Davis Only with the orders Bette Davis made or David Bowie with the orders David Bowie made,

103
00:07:01,370 --> 00:07:07,250
or if we have anybody who didn't make any orders, then they won't be joined with any orders.

104
00:07:07,250 --> 00:07:13,040
But right now, you know, if we look at customer ID, it's one, two, five, three.

105
00:07:13,040 --> 00:07:18,440
So for the person with ID of four is my cat Blue Steel.

106
00:07:18,530 --> 00:07:24,080
She never made any orders, but she's still sitting right alongside this order here.

107
00:07:24,080 --> 00:07:27,770
And this order over here and this order here.

108
00:07:27,770 --> 00:07:28,850
It makes no sense.

109
00:07:29,120 --> 00:07:30,410
But that's a cross join.

110
00:07:30,440 --> 00:07:35,090
It just takes every bit of information from both tables that we provided.

111
00:07:35,090 --> 00:07:39,080
We just separate them with commas and it basically cross multiplies them.

112
00:07:39,080 --> 00:07:43,610
It says, I'll take this first order and set that alongside the first user.

113
00:07:43,610 --> 00:07:49,430
Here's the same first order, second user, first order, third user, and it just makes these combinations.

114
00:07:49,550 --> 00:07:51,460
So that's technically a join.

115
00:07:51,470 --> 00:07:52,580
It's a simple one.

116
00:07:52,580 --> 00:07:55,970
Next up, we're going to learn about useful joins Hang in there.

117
00:07:55,970 --> 00:07:59,210
It's a lot but it's also really powerful technique.

