WEBVTT 1 00:00:00.000 --> 00:00:01.960 Hi guys and welcome back. 2 00:00:01.960 --> 00:00:05.540 In this video I wanted to tell you about nested queries. 3 00:00:05.540 --> 00:00:08.180 A sub query essentially, and we need this in order 4 00:00:08.180 --> 00:00:12.300 to find the polls and options for the latest poll 5 00:00:12.300 --> 00:00:15.840 added to a database, so here's how it works. 6 00:00:15.840 --> 00:00:19.160 Let's say we've got this query, that we use to find 7 00:00:19.160 --> 00:00:23.300 the latest poll information, select id from polls, 8 00:00:23.300 --> 00:00:26.490 order by id in descending order, limit one. 9 00:00:26.490 --> 00:00:29.190 This, since we're only selecting from the poll's table, 10 00:00:29.190 --> 00:00:31.240 is always gonna give us the latest poll, 11 00:00:31.240 --> 00:00:34.600 or the latest id, or the highest id. 12 00:00:34.600 --> 00:00:36.220 Because we're doing select id, 13 00:00:36.220 --> 00:00:38.393 this gives us only the id column, 14 00:00:39.250 --> 00:00:42.420 but let's we want this query as well, 15 00:00:42.420 --> 00:00:47.420 select star from polls, join options on the id, 16 00:00:47.810 --> 00:00:50.550 where the poll id is equal to something. 17 00:00:50.550 --> 00:00:54.770 Now, we can use this second query to find the latest poll, 18 00:00:54.770 --> 00:00:56.740 if we run the first query. 19 00:00:56.740 --> 00:00:59.650 So, we can run the first one, find the id, 20 00:00:59.650 --> 00:01:03.493 and then put that into the where clause of the second query. 21 00:01:04.420 --> 00:01:08.060 But it's inefficient right, we've gotta run two things, 22 00:01:08.060 --> 00:01:10.550 we have to get the result of one query first, 23 00:01:10.550 --> 00:01:12.320 put it into the other, then get the result 24 00:01:12.320 --> 00:01:15.420 of the second query, you know it's not great. 25 00:01:15.420 --> 00:01:17.580 So instead what we can do, is we can put one 26 00:01:17.580 --> 00:01:19.800 inside of the other like this. 27 00:01:19.800 --> 00:01:24.590 Select star from polls, join options, where the polls id 28 00:01:24.590 --> 00:01:28.100 is equal to the result of running this other query, 29 00:01:28.100 --> 00:01:29.540 select id from polls. 30 00:01:29.540 --> 00:01:32.410 These things, bottom query is there, the outer query, 31 00:01:32.410 --> 00:01:34.930 the first query is the inner query, 32 00:01:34.930 --> 00:01:37.530 and that's just what they're called as well. 33 00:01:37.530 --> 00:01:40.900 So, let's grab this and something important to note here, 34 00:01:40.900 --> 00:01:44.850 is there's no semicolons there, whenever you put a semicolon 35 00:01:44.850 --> 00:01:47.900 in SQL code that signals the end of the query, 36 00:01:47.900 --> 00:01:50.690 and if you put one there, then you're essentially 37 00:01:50.690 --> 00:01:53.240 missing a bracket, so don't put it there, 38 00:01:53.240 --> 00:01:55.350 only at the end of the outer query, 39 00:01:55.350 --> 00:01:57.440 is where you put the colon. 40 00:01:57.440 --> 00:01:59.540 So where can you use nested queries? 41 00:01:59.540 --> 00:02:01.800 Is it only in where clauses? 42 00:02:01.800 --> 00:02:04.540 Well really anywhere, a column table 43 00:02:04.540 --> 00:02:06.650 or data set is referenced. 44 00:02:06.650 --> 00:02:10.340 So, the columns of a select, instead of select star 45 00:02:10.340 --> 00:02:13.350 you can do select, and then put a sub query in there, 46 00:02:13.350 --> 00:02:15.880 and then put a from after the sub query, 47 00:02:15.880 --> 00:02:17.980 I'll show you an example in just a moment. 48 00:02:17.980 --> 00:02:20.420 You can put in the values of an insert, 49 00:02:20.420 --> 00:02:22.780 the value of a where, really a lot of places, 50 00:02:22.780 --> 00:02:25.930 but we can't possibly give you every example of course. 51 00:02:25.930 --> 00:02:30.930 So here's an example, as the column of a select id, comma, 52 00:02:31.880 --> 00:02:34.770 and then select the count star from options 53 00:02:34.770 --> 00:02:39.130 where options dot poll id equal poll id, from polls. 54 00:02:39.130 --> 00:02:42.690 What this is gonna do is it's going to find the id 55 00:02:42.690 --> 00:02:47.370 of the polls, and it's also gonna, for each poll id, 56 00:02:47.370 --> 00:02:49.170 give you the count of options, 57 00:02:49.170 --> 00:02:50.874 notice that it's very interesting in here, 58 00:02:50.874 --> 00:02:53.110 as well as the fact that I forgot to capitalise 59 00:02:53.110 --> 00:02:56.798 this keywords, and that the inner query 60 00:02:56.798 --> 00:02:59.803 has access to polls dot id. 61 00:03:01.010 --> 00:03:03.980 So it has access to the outer queries column, 62 00:03:03.980 --> 00:03:06.444 this is why we can do this here, 63 00:03:06.444 --> 00:03:09.460 using this where clause there. 64 00:03:09.460 --> 00:03:11.750 So that's pretty useful, can be useful 65 00:03:11.750 --> 00:03:15.220 in a number of scenarios, so something to take into account. 66 00:03:15.220 --> 00:03:18.480 The other thing is insert into poll authors, 67 00:03:18.480 --> 00:03:21.500 and then what you're inserting is actually the result 68 00:03:21.500 --> 00:03:24.530 of running another query, and that can be a way 69 00:03:24.530 --> 00:03:26.990 to copy tables for example, what we're gonna do here 70 00:03:26.990 --> 00:03:29.740 is we're gonna insert potentially many different rows, 71 00:03:29.740 --> 00:03:32.130 and we're gonna insert everything that comes back 72 00:03:32.130 --> 00:03:33.550 from this select statement. 73 00:03:33.550 --> 00:03:35.220 Something important here is of course, 74 00:03:35.220 --> 00:03:38.100 this table poll authors has to have the same 75 00:03:38.100 --> 00:03:40.650 number of columns as we are selecting there, 76 00:03:40.650 --> 00:03:41.949 so that they match, 77 00:03:41.949 --> 00:03:44.640 otherwise then you're gonna get into trouble. 78 00:03:44.640 --> 00:03:48.840 You can also use the with keyword to extract sub queries 79 00:03:48.840 --> 00:03:51.720 so that your main query doesn't get too complicated. 80 00:03:51.720 --> 00:03:53.930 So, this is an example of that. 81 00:03:53.930 --> 00:03:58.618 We've got with latest id as this sub query, 82 00:03:58.618 --> 00:04:02.170 then what's happening is latest id becomes the result 83 00:04:02.170 --> 00:04:05.380 of running this query, and then without a semicolon, 84 00:04:05.380 --> 00:04:07.710 we can then go into our outer query, 85 00:04:07.710 --> 00:04:09.790 select star from polls, blah, blah, blah, 86 00:04:09.790 --> 00:04:12.810 where polls dot id equal, 87 00:04:12.810 --> 00:04:16.240 and then we're doing select star from latest id. 88 00:04:16.240 --> 00:04:18.940 So, here we need a second select, 89 00:04:18.940 --> 00:04:21.370 previously we could just put this directly in there, 90 00:04:21.370 --> 00:04:24.700 so this may look like it's generally worse, 91 00:04:24.700 --> 00:04:27.170 but if this query was longer, 92 00:04:27.170 --> 00:04:29.310 then it would start to make a bit more sense. 93 00:04:29.310 --> 00:04:30.500 Here they're both quite short, 94 00:04:30.500 --> 00:04:33.430 so it's probably a bit more difficult to see the purpose 95 00:04:33.430 --> 00:04:35.900 of doing this with, but when you're nesting more 96 00:04:35.900 --> 00:04:38.310 as I say here, then it can be more useful. 97 00:04:38.310 --> 00:04:40.260 All right, so wrapping up. 98 00:04:40.260 --> 00:04:42.820 As we learn about more advanced concepts, 99 00:04:42.820 --> 00:04:46.880 like this sub query example, no guide or tutorial 100 00:04:46.880 --> 00:04:48.250 or course can give you everything, 101 00:04:48.250 --> 00:04:51.970 as I mentioned earlier on, so you should do what I do, 102 00:04:51.970 --> 00:04:54.470 and go and read the documentation, 103 00:04:54.470 --> 00:04:56.480 which can be tricky to do, 104 00:04:56.480 --> 00:04:59.490 because it often links to other parts of the documentation 105 00:04:59.490 --> 00:05:01.930 and links elsewhere, so it can be quite time consuming, 106 00:05:01.930 --> 00:05:04.480 it can send you down this you know, 107 00:05:04.480 --> 00:05:06.470 spiderweb of reading documentation, 108 00:05:06.470 --> 00:05:09.980 but really that is what software development is about. 109 00:05:09.980 --> 00:05:11.810 So do that, get accustomed to reading the documentation, 110 00:05:11.810 --> 00:05:14.533 trying to figure out, sort of how it works, 111 00:05:14.533 --> 00:05:17.390 and how you can get the most information from it. 112 00:05:17.390 --> 00:05:18.980 All right, now we've got this, 113 00:05:18.980 --> 00:05:22.620 let's go into our application and make use of it. 114 00:05:22.620 --> 00:05:25.293 Here I'm in PyCharm and I have gone ahead 115 00:05:25.293 --> 00:05:28.400 and created the query for us, we've got select latest poll, 116 00:05:28.400 --> 00:05:30.690 and that is gonna be the select star from polls. 117 00:05:30.690 --> 00:05:34.770 Join options on polls dot id equal options dot poll id, 118 00:05:34.770 --> 00:05:37.900 where the polls table id column has a value 119 00:05:37.900 --> 00:05:41.640 equal to the result of select id from polls, 120 00:05:41.640 --> 00:05:44.770 order by id descending, limit one. 121 00:05:44.770 --> 00:05:47.650 I've created this and now all we have to do is grab that, 122 00:05:47.650 --> 00:05:50.640 go into get latest poll and run it, 123 00:05:50.640 --> 00:05:55.640 so we'll do cursor dot execute, that return cursor 124 00:05:55.790 --> 00:05:59.050 dot fetch all, remember, because we are selecting 125 00:05:59.050 --> 00:06:02.010 the latest poll together with it's options, 126 00:06:02.010 --> 00:06:05.730 that's gonna return multiple rows, one per poll 127 00:06:05.730 --> 00:06:08.320 plus option combination, so that's why we wanna do 128 00:06:08.320 --> 00:06:10.790 fetch all here, and not just fetch one. 129 00:06:10.790 --> 00:06:12.500 All right, thank you guys for joining me, 130 00:06:12.500 --> 00:06:14.490 thanks for watching this video, I hope you've enjoyed it, 131 00:06:14.490 --> 00:06:16.140 and I'll see you in the next one.