WEBVTT 1 00:00:00.210 --> 00:00:01.730 Hi guys, and welcome back. 2 00:00:01.730 --> 00:00:03.130 In this video we're going to learn 3 00:00:03.130 --> 00:00:05.310 how to extract data from two 4 00:00:05.310 --> 00:00:08.280 or more tables at once using JOIN. 5 00:00:08.280 --> 00:00:11.810 So let's say we've got one table here for users 6 00:00:11.810 --> 00:00:14.230 and the other one for accounts. 7 00:00:14.230 --> 00:00:17.000 On the left you can see you've got the id column 8 00:00:17.000 --> 00:00:20.060 and on the right you've got id columns and holder id, 9 00:00:20.060 --> 00:00:20.900 which as you know, 10 00:00:20.900 --> 00:00:23.690 are primary and foreign keys respectively. 11 00:00:23.690 --> 00:00:26.590 Using JOIN, we can make a query like this one. 12 00:00:26.590 --> 00:00:28.160 So here we've got, 13 00:00:28.160 --> 00:00:29.520 select * 14 00:00:29.520 --> 00:00:30.650 from, 15 00:00:30.650 --> 00:00:34.230 but then we're not only putting one table here, 16 00:00:34.230 --> 00:00:37.230 we're seeing users JOIN accounts. 17 00:00:37.230 --> 00:00:41.450 And this is going to essentially join both tables together 18 00:00:41.450 --> 00:00:45.050 and allow us to perform a select or where closes 19 00:00:45.050 --> 00:00:49.850 and order buys and so forth from the joining of both tables. 20 00:00:49.850 --> 00:00:53.140 Important we have to say how the tables are joined. 21 00:00:53.140 --> 00:00:56.720 And so here we are saying that for each row 22 00:00:56.720 --> 00:00:58.710 on the left table, 23 00:00:58.710 --> 00:01:01.910 we're taking the id value and we're mapping it to 24 00:01:01.910 --> 00:01:05.070 the holder_id value on the right table. 25 00:01:05.070 --> 00:01:07.470 So here is the final table, 26 00:01:07.470 --> 00:01:08.750 how it's going to be constructed. 27 00:01:08.750 --> 00:01:11.410 We've got all the columns from both tables. 28 00:01:11.410 --> 00:01:13.940 That's what the select * is telling us. 29 00:01:13.940 --> 00:01:16.880 And notice that that means that we've got id first name 30 00:01:16.880 --> 00:01:19.350 surname from the users table, 31 00:01:19.350 --> 00:01:21.800 and we also have id holder_id, 32 00:01:21.800 --> 00:01:24.060 a number from the accounts table. 33 00:01:24.060 --> 00:01:25.550 There's two id columns there, 34 00:01:25.550 --> 00:01:26.540 that's totally fine, 35 00:01:26.540 --> 00:01:28.690 we know that they mean different things. 36 00:01:28.690 --> 00:01:33.450 Now then we're going to map the id values on the left 37 00:01:33.450 --> 00:01:35.860 to the holder_id values on the right, 38 00:01:35.860 --> 00:01:39.420 that's what the own clause does in our query. 39 00:01:39.420 --> 00:01:40.720 And when that happens, 40 00:01:40.720 --> 00:01:43.559 we're going to extract both rows, 41 00:01:43.559 --> 00:01:45.520 join them together, 42 00:01:45.520 --> 00:01:47.370 and that's the result of our JOIN. 43 00:01:47.370 --> 00:01:48.820 So the same thing's gonna happen 44 00:01:48.820 --> 00:01:50.950 for every other row here. 45 00:01:50.950 --> 00:01:52.020 We've got row two, 46 00:01:52.020 --> 00:01:54.692 you can see that the id value on the left 47 00:01:54.692 --> 00:01:59.009 always maps to the correct holder_id value on the right, 48 00:01:59.009 --> 00:02:02.690 and that's how our table gets constructed. 49 00:02:02.690 --> 00:02:05.250 Now, instead of select * to select all columns, 50 00:02:05.250 --> 00:02:07.520 we can select only certain columns. 51 00:02:07.520 --> 00:02:08.353 Here for example, 52 00:02:08.353 --> 00:02:10.320 we've got users.* 53 00:02:10.320 --> 00:02:12.917 that gives us all the columns from the users table, 54 00:02:12.917 --> 00:02:16.560 and accounts.number that gives us the account number only. 55 00:02:16.560 --> 00:02:20.100 So the end result of this query would be four columns, 56 00:02:20.100 --> 00:02:22.840 the three from the users table and the account number. 57 00:02:22.840 --> 00:02:24.439 Notice that the JOIN is still the same, 58 00:02:24.439 --> 00:02:27.530 it is not affected by what we're selecting, 59 00:02:27.530 --> 00:02:31.000 so we can still compare on accounts.holder_id, 60 00:02:31.000 --> 00:02:33.400 even though we're not selecting that totally fine. 61 00:02:33.400 --> 00:02:36.850 The order of the JOIN sometimes matters, 62 00:02:36.850 --> 00:02:39.620 so accounts JOIN users is not the same as 63 00:02:39.620 --> 00:02:40.850 users JOIN accounts, 64 00:02:40.850 --> 00:02:42.190 the table on the left, 65 00:02:42.190 --> 00:02:44.130 and therefore the columns on the left 66 00:02:44.130 --> 00:02:45.270 are gonna be different. 67 00:02:45.270 --> 00:02:47.406 But there's also a few more differences 68 00:02:47.406 --> 00:02:49.490 that we're going to learn about when we learn about 69 00:02:49.490 --> 00:02:52.490 the different types of JOIN in a couple of lectures time. 70 00:02:52.490 --> 00:02:55.975 For now though, you can see that the table on the left 71 00:02:55.975 --> 00:02:58.590 is going to have all its rows there 72 00:02:58.590 --> 00:03:01.560 and we're going to map to it the table on the right. 73 00:03:01.560 --> 00:03:04.589 So can you only join the primary and foreign keys? 74 00:03:04.589 --> 00:03:06.660 No, you can join on any column, 75 00:03:06.660 --> 00:03:09.370 but using primary and foreign keys is gonna be much faster. 76 00:03:09.370 --> 00:03:11.520 So that's normally what you'd wanna be doing. 77 00:03:11.520 --> 00:03:13.000 Thank you guys for watching, 78 00:03:13.000 --> 00:03:14.180 I hope you've enjoyed this video, 79 00:03:14.180 --> 00:03:15.893 I'll see you in the next one.