1 00:00:02,060 --> 00:00:03,910 So how can we now query data 2 00:00:03,910 --> 00:00:05,900 across multiple tables? 3 00:00:05,900 --> 00:00:08,610 For this, let's maybe say what we wanna get 4 00:00:08,610 --> 00:00:11,750 are a list of all the restaurants 5 00:00:11,750 --> 00:00:14,750 with the address data mixed into it. 6 00:00:14,750 --> 00:00:18,480 So that I have my restaurants here, 7 00:00:18,480 --> 00:00:21,610 I'm querying from restaurants. 8 00:00:21,610 --> 00:00:24,860 But now, instead of having just the address_id here, 9 00:00:24,860 --> 00:00:27,250 I would rather have all the restaurant data 10 00:00:27,250 --> 00:00:29,113 merged into the result. 11 00:00:29,960 --> 00:00:33,590 It's stored in separate tables for a good reason, 12 00:00:33,590 --> 00:00:38,330 so that the data pieces are very manageable in our database. 13 00:00:38,330 --> 00:00:40,260 But when I work with that data, 14 00:00:40,260 --> 00:00:42,810 when I fetch it into my application, 15 00:00:42,810 --> 00:00:45,610 I might wanna get that merged view 16 00:00:45,610 --> 00:00:48,180 where I have one large table 17 00:00:48,180 --> 00:00:51,070 that doesn't actually exist in the database, 18 00:00:51,070 --> 00:00:53,000 but which I get as a result 19 00:00:53,000 --> 00:00:55,580 that has the full address data in it 20 00:00:55,580 --> 00:00:58,330 instead of just the address_id. 21 00:00:58,330 --> 00:01:02,740 Because otherwise, if we couldn't get such a merged table, 22 00:01:02,740 --> 00:01:06,020 what we would have to do is select the restaurants 23 00:01:06,020 --> 00:01:10,640 and then run extra queries for getting their address data 24 00:01:10,640 --> 00:01:12,210 by that ID. 25 00:01:12,210 --> 00:01:13,890 That would be extra steps, 26 00:01:13,890 --> 00:01:17,423 which are unnecessary and which cost extra performance. 27 00:01:18,500 --> 00:01:20,630 That's why instead in SQL, 28 00:01:20,630 --> 00:01:25,490 we have a very important keyword or functionality 29 00:01:25,490 --> 00:01:27,920 built into that SQL language 30 00:01:27,920 --> 00:01:32,520 that allows us to actually query across multiple tables 31 00:01:32,520 --> 00:01:36,743 and that allows us to join the data from these tables. 32 00:01:37,700 --> 00:01:40,600 And that's the keyword JOIN. 33 00:01:40,600 --> 00:01:44,220 What you can do when you run a select statement like this 34 00:01:44,220 --> 00:01:49,220 is you can add INNER JOIN after the SELECT FROM part. 35 00:01:51,300 --> 00:01:55,330 There are also other joins like left join, 36 00:01:55,330 --> 00:01:58,830 but the INNER JOIN is the most common and important one 37 00:01:58,830 --> 00:02:00,853 and therefore we'll focus on that here. 38 00:02:01,870 --> 00:02:02,940 With the INNER JOIN, 39 00:02:02,940 --> 00:02:05,453 you can combine data from different tables. 40 00:02:06,340 --> 00:02:10,000 Now, it works such that after INNER JOIN, 41 00:02:10,000 --> 00:02:13,970 you specify the table that has the related data 42 00:02:13,970 --> 00:02:16,050 that you wanna join. 43 00:02:16,050 --> 00:02:19,020 For example here, addresses, 44 00:02:19,020 --> 00:02:21,953 so we wanna join addresses with the restaurants. 45 00:02:23,310 --> 00:02:25,170 Now for INNER JOIN to work, 46 00:02:25,170 --> 00:02:27,640 you now need to let SQL know 47 00:02:27,640 --> 00:02:31,820 on which field you wanna join those tables. 48 00:02:31,820 --> 00:02:36,710 So which field, which column in restaurants contains data 49 00:02:36,710 --> 00:02:40,810 that can also be found in addresses that allows SQL 50 00:02:40,810 --> 00:02:42,353 to then link records together. 51 00:02:43,560 --> 00:02:46,100 And in our case, that's the address_id 52 00:02:46,100 --> 00:02:47,780 because in the restaurants table, 53 00:02:47,780 --> 00:02:52,780 the address_id stores ID values from the addresses table. 54 00:02:53,170 --> 00:02:56,163 That's why we added that extra table field here. 55 00:02:57,130 --> 00:03:01,400 So therefore now after specifying the related table name, 56 00:03:01,400 --> 00:03:04,860 after INNER JOIN, we add the ON keyword 57 00:03:04,860 --> 00:03:08,180 and now we let SQL know how to connect records 58 00:03:08,180 --> 00:03:10,140 from the two tables. 59 00:03:10,140 --> 00:03:13,390 And here, we would simply say that we wanna join 60 00:03:13,390 --> 00:03:16,000 ON restaurants.address_id, 61 00:03:18,660 --> 00:03:21,980 so on the address_id field in the restaurants table, 62 00:03:21,980 --> 00:03:23,513 the dot is important, 63 00:03:24,520 --> 00:03:29,520 where the value stored there is equal to addresses.id. 64 00:03:33,240 --> 00:03:37,590 So we wanna join records where the values in address_id 65 00:03:37,590 --> 00:03:41,330 in the restaurants table meet the values in the ID column 66 00:03:41,330 --> 00:03:43,490 in the addresses table. 67 00:03:43,490 --> 00:03:46,310 And if you now execute this statement, 68 00:03:46,310 --> 00:03:49,680 possibly wrapping parentheses around this here, 69 00:03:49,680 --> 00:03:54,340 but that's optional, then what you get back is this table 70 00:03:54,340 --> 00:03:55,920 as you can see here. 71 00:03:55,920 --> 00:03:57,530 And that's now not a table 72 00:03:57,530 --> 00:03:59,620 that would be stored in a database. 73 00:03:59,620 --> 00:04:03,380 Instead, it's basically just a result set 74 00:04:03,380 --> 00:04:06,240 that is created on demand for this query 75 00:04:06,240 --> 00:04:08,290 and that's returned to you. 76 00:04:08,290 --> 00:04:11,210 And this now does not just contain the restaurant data 77 00:04:11,210 --> 00:04:15,653 like the name, but also the complete address data. 78 00:04:17,010 --> 00:04:18,690 And I'm selecting everything here. 79 00:04:18,690 --> 00:04:22,050 Of course, you could now also be more restrictive. 80 00:04:22,050 --> 00:04:24,200 Instead of selecting everything here, 81 00:04:24,200 --> 00:04:28,277 you could also just select let's say restaurants.name 82 00:04:30,420 --> 00:04:35,420 and then separated by a comma, addresses.star. 83 00:04:35,940 --> 00:04:37,920 That would say that for the restaurants, 84 00:04:37,920 --> 00:04:39,380 I only wanna get the name. 85 00:04:39,380 --> 00:04:42,360 And for the addresses, I wanna get everything. 86 00:04:42,360 --> 00:04:46,550 And maybe I also wanna get the restaurants.id though. 87 00:04:46,550 --> 00:04:48,760 So that's now a query we could execute. 88 00:04:48,760 --> 00:04:52,070 And if we do so, we get that data. 89 00:04:52,070 --> 00:04:56,130 We don't get the address_id and the type_id anymore. 90 00:04:56,130 --> 00:04:58,590 Instead, we now get the restaurant name 91 00:04:58,590 --> 00:05:02,110 and the full address data for every restaurant. 92 00:05:02,110 --> 00:05:06,570 And it is worth pointing out that now with that, of course, 93 00:05:06,570 --> 00:05:09,010 we actually don't work with a table 94 00:05:09,010 --> 00:05:11,430 that's stored like this in the database, 95 00:05:11,430 --> 00:05:15,760 but as mentioned before, an on-demand created results set, 96 00:05:15,760 --> 00:05:18,453 which we do create with this statement. 97 00:05:21,230 --> 00:05:25,300 Now, you are also not limited to just one join. 98 00:05:25,300 --> 00:05:28,840 Often, you wanna join data from more than one table, 99 00:05:28,840 --> 00:05:31,320 and that is something you can do. 100 00:05:31,320 --> 00:05:33,530 For this, I'll split this across multiple lines 101 00:05:33,530 --> 00:05:35,250 just to make it easier to read 102 00:05:35,250 --> 00:05:38,430 and I'll simply add another INNER JOIN here. 103 00:05:38,430 --> 00:05:40,680 So we already have one INNER JOIN. 104 00:05:40,680 --> 00:05:43,730 Now I'll add another INNER JOIN. 105 00:05:43,730 --> 00:05:48,710 And here, I wanna join on my types as well 106 00:05:48,710 --> 00:05:52,333 so that I also get all the type names for my restaurants. 107 00:05:53,320 --> 00:05:57,620 Because in the restaurants table, we just stored a type_id. 108 00:05:57,620 --> 00:06:01,421 Now I wanna get the human readable name instead. 109 00:06:01,421 --> 00:06:03,942 For this, I INNER JOIN on types 110 00:06:03,942 --> 00:06:08,775 and I join on restaurants.type_id being equal to types.id. 111 00:06:11,492 --> 00:06:15,520 So basically what I also did for the addresses. 112 00:06:15,520 --> 00:06:19,390 Now, I just wanna make sure that I also then print and show 113 00:06:19,390 --> 00:06:20,820 the human readable type 114 00:06:20,820 --> 00:06:24,420 and therefore here after addresses.star, 115 00:06:24,420 --> 00:06:28,210 I'll make sure that I show types.name. 116 00:06:28,210 --> 00:06:30,610 We have a name column in the types table, 117 00:06:30,610 --> 00:06:34,913 and I wanna get that name once we joined the tables. 118 00:06:36,150 --> 00:06:38,050 So now if we execute this, 119 00:06:38,050 --> 00:06:41,120 you also have the name field here at the very end 120 00:06:41,120 --> 00:06:42,863 and that's our type name. 121 00:06:44,500 --> 00:06:46,670 Now, since we have two name fields here now 122 00:06:46,670 --> 00:06:48,780 for the restaurant and for the type, 123 00:06:48,780 --> 00:06:53,180 we can also assign a so-called alias with the AS keyword 124 00:06:53,180 --> 00:06:56,540 which allows us to set a new column name here 125 00:06:56,540 --> 00:06:58,000 in our results set 126 00:06:59,150 --> 00:07:00,060 like this. 127 00:07:00,060 --> 00:07:01,700 Now we have type_name here, 128 00:07:01,700 --> 00:07:05,230 which is not the actual name of the column in the database, 129 00:07:05,230 --> 00:07:09,063 but a name that is chosen here for this result set. 130 00:07:09,900 --> 00:07:13,770 So that's now how you can join data from tables. 131 00:07:13,770 --> 00:07:16,800 Now, we can also add WHERE clauses here. 132 00:07:16,800 --> 00:07:18,990 Let's say we wanna get all the restaurants 133 00:07:18,990 --> 00:07:20,830 that are in Munich. 134 00:07:20,830 --> 00:07:23,160 Now for this, we can add a WHERE clause here 135 00:07:23,160 --> 00:07:25,320 after the INNER JOINs. 136 00:07:25,320 --> 00:07:28,180 And that's important. It has to come at the end here. 137 00:07:28,180 --> 00:07:32,647 And there, we can say WHERE addresses.city 138 00:07:33,900 --> 00:07:36,623 is equal to Munich, for example. 139 00:07:37,620 --> 00:07:39,930 We can work with our addresses table here 140 00:07:39,930 --> 00:07:43,423 because we're joining it into this overall SELECT statement. 141 00:07:44,470 --> 00:07:46,340 And hence, if I execute this now, 142 00:07:46,340 --> 00:07:49,240 I only get the restaurants in Munich. 143 00:07:49,240 --> 00:07:51,260 So that also works. 144 00:07:51,260 --> 00:07:53,940 And of course, there's more you can do with that. 145 00:07:53,940 --> 00:07:56,600 I can only encourage you to play around with that 146 00:07:56,600 --> 00:07:58,110 and experiment with that, 147 00:07:58,110 --> 00:08:00,130 but as always and as mentioned before, 148 00:08:00,130 --> 00:08:04,540 the official SQL documentation or a complete SQL course 149 00:08:04,540 --> 00:08:07,260 like our course or any other course of course 150 00:08:07,260 --> 00:08:10,993 is the way to go if you wanna learn all about SQL. 151 00:08:12,000 --> 00:08:14,940 Now with that, we covered basically all the basics 152 00:08:14,940 --> 00:08:16,730 I do wanna cover here though 153 00:08:16,730 --> 00:08:19,390 and therefore to now conclude this, 154 00:08:19,390 --> 00:08:21,610 here's a little challenge for you. 155 00:08:21,610 --> 00:08:23,820 Write a query from the ground up 156 00:08:23,820 --> 00:08:25,620 where you select all the reviews 157 00:08:25,620 --> 00:08:27,890 where the rating is greater than three 158 00:08:27,890 --> 00:08:31,550 and make sure you merge in all the restaurant data 159 00:08:31,550 --> 00:08:34,789 and the full address data for these restaurants 160 00:08:34,789 --> 00:08:38,150 and the full type data for these restaurants 161 00:08:38,150 --> 00:08:39,563 into the result set. 162 00:08:40,429 --> 00:08:41,870 That's a challenge for you. 163 00:08:41,870 --> 00:08:44,550 We'll write and execute that query together 164 00:08:44,550 --> 00:08:45,703 in the next lecture.