Postgres Subqueries

Introduction

When you’re creating a SQL statement in PostgreSQL, you may find that you need to embed another SQL query within your statement. These nested, or embedded, queries are known as subqueries, and they provide an effective way to get the data you need from a SQL statement. In this article, we’ll take a closer look at Postgres subqueries and review some examples that demonstrate how to use them.

Prerequisite

Before you begin this tutorial, make sure you’ve installed and configured the following on your machine:

Basic PostgreSQL knowledge is also required to be able to follow along with the tutorial.

What is a Postgres Subquery?

As the name suggests, a Postgres query is a query nested inside another SQL query or statement. The subquery returns data that is then used in the main query as part of the conditions specified in the WHERE clause.

Creating Sample Dataset

In this section, we’ll create a sample dataset that we can use in our examples. We’ll need to log in to the Postgres shell to create the database and table.

  • We can create our database using the following command:
1
create database product
  • We can then create tables for the database with the following structure:
1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS stock (
    id SERIAL PRIMARY KEY,
    category_id CHARACTER VARYING(100),
    product_name CHARACTER VARYING(100),
    sku CHARACTER VARYING(100),
    description CHARACTER VARYING(100),
    price INT,
    quantity INT

);

Finally, we insert sample records:

1
2
3
4
5
6
7
INSERT INTO stock (id,category_id, product_name, sku, description, price, quantity)
VALUES
   (1,'M01','Monitor 22inch ','HW020', 'Computer monitor 22inch', 300, 100),
   (2,'M02','Thermal Paste','HW040', 'CPU thermal paste', 2, 130),
   (3,'M03','Crimping Tool','HW021', 'Networking tool', 14, 10),
   (4,'M01','Keyboard','HW098', 'Computer Keyboard', 3, 25),
   (5,'M01','SSD 250GB','HW033', 'Solid State Drive', 24, 60);

Now, let’s create another table named ‘category’ using the following structure:

1
2
3
4
CREATE TABLE IF NOT EXISTS category (
    category_id CHARACTER VARYING(100),
    category_name CHARACTER VARYING(100)
);

We’ll insert sample records into this table too:

1
2
3
4
5
INSERT INTO category (category_id,category_name)
VALUES
('M01','Pheriperals'),
('M02', 'Miscellaneous Materials'),
('M03','Networking');

The two tables should look like the following:

STOCK TABLE

1
2
3
4
5
6
7
8
 id | category_id |  product_name   |  sku  |       description       | price | quantity
----+-------------+-----------------+-------+-------------------------+-------+----------
  1 | M01         | Monitor 22inch  | HW020 | Computer monitor 22inch |   300 |      100
  2 | M02         | Thermal Paste   | HW040 | CPU thermal paste       |     2 |      130
  3 | M03         | Crimping Tool   | HW021 | Networking tool         |    14 |       10
  4 | M01         | Keyboard        | HW098 | Computer Keyboard       |     3 |       25
  5 | M01         | SSD 250GB       | HW033 | Solid State Drive       |    24 |       60
(5 rows)

CATEGORY TABLE

1
2
3
4
5
6
 category_id |      category_name
-------------+-------------------------
 M01         | Pheriperals
 M02         | Miscellaneous Materials
 M03         | Networking
(3 rows)

Postgres Subqueries Example

Now that we have created our sample dataset, we can look at some examples that illustrate how to use Postgres subqueries:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
   id,
   product_name,
   price
FROM
   stock
WHERE
   price > (
      SELECT
         AVG (price)
      FROM
         stock
   );

Let’s discuss this query in a bit more detail.

  • First, notice the query that’s enclosed in parentheses:
1
2
3
4
SELECT
         AVG (price)
      FROM
         stock
  • Postgres executes the queries in order, starting from the innermost subquery.
  • After processing the subquery, the result will be returned to the outer query.
  • Finally, the outer query will be executed.

The output should look something like this:

1
2
3
4
 id |  product_name   | price
----+-----------------+-------
  1 | Monitor 22inch  |   300
(1 row)

Let’s look at another example where we use a subquery in a FROM clause:

1
2
3
4
5
6
SELECT stock.product_name, the_subquery.category_name
FROM stock,
 (SELECT category.category_id, category.category_name, COUNT(category_id) AS total
  FROM category
  GROUP BY category.category_id, category.category_name) the_subquery
WHERE the_subquery.category_id = stock.category_id;
1
2
3
(SELECT category.category_id, category.category_name, COUNT(category_id) AS total
  FROM category
  GROUP BY category.category_id, category.category_name) the_subquery

In the above query, we placed the subquery under the alias ‘the_subquery’. This alias will be used as a reference to the subquery.

The output should look something like this:

1
2
3
4
5
6
7
8
  product_name   |      category_name
-----------------+-------------------------
 SSD 250GB       | Pheriperals
 Keyboard        | Pheriperals
 Monitor 22inch  | Pheriperals
 Thermal Paste   | Miscellaneous Materials
 Crimping Tool   | Networking
(5 rows)

Conclusion

If you need to construct a more complex query to obtain the data you need, a Postgres subquery may be necessary to accomplish the task. Subqueries are nested inside the outer query; the results returned from them are then used as part of the WHERE clause in the main query. In this article, we explained how to use a Postgres subquery and looked at some examples of SQL statements that include subqueries. With these examples and explanations, you’ll have no problem implementing subqueries in your own database queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.