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