PostgreSQL Subquery

Summary: in this tutorial, you will learn how to use the PostgreSQL subquery that allows you to construct complex queries.

Introduction to PostgreSQL subquery

A subquery is a query 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_list
FROM
  table1
WHERE
  columnA operator (
    SELECT
      columnB
    FROM
      table2
    WHERE
      condition
  );

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

SELECT
  columnB
FROM
  table2
WHERE
  condition

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

PostgreSQL subquery examples

Let’s take some examples of using subqueries.

1) Basic PostgreSQL subquery example

First, retrieve the country id of the United States from the country table:

SELECT
  country_id
FROM
  country
WHERE
  country = 'United States';

It returns the following output:

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

Second, retrieve cities from the city table where country_id is 103:

SELECT
  city
FROM
  city
WHERE
  country_id = 103
ORDER 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
  city
FROM
  city
WHERE
  country_id = (
    SELECT
      country_id
    FROM
      country
    WHERE
      country = 'United States'
  )
ORDER BY
  city;

In this query, the following is the subquery:

SELECT
  country_id
FROM
  country
WHERE
  country = 'United States';

PostgreSQL executes the subquery first to get the country id and uses it for the WHERE 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 the IN operator. For example:

First, retrieve film_id of the film with the category Action:

SELECT
  film_id
FROM
  film_category
  INNER JOIN category USING(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 the film table:

SELECT
  film_id,
  title
FROM
  film
WHERE
  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.