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.