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:
1 | sudo su - Postgres |
This command will prompt you for a password. Once you enter it, type the following command:
1 | psql your_username -h 127.0.0.1 -d your_database |
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:
1 | CREATE DATABASE your_database_name; |
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:
1 2 3 4 5 6 7 | CREATE TABLE employee( id SERIAL PRIMARY KEY NOT NULL, Name TEXT, Age INT NOT NULL, Address TEXT NOT NULL, SALARY REAL ); |
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:
1 | INSERT INTO TABLE_NAME(col1, col2) VALUES(val1, val2); |
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:
1 2 | SELECT * FROM employee WHERE id IN (SELECT id FROM employee WHERE age <= 29); |
The result of this query would look like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | id | name | age | address | salary -----+----------+-----+----------------+-------- 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 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:
1 2 | DELETE FROM employee WHERE salary > 25000; |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started