PostgreSQL Subquery (original) (raw)

Last Updated : 12 Jul, 2025

**Subqueries, also known as nested queries or inner queries, are queries embedded within another SQL query. They allow you to perform multiple queries within a single PostgreSQL command, making your SQL statements more powerful and efficient. They allow you to perform multiple queries within a single PostgreSQL command, making your SQL statements more powerful and efficient.

Let us better understand the **Subqueries in **PostgreSQL from this article.

What is a Subquery?

A subquery is a query nested inside another query, typically within the '**SELECT', '**INSERT', '**UPDATE', or '**DELETE**'**statements. Subqueries are used to perform complex data retrieval operations and can return individual values or a set of rows that the main query uses for its conditions.

Syntax:

The basic syntax for a subquery is as follows:

**SELECT column1, column2
**FROM table1
**WHERE column1 = (**SELECT column1 **FROM table2 **WHERE condition);

PostgreSQL Subquery Examples

Let us look at some of the examples of Subqueries in PostgreSQL to better understand the concept.

**Example 1: Finding Films with Rental Rates Higher than Average

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.

**Query:

**SELECT
AVG (rental_rate)
**FROM
film;

**Output:

PostgreSQL Subquery Example

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

**Query:

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

**Output:

PostgreSQL Subquery Example

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.

**Query:

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

**Output:

PostgreSQL Subquery Example

**Sequence of Execution:

  1. Execute the subquery to get the average rental rate.
  2. Pass the result to the outer query.
  3. Execute the outer query to find films with rental rates higher than the average.

**Example 2: Finding Films Rented on Specific Dates

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:

PostgreSQL Subquery Example

Important Points About PostgreSQL Subquery