How to Use the Inner Query in PostgreSQL

Introduction

When you’re formulating a PostgreSQL query, there will be times when a simple query just isn’t adequate to provide the information you need. More complex query conditions sometimes require the use of a second query nested within the original query. This is known as a sub-query, or inner query, in PostgreSQL. In this article, we’ll look at some different ways you can use an inner query in PostgreSQL and review some examples that illustrate the concept of a nested query.

Prerequisites

Before we dive into our examples using an inner query in PostgreSQL, let’s go over a few prerequisites that are necessary for this tutorial:

• First, make sure to have PostgreSQL installed on your computer. You’ll need it to follow along with the inner query examples presented in this article.
• It’s helpful to have at least some degree of PostgreSQL knowledge in order to get the most out of the instructions.

PostgreSQL Inner Query Syntax

The following is an example of a typical inner query you may use:

Notice how the second SELECT statement is nested within the outer SELECT statement.

PostgreSQL Inner Query Examples

We’ll need to create two tables to use in our examples. You can populate these tables with the same values that we’re using, or you can use your own. Our table is named students:

The following SELECT statement makes use of an inner query. We use the sub-query to select the name column from the student table where the value of name is found in the scholar table:

Update inner query

Nested queries aren’t just for SELECT statements. You can also update records using an inner query in PostgreSQL.

The following statement will update the records in the grade column of the scholar table:

Let’s display the contents of the scholar table again:

We can see that the UPDATE operation was successful and the values of grade were modified.

Conclusion

When you’re performing a SELECT or UPDATE statement, there are times when you need to retrieve data from another table in order to define the conditions for querying or updating the main table in the statement. Using an inner query in PostgreSQL can help you accomplish the task at hand and provide the information you need. In this article, we reviewed several examples that show how to use an inner query in PostgreSQL. If you’ve followed along with these examples, you’ll be able to incorporate inner queries in your own PostgreSQL statements.