Instructor: Hi guys and welcome back.
In this video I wanted to tell you about nested queries.
A sub query essentially, and we need this in order
to find the polls and options for the latest poll
added to a database, so here's how it works.
Let's say we've got this query, that we use to find
the latest poll information, select id from polls,
order by id in descending order, limit one.
This, since we're only selecting from the poll's table,
is always gonna give us the latest poll,
or the latest id, or the highest id.
Because we're doing select id,
this gives us only the id column,
but let's we want this query as well,
select star from polls, join options on the id,
where the poll id is equal to something.
Now, we can use this second query to find the latest poll,
if we run the first query.
So, we can run the first one, find the id,
and then put that into the where clause of the second query.
But it's inefficient right, we've gotta run two things,
we have to get the result of one query first,
put it into the other, then get the result
of the second query, you know it's not great.
So instead what we can do, is we can put one
inside of the other like this.
Select star from polls, join options, where the polls id
is equal to the result of running this other query,
select id from polls.
These things, bottom query is there, the outer query,
the first query is the inner query,
and that's just what they're called as well.
So, let's grab this and something important to note here,
is there's no semicolons there, whenever you put a semicolon
in SQL code that signals the end of the query,
and if you put one there, then you're essentially
missing a bracket, so don't put it there,
only at the end of the outer query,
is where you put the colon.
So where can you use nested queries?
Is it only in where clauses?
Well really anywhere, a column table
or data set is referenced.
So, the columns of a select, instead of select star
you can do select, and then put a sub query in there,
and then put a from after the sub query,
I'll show you an example in just a moment.
You can put in the values of an insert,
the value of a where, really a lot of places,
but we can't possibly give you every example of course.
So here's an example, as the column of a select id, comma,
and then select the count star from options
where options dot poll id equal poll id, from polls.
What this is gonna do is it's going to find the id
of the polls, and it's also gonna, for each poll id,
give you the count of options,
notice that it's very interesting in here,
as well as the fact that I forgot to capitalise
this keywords, and that the inner query
has access to polls dot id.
So it has access to the outer queries column,
this is why we can do this here,
using this where clause there.
So that's pretty useful, can be useful
in a number of scenarios, so something to take into account.
The other thing is insert into poll authors,
and then what you're inserting is actually the result
of running another query, and that can be a way
to copy tables for example, what we're gonna do here
is we're gonna insert potentially many different rows,
and we're gonna insert everything that comes back
from this select statement.
Something important here is of course,
this table poll authors has to have the same
number of columns as we are selecting there,
so that they match,
otherwise then you're gonna get into trouble.
You can also use the with keyword to extract sub queries
so that your main query doesn't get too complicated.
So, this is an example of that.
We've got with latest id as this sub query,
then what's happening is latest id becomes the result
of running this query, and then without a semicolon,
we can then go into our outer query,
select star from polls, blah, blah, blah,
where polls dot id equal,
and then we're doing select star from latest id.
So, here we need a second select,
previously we could just put this directly in there,
so this may look like it's generally worse,
but if this query was longer,
then it would start to make a bit more sense.
Here they're both quite short,
so it's probably a bit more difficult to see the purpose
of doing this with, but when you're nesting more
as I say here, then it can be more useful.
All right, so wrapping up.
As we learn about more advanced concepts,
like this sub query example, no guide or tutorial
or course can give you everything,
as I mentioned earlier on, so you should do what I do,
and go and read the documentation,
which can be tricky to do,
because it often links to other parts of the documentation
and links elsewhere, so it can be quite time consuming,
it can send you down this you know,
spiderweb of reading documentation,
but really that is what software development is about.
So do that, get accustomed to reading the documentation,
trying to figure out, sort of how it works,
and how you can get the most information from it.
All right, now we've got this,
let's go into our application and make use of it.
Here I'm in PyCharm and I have gone ahead
and created the query for us, we've got select latest poll,
and that is gonna be the select star from polls.
Join options on polls dot id equal options dot poll id,
where the polls table id column has a value
equal to the result of select id from polls,
order by id descending, limit one.
I've created this and now all we have to do is grab that,
go into get latest poll and run it,
so we'll do cursor dot execute, that return cursor
dot fetch all, remember, because we are selecting
the latest poll together with it's options,
that's gonna return multiple rows, one per poll
plus option combination, so that's why we wanna do
fetch all here, and not just fetch one.
All right, thank you guys for joining me,
thanks for watching this video, I hope you've enjoyed it,
and I'll see you in the next one.