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.
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;
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;
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 );
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.
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' );
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.