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:

sudo su - Postgres

This command will prompt you for a password. Once you enter it, type the following command:

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:

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:

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:

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:

SELECT * FROM employee
    WHERE id IN (SELECT id FROM employee WHERE age <= 29);

The result of this query would look like this:

 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:

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

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.