Summary: in this tutorial, you will learn how to use the PostgreSQL subquery that allows you to construct complex queries.
Introduction to PostgreSQL subquery
Let’s start with a simple example.
Suppose we want to find the films whose rental rate is higher than the average rental rate. We can do it in two steps:
- Find the average rental rate by using the SELECT statement and average function (
AVG). - Use the result of the first query in the second
SELECTstatement to find the films that we want.
The following query gets the average rental rate:
1 2 3 4 | SELECT AVG (rental_rate) FROM film; |
The average rental rate is 2.98
Now, we can get films whose rental rate is higher than the average rental rate:
1 2 3 4 5 6 7 8 | SELECT film_id, title, rental_rate FROM film WHERE rental_rate > 2.98; |
The code is not so elegant, which requires two steps. We want a way to pass the result of the first query to the second query in one query. The solution is to use a subquery.
A subquery is a query nested inside another query such as SELECT, INSERT, DELETE and UPDATE. In this tutorial, we are focusing on the SELECT statement only.
To construct a subquery, we put the second query in brackets and use it in the WHERE clause as an expression:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT film_id, title, rental_rate FROM film WHERE rental_rate > ( SELECT AVG (rental_rate) FROM film ); |
The query inside the brackets is called a subquery or an inner query. The query that contains the subquery is known as an outer query.
PostgreSQL executes the query that contains a subquery in the following sequence:
- First, executes the subquery.
- Second, gets the result and passes it to the outer query.
- Third, executes the outer query.
PostgreSQL subquery with IN operator
A subquery can return zero or more rows. To use this subquery, you use the IN operator in the WHERE clause.
For example, to get films that have the returned date between 2005-05-29 and 2005-05-30, you use the following query:
1 2 3 4 5 6 7 8 | 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'; |
It returns multiple rows so we can use this query as a subquery in the WHERE clause of a query as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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' ); |
PostgreSQL subquery with EXISTS operator
The following expression illustrates how to use a subquery with EXISTS operator:
1 | EXISTS subquery |
A subquery can be an input of the EXISTS operator. If the subquery returns any row, the EXISTS operator returns true. If the subquery returns no row, the result of EXISTS operator is false.
The EXISTS operator only cares about the number of rows returned from the subquery, not the content of the rows, therefore, the common coding convention of EXISTS operator is as follows:
1 | EXISTS (SELECT 1 FROM tbl WHERE condition); |
See the following query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT first_name, last_name FROM customer WHERE EXISTS ( SELECT 1 FROM payment WHERE payment.customer_id = customer.customer_id ); |
The query works like an inner join on the customer id column. However, it returns at most one row for each row in the customer table even though there are some corresponding rows in the payment table.
In this tutorial, you have learned how to use the PostgreSQL subquery to construct complex queries.