How to Perform the PostgreSQL Subquery
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.
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:
id SERIAL PRIMARY KEY NOT NULL,
Age INT NOT NULL,
Address TEXT NOT NULL,
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
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:
WHERE id IN (SELECT id FROM employee WHERE age <= 29);
The result of this query would look like this:
1 | Tresa | 25 | Livingston | 25000
14 | Cherry | 22 | Webb | 25013
15 | Kattie | 27 | Maricopa | 25014
16 | Lilli | 28 | Warren | 25015
18 | Barbra | 29 | Wayne | 25017
49 | Roselle | 29 | Philadelphia | 25048
50 | Samira | 28 | NEW York | 25049
51 | Margart | 27 | Coffee | 25050
58 | Kristel | 28 | Providence | 25057
59 | Vincenza | 28 | Montgomery | 25058
60 | Elouise | 28 | Providence | 25059
61 | Venita | 28 | Middlesex | 25060
62 | Kasandra | 28 | Travis | 25061
63 | Xochitl | 27 | Douglas | 25062
64 | Maile | 29 | Milwaukee | 25063
65 | Krissy | 26 | NEW York | 25064
66 | Pete | 26 | Anchorage | 25065
67 | Linn | 25 | Erie | 25066
68 | Paris | 22 | Anne Arundel | 25067
69 | Wynell | 24 | Ada | 25068
70 | Quentin | 22 | San Francisco | 25069
Subqueries can also be used in
UPDATE statements, and they’re typically used in conjunction with the
WHERE clauses. The syntax of the nested query in these statements is essentially the same as it is in a
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:
WHERE salary > 25000;
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started