Open In App

PostgreSQL – Subquery

Improve
Improve
Like Article
Like
Save
Share
Report

In this article we will discuss the process of constructing complex queries using the PostgreSQL subquery. Subqueries in the simplest term can be defined as multiple queries disguised in a single PostgreSQL command.
For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.
Now, let’s look into a few examples of PostgreSQL subqueries.

Example 1:

Here we will query for all films whose rental rate is higher than the average rental rate from the “film” table of our sample database. For that we will need to find the average rental rate by using the SELECT statement and average function( AVG). Then use the result of the first query in the second SELECT statement to find the films that has higher rental rate than the average.

SELECT
    AVG (rental_rate)
FROM
    film;

Output:

Now we will query for films whose rental rate is higher than the average rental rate.

SELECT
    film_id,
    title,
    rental_rate
FROM
    film
WHERE
    rental_rate > 2.98;

Output:

As you can observe the above query is not too elegant and requires an unnecessary amount of multiple queries.This can be avoided by using PostgreSQL subqueries as below.

SELECT
    film_id,
    title,
    rental_rate
FROM
    film
WHERE
    rental_rate > (
        SELECT
            AVG (rental_rate)
        FROM
            film
    );

Output:

A couple of this to notice about the sequence of execution of the above query:

  • First, executes the subquery.
  • Second, gets the result and passes it to the outer query.
  • Third, executes the outer query.

Example 2:
Here we will query for all films that have the returned date between 2005-05-29 and 2005-05-30, using the IN operator in the “rental” table of our sample database.

SELECT
    film_id,
    title
FROM
    film
WHERE
    film_id IN (
        SELECT
            inventory.film_id
        FROM
            rental
        INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
        WHERE
            return_date BETWEEN '2005-05-29'
        AND '2005-05-30'
    );

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads