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
Share your thoughts in the comments
Please Login to comment...