How to Use the Inner Query in PostgreSQL

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
2
3
SELECT column_name FROM
TABLE_NAME WHERE column_name
OPERATOR(SELECT column_name FROM TABLE_NAME [WHERE]);

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:

1
SELECT * FROM students;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 id  |       name       | grade | STATUS
-----+------------------+-------+--------
  32 | Allen James      |    89 | Passed
 371 | Jerry Lawrel     |    92 | Passed
  51 | Fred Smith       |    89 | Passed
 481 | Wayne Rosing     |    91 | Passed
 261 | Samuel Davis     |    92 | Passed
 621 | Philip Wilson    |    95 | Passed
 782 | Jay Leno         |    93 | Passed
 891 | Gerry Starr      |    94 | Passed
 312 | Michelle Rogers  |    85 | Passed
 322 | Alfred Roosevelt |    91 | Passed
 536 | Tommy Jones      |    74 | Failed
 162 | Kelly Ripa       |    89 | Passed
 361 | MAX Heindel      |    95 | Passed
1
SELECT * FROM scholar;
1
2
3
4
5
6
7
8
9
10
 id  |     name      | grade | STATUS
-----+---------------+-------+--------
 146 | Joan Rivers   |    91 |
 371 | Jerry Lawrel  |     0 | Passed
 481 | Wayne Rosing  |     0 | Passed
 261 | Samuel Davis  |     0 | Passed
 621 | Philip Wilson |     0 | Passed
 782 | Jay Leno      |     0 | Passed
 891 | Gerry Starr   |     0 | Passed
 361 | MAX Heindel   |     0 | Passed

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:

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM student
WHERE name IN (SELECT name FROM scholar);
 id  |     name      | grade | STATUS
-----+---------------+-------+--------
 371 | Jerry Lawrel  |    92 | Passed
 481 | Wayne Rosing  |    91 | Passed
 261 | Samuel Davis  |    92 | Passed
 621 | Philip Wilson |    95 | Passed
 782 | Jay Leno      |    93 | Passed
 891 | Gerry Starr   |    94 | Passed
 361 | MAX Heindel   |    95 | Passed

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:

1
2
3
UPDATE scholar
SET grade = (SELECT grade FROM student WHERE student.name = scholar.name)
WHERE grade = 0;

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

1
2
3
4
5
6
7
8
9
10
 id  |     name      | grade | STATUS
-----+---------------+-------+--------
 146 | Joan Rivers   |    91 |
 371 | Jerry Lawrel  |    92 | Passed
 481 | Wayne Rosing  |    91 | Passed
 261 | Samuel Davis  |    92 | Passed
 621 | Philip Wilson |    95 | Passed
 782 | Jay Leno      |    93 | Passed
 891 | Gerry Starr   |    94 | Passed
 361 | MAX Heindel   |    95 | Passed

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.

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.