Introduction to PostgreSQL subquery

A subquery is aquery nested within another query. A subquery is also known as an inner query or nested query.

A subquery can be useful for retrieving data that will be used by the main query as a condition for further data selection.

The basic syntax of the subquery is as follows:

SELECT  select_listFROM  table1WHERE  columnA operator (    SELECT      columnB    FROM      table2    WHERE      condition  );

In this syntax, the subquery is enclosed within parentheses and is executed first:

SELECT  columnBFROM  table2WHERE  condition

The main query will use the result of the subquery to filter data in theWHERE clause.

PostgreSQL subquery examples

Let’s take some examples of using subqueries.

1) Basic PostgreSQL subquery example

First, retrieve the country id of theUnited States from thecountry table:

SELECT  country_idFROM  countryWHERE  country= 'United States';

It returns the following output:

country_id------------        103(1 row)

Second, retrieve cities from thecity table wherecountry_id is103:

SELECT  cityFROM  cityWHERE  country_id = 103ORDER BY  city;

Output:

city------------------------- Akron Arlington Augusta-Richmond County Aurora Bellevue Brockton Cape Coral Citrus Heights...

Instead of executing two queries, you can combine them into one, making the first query as a subquery and the second query as the main query as follows:

SELECT  cityFROM  cityWHERE  country_id = (    SELECT      country_id    FROM      country    WHERE      country = 'United States'  )ORDER BY  city;

In this query, the following is the subquery:

SELECT  country_idFROM  countryWHERE  country= 'United States';

PostgreSQL executes the subquery first to get the country id and uses it for theWHERE clause to retrieve the cities.

2) Using a subquery with the IN operator

A subquery can return zero or more rows. If the query returns more than one row, you can use it with theIN operator. For example:

First, retrievefilm_id of the film with the categoryAction:

SELECT  film_idFROM  film_category  INNER JOIN categoryUSING(category_id)WHERE  name = 'Action';

Output:

film_id---------      19      21      29      38      56...

Second, use the query above as a subquery to retrieve the film title from thefilm table:

SELECT  film_id,  titleFROM  filmWHERE  film_id IN (    SELECT      film_id    FROM      film_category      INNER JOIN category USING(category_id)    WHERE      name = 'Action'  )ORDER BY  film_id;

Output:

film_id |          title---------+-------------------------      19 | Amadeus Holy      21 | American Circus      29 | Antitrust Tomatoes      38 | Ark Ridgemont      56 | Barefoot Manchurian...

Summary

• A subquery is a query nested inside another query
• A subquery is also known as an inner query or nested query.

Last updated onJuly 1, 2024

On this page

• Introduction to PostgreSQL subquery
• PostgreSQL subquery examples
• Summary