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 SELECT, INSERT, and WHERE.

With SELECT

You can use a subquery anywhere you might use a column or table.

For example:

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.

With INSERT

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 owner_name.

With WHERE

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 WHERE in SELECT though: it'll work on any WHERE clause such as UPDATE or DELETE.

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.

For example:

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!