PostgreSQL Subquery

Summary: In this tutorial, you will learn how to use PostgreSQL subquery to write more flexible queries in a more simple way.

Getting Started with PostgreSQL Subquery #

PostgreSQL allows you to embed a query within another query. This embedded query is called a subquery. The query that contains a subquery is called an outer query.

In essence, a subquery is a query embedded within an outer query.

The first important question is where to place the subquery inside a query.

To answer this question, let’s take another look at the SELECT statement:

SELECT
  select_list
FROM
  table1
  JOIN table2
WHERE
  condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this SELECT statement:

The SELECT clause can accept a single value (an expression or a column).
The FROM and JOIN clauses can accept table or a result set that consists of rows and columns.
The WHERE clause can accept a single value or column depending on the operator of the condition.

Depending on the shape of the data each clause accepts, you can use an appropriate subquery.

PostgreSQL subquery in the WHERE clause #

Image you want to find the most expensive products in the products table. To do that, you might perform two queries:

First, select the highest price from the products table:

SELECT
  MAX(price)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The query returns a single value:

   max
---------
 2999.99Code language: plaintext (plaintext)

Second, query the products with the prices equal to the max price:

SELECT
  product_name,
  price
FROM
  products
WHERE
  price = 2999.99;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      product_name       |  price
-------------------------+---------
 Samsung QN900C Neo QLED | 2999.99Code language: plaintext (plaintext)

In the second query, the condition in the WHERE clause can accept a query that returns a single value. So you can use the first query that returns a single value as a subquery in the second query like this:

SELECT
  product_name,
  price
FROM
  products
WHERE
  price = (
    SELECT
      MAX(price)
    FROM
      products
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      product_name       |  price
-------------------------+---------
 Samsung QN900C Neo QLED | 2999.99Code language: plaintext (plaintext)

Similarly, you can find the products with prices greater than the average price:

SELECT
  product_name,
  price
FROM
  products
WHERE
  price > (
    SELECT
      AVG(price)
    FROM
      products
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        |  price
----------------------------+---------
 Samsung Galaxy Z Fold 5    | 1799.99
 Apple iPhone 15 Pro Max    | 1299.99
 LG OLED TV C3              | 1999.99
 Sony Bravia XR A95K        | 2499.99
 Samsung QN900C Neo QLED    | 2999.99
 LG G3 OLED                 | 2499.99
 Sony HT-A7000 Soundbar     | 1299.99
 Dell XPS 15                | 1499.99
 HP Spectre x360            | 1399.99
 Microsoft Surface Laptop 5 | 1299.99
 Lenovo ThinkPad X1 Carbon  | 1599.99
 Apple iMac 24"             | 1299.99Code language: plaintext (plaintext)

PostgreSQL subquery in the SELECT clause #

The SELECT clause accepts a value. You can use a subquery that returns a single value in the SELECT clause.

For example, the following query uses a subquery to retrieve product name, price, and max price of products with brand_id 2:

SELECT
  product_name,
  price,
  (
    SELECT
      MAX(price)
    FROM
      products
    WHERE
      brand_id = 2
  ) max_price
FROM
  products
WHERE
  brand_id = 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      product_name       |  price  | max_price
-------------------------+---------+-----------
 Apple iPhone 15         | 1099.99 |   1299.99
 Apple iPhone 15 Pro Max | 1299.99 |   1299.99
 Apple iPad Pro 12.9     | 1099.99 |   1299.99
 Apple AirPods Pro 3     |  249.99 |   1299.99
 Apple Watch Series 9    |  399.99 |   1299.99
 Apple iMac 24"          | 1299.99 |   1299.99Code language: plaintext (plaintext)

The following example uses a subquery to return the product name and its inventory quantity:

SELECT
  p.product_name,
  (
    SELECT
      SUM(quantity)
    FROM
      inventories
    WHERE
      product_id = p.product_id
  ) AS inventory_qty
FROM
  products p
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Partial Output:

        product_name        | inventory_qty
----------------------------+---------------
 Apple AirPods Pro 3        |           180
 Apple iMac 24"             |           320
 Apple iPad Pro 12.9        |           170
 Apple iPhone 15            |           150
 Apple iPhone 15 Pro Max    |           140
...Code language: plaintext (plaintext)

PostgreSQL subquery in the FROM clause #

You can use a subquery that returns a result set (rows & columns) in the FROM clause.

For example, the following statement uses a subquery in the FROM clause to return the average number of products by all brands:

SELECT
  AVG(product_count)
FROM
  (
    SELECT
      brand_id,
      COUNT(*) product_count
    FROM
      products
    GROUP BY
      brand_id
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

        avg
--------------------
 2.5000000000000000
(1 row)Code language: plaintext (plaintext)

The subquery uses a GROUP BY clause with a COUNT function to return a result set that includes brand_id and product count:

SELECT
  brand_id,
  count(*) product_count
FROM
  products
GROUP BY
  brand_idCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 brand_id | product_count
----------+---------------
        9 |             1
        5 |             3
        4 |             1
       10 |             1
        6 |             2
        2 |             5
        7 |             3
        1 |             5
        8 |             1Code language: plaintext (plaintext)

The outer query uses the AVG() function to calculate the average product count for all brands.

PostgreSQL subquery in the JOIN clause #

The JOIN clause accepts a result set, a collection of rows and columns.

The subquery must return a result set to use a subquery in the JOIN clause. Additionally, it must include columns for joining with the table in the outer query.

The following statement uses a subquery in the JOIN clause to retrieve products with an inventory quantity greater than 300:

SELECT
  product_name,
  quantity
FROM
  products
  JOIN (
    SELECT
      product_id,
      quantity
    FROM
      inventories
    WHERE
      quantity > 300
  ) USING (product_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       product_name        | quantity
---------------------------+----------
 Lenovo ThinkPad X1 Carbon |      310
 Apple iMac 24"            |      320
 Dell Inspiron 27          |      330Code language: plaintext (plaintext)

First, the subquery retrieves product_id and quantity from the inventories table with a quantity greater than 300:

SELECT
  product_id,
  quantity
FROM
  inventories
WHERE
  quantity > 300Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | quantity
------------+----------
         23 |      310
         24 |      320
         25 |      330Code language: plaintext (plaintext)

Second, the outer query joins the products table with the above result set based on the values in the product_id column.

Summary #

A subquery is a query nested within another query.
A query that contains a subquery is called an outer query.
Use a subquery in the SELECT, FROM, JOIN, and WHEREclauses of the SELECT statement.

Quiz #

Was this tutorial helpful ?