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:
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