How to Perform the PostgreSQL Subquery

Introduction

When you’re querying for data in a PostgreSQL database, there are times when the criteria you need to specify in your query are quite complex. In some cases, you may need to make use of subqueries. A PostgreSQL subquery is simply a query that’s enclosed within another query. Using subqueries allows you to query the results set of another query: The nested query is executed first, and the results of that inner query are then passed to the enclosing query. Subqueries are typically delimited by the SQL FROM clause. In this article, we’ll look at some examples of how to use a PostgreSQL subquery to retrieve the data you need.

Prerequisites

Before we begin our discussion of PostgreSQL subqueries, it’s best to review any prerequisites that might be necessary for this tutorial. In this case, there’s only one requirement: PostgreSQL must be installed on your machine. If you’re not sure whether it’s installed, use the psql -V command in the terminal. This command returns the version of PostgreSQL that’s running on your machine.

Connect to Postgres console

We’ll be using the psql command-line interface to execute our PostgreSQL subquery examples. To connect to PostgreSQL, we’ll use the command syntax shown below:

This command will prompt you for a password. Once you enter it, type the following command:

This command will allow you to enter psql and specify both the username and the database that you’ll be using for your session.

Create a PostgreSQL database

In order to create and execute our subqueries, we’ll need a database and table available. Let’s create a database and table to use in our examples; we’ll also insert records into our table so that we can perform queries against it.

To create a PostgreSQL database, use the following SQL statement:

After creating this new database, we can connect to it using the command \c in the psql command-line interface.

Create a PostgreSQL table

Next, we’ll create a table that we can use to perform complex SELECT queries. We’ll use the CREATE TABLE statement shown below:

Insert value in table

We’ll need to insert some records into our table in order to perform queries. The following syntax can be used to insert as many records as you wish:

PostgreSQL subquery example

A subquery is also known as a nested query. The data returned by a subquery is more restricted in scope, because the outer, or enclosing, query is only querying the result set returned from the inner query.

Let’s look at the following PostgreSQL subquery example:

The result of this query would look like this:

Subqueries can also be used in DELETE, INSERT and UPDATE statements, and they’re typically used in conjunction with the FROM and WHERE clauses. The syntax of the nested query in these statements is essentially the same as it is in a SELECT statement.

Let’s look at another example. In this statement, we’re deleting any records from the employee table where the salary is greater than 25,000:

Conclusion

Not all criteria for a SQL query can be expressed in a simple statement. When you need to create a more complex PostgreSQL query, a subquery can be a powerful and efficient tool. In this article, we looked at a few different examples of PostgreSQL subqueries. With these examples and our step-by-step instructions, you’ll be able to create subqueries to use in your own PostgreSQL implementation.