In relational databases like PostgreSQL, various tables are linked with each other to manage a large amount of data efficiently. While working with relational databases you may encounter a situation where you have to select numerous records from different tables and then filter them based on specific criteria. This process may require writing a large amount of unnecessary and redundant code. In such scenarios, the “Subqueries” can be proved very useful.

This write-up will explain the usage of the Postgres “Subqueries” using suitable examples.

What is a Subquery in Postgres?

A subquery in Postgres is a query within another query. It can also be named as an inner select, nested query, or inner query. While the query that contains a subquery is referred to as an outer query, main query, or outer select.

The following guidelines must be considered before using a subquery:

- A subquery must be wrapped/enclosed inside the parenthesis.
- Subqueries can be utilized with the FROM, SELECT, DELETE, UPDATE, INSERT, or WHERE clauses.
- In the WHERE clause, conditional operators like =, >, <, >=, <=, IN, or EXISTS can be used.
- The ORDER BY can be utilized with the main/outer query, however, it can’t be utilized with the inner/subquery.
- A subquery that retrieves multiple records can only be utilized with multi-value operators, such as IN, ANY/SOME, EXISTS, ALL, etc.
- The BETWEEN operator can’t be used with the subquery; however, it can be used inside the subquery.

How to Write or Define a Subquery in Postgres?

In Postgres, a subquery is mostly used with the WHERE clause. So, you must follow the below-provided syntax to write a subquery:

SELECT column_name [, col_name ]
FROM table
WHERE expression OPERATOR
(SELECT col_name [, col_name ]
FROM table
[WHERE]
)

In the above syntax:

- The query specified within the parenthesis represents the inner query.
- The inner/subquery will execute before the outer/main query.
- The result of the inner query will be used as a condition in the WHERE clause of the outer query.

How to Use a Subquery in Postgres?

This section describes the usage of the Postgres subquery with:

- WHERE Clause
- IN Operator
- EXIST Operator

Example 1: What is the Need for the Subqueries in Postgres?

Assume we have to fetch the details of employees having salaries above the average salary. For this, first, we must find the average salary. After that, we can utilize the average salary to filter the employees having salaries greater than the average salary.

The following snippet depicts the data of the sample table:

To find the average employee salary, utilize the AVG() function as follows:

SELECT AVG(emp_sal)
FROM emp_bio;

The output depicts that the average employee salary is “44000”. Utilize the following command to get the employees with an average salary greater than or equal to “44000”:

SELECT e_id, emp_name, emp_sal
FROM emp_bio
WHERE emp_sal >= 44000;

Executing the above query will filter the employees' data based on the average salary:

The result signifies that the employees' data has been successfully filtered. However, the code is not optimized/efficient, as it involves two steps. To wrap both steps into one, use the subquery.

Example 2: Using Subquery With a Postgres WHERE Clause

The following code explains the usage of the Postgres subquery with the WHERE clause:

SELECT e_id, emp_name, emp_sal
FROM emp_bio
WHERE emp_sal >= (
SELECT AVG (emp_sal)
FROM emp_bio );

The above query will be executed in the following sequence:

- First, the INNER SELECT (subquery) will be executed.
- Next, the results retrieved by the INNER SELECT will be passed to the OUTER SELECT.
- Finally, the OUTER SELECT will be executed based on the results of the INNER SELECT.

The output shows that the subquery successfully retrieves the filtered data.

Example 3: Using Subquery With a Postgres IN Operator/Clause

Use the subquery with the IN operator to retrieve zero or more rows from the INNER SELECT. Consider the following query for a profound understanding:

SELECT e_id, emp_salary
FROM emp_details
WHERE dpt_id IN 
(SELECT dpt_id
FROM dpt_info
WHERE dpt_name = 'Writing Department');

In the above query:

- The “emp_details” and “dpt_info” tables are used that are linked with each other via a foreign key.
- The inner query will filter the records based on the “Writing Department”.
- The IN operator will check the list of values returned by the nested SELECT.
- Finally, the query retrieves the employee’s id and salary whose department is “Writing Department”:

The output demonstrates that the subquery retrieves the filtered records.

Example 4: Using Subquery With the EXIST Operator

The subquery can be used with the EXIST operator to evaluate if it retrieves any rows.

SELECT e_id, emp_salary
FROM emp_details 
WHERE EXISTS 
(SELECT 1
FROM dpt_info
WHERE dpt_info.dpt_id = emp_details.dpt_id
);

- The above query will work as an INNER JOIN on the dpt_id column.
- The INNER SELECT will check if a department has at least one employee using the “dpt_info.dpt_id = emp_details.dpt_id” condition.
- The INNER SELECT will retrieve true if the specified condition is valid.
- Finally, the ids and salaries of those employees will be retrieved who met the specified criteria.

This is how a subquery works with the EXISTS operator.

Conclusion

A subquery in PostgreSQL is nothing more than a query within another query. It is also called a nested query, inner query, or inner select. While the query that contains a subquery is referred to as an outer query, main query, or outer select. In Postgres, the inner SELECT or the subquery executes before the main or outer query. The results retrieved by the inner/nested query are used as a condition in the WHERE clause of the outer/main query. This article explained different use cases of the Postgres subqueries using suitable examples.