# Nested queries: getting the latest poll
A nested query or subquery is just that: a query inside another query. These are normally known as the inner query and the outer query.
Subqueries can be useful to perform more complex queries where the outer query relies on data from the inner query.
For example, this single query here returns the latest poll id in our database:
SELECT id FROM polls ORDER BY id DESC LIMIT 1;
And this query here returns the options for a poll:
SELECT * FROM polls JOIN options ON polls.id = options.poll_id WHERE polls.id = %s;
But as you can see, we need to provide the poll
id in order for that query to work.
How could we instead get the options for the latest poll?
The simplest solution (which isn't particularly simple!) makes use of a subquery. Instead of an argument (
%s), we'll use a subquery to retrieve the latest poll id:
SELECT * FROM polls JOIN options ON polls.id = options.poll_id WHERE polls.id = ( SELECT id FROM polls ORDER BY id DESC LIMIT 1 );
Note that we have replaced the
%s for the subquery, and the subquery does not use a semicolon at the end as that would signal the end of the outer query as well.
# Usage of subqueries
Let's take a look at where else we can use subqueries: with
You can use a subquery anywhere you might use a column or table.
SELECT * FROM polls;
Could use a subquery instead of
polls. Then the columns selected would be selecting from the subquery return value.
Indeed, you could use a subquery instead of a column selected! However, if you do that, the subquery must only select one column.
SELECT id, ( SELECT COUNT(*) from options where options.poll_id = polls.id ) FROM polls;
Note that here, the subquery has access to
polls.id, from the outer query.
That query could tell us how many options we have for each poll.
You can also use a subquery instead of values to insert.
INSERT INTO poll_authors ( SELECT owner_name FROM polls );
This query here would insert into a table called
poll_authors one row for each row in
polls, but only inserting the
You can use a subquery in place of comparison values, as shown as the start of this chapter. Remember that you're not limited to
SELECT though: it'll work on any
WHERE clause such as
WITH queries to extract subqueries
Instead of writing the complete subqueries inline, you can extract them to the top of the query. This makes them easier to read, understand, and modify.
WITH latest_id AS ( SELECT id FROM polls ORDER BY id DESC LIMIT 1 ) SELECT * FROM polls JOIN options ON polls.id = options.poll_id WHERE polls.id = (SELECT * FROM latest_id);
Do note you still need the inline subquery, but it is now much simpler.
However this is especially useful when you are nesting more than one level of subqueries. We're not doing that here, but you can check the official documentation (opens new window) for more information.
# Wrapping Up
Something important to remember as we learn about more advanced PostgreSQL concepts (such as subqueries) is that many tutorials and guides (including this one) can't possibly give you examples of every type of subquery available.
It's always a good idea to check the official documentation (opens new window), as confusing as it may sometimes be!
The documentation will often link to other parts of the documentation, which makes reading through it a time-consuming job. However in exchange you'll get complete knowledge of how things work, as opposed to only examples and some explanations.
Naturally, we do our best to explain things simply, accurately, and concisely; however, in order to do that we skip (or sometimes miss) things!