What is Nested Select Statement in PostgreSQL?
Last Updated :
11 Nov, 2024
Nested select statements, also known as subqueries which is a fundamental concept in PostgreSQL and play an important role in data retrieval and manipulation. In PostgreSQL, a powerful relational database management system, the nested select statements allow for complex queries by nesting one query within another.
In this article, We will explain the concept of nested select statements in PostgreSQL by understanding the various examples and so on.
What is a Nested Select Statement?
In PostgreSQL, a Nested query isĀ a query within another PostgreSQL query and embedded within the WHERE clause. It is also known as a subquery, which is a query nested within another query.
It allows us to use the result of one query as a condition or value in another query. Nested select statements can be used in various parts of a SQL query, such as the SELECT, FROM, WHERE, and HAVING clauses.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition);
Key Terms
- SELECT column1, column2, ... specifies the columns to retrieve in the outer query.
- FROM table_name specifies the table from which to retrieve data in the outer query.
- WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition) is the nested select statement.
- column_name is the column to compare in the outer query.
- OPERATOR is a comparison operator (e.g., =, >, <, IN, EXISTS).
- (SELECT column_name FROM table_name WHERE condition) is the inner query that returns a result set.
Examples of Nested Select statement in PostgreSQL
To understandĀ What is Nested select statement in PostgreSQLĀ we need a table on which we will perform various operations and queries. Here we will consider a table called employees which containsĀ id, name, salary, and departmentĀ as Columns.

Example 1: Using Nested Select to Filter Results
Suppose we want to retrieve the names of employees who work in the 'Sales' department. We can achieve this using a nested select statement as follows:
Query:
SELECT name
FROM employees
WHERE department = (
SELECT id
FROM departments
WHERE name = 'Sales'
);
Output

Explanation:
In the above query, We retrieves the names of employees who belong to the 'Sales' department. It uses a subquery to first find the department ID for 'Sales' and then uses that ID to filter the employees table.
Example 2: Using Nested Select to Calculate Aggregate Functions
Suppose we want to find the average salary of employees in the 'Marketing' department. We can use a nested select statement with an aggregate function to achieve this
Query:
SELECT AVG(salary)
FROM employees
WHERE department = (
SELECT id
FROM departments
WHERE name = 'Marketing'
);
Output

Explanation:
In the above query, We calculates the average salary of employees in the 'Marketing' department. It uses a subquery to find the department ID for 'Marketing' and then calculates the average salary for employees in that department.
Example 3: Using Nested Select to Perform Comparison
Suppose we want to find employees whose salary is higher than the average salary in the 'Finance' department. We can use a nested select statement to achieve this:
Query:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = (
SELECT id
FROM departments
WHERE name = 'Finance'
)
);
Output

Explanation
In the above query, We retrieves the names and salaries of employees whose salaries are higher than the average salary of the 'Finance' department. It uses a subquery to calculate the average salary of the 'Finance' department and then compares each employee's salary to that average.
Example 4: Using Nested Select to Retrieve Top N Records
Suppose we want to retrieve the top 3 highest-paid employees. We can use a nested select statement with the ORDER BY and LIMIT clauses to achieve this:
Query:
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
Output

Explanation:
In the above query, We retrieves the names and salaries of the top 3 highest-paid employees. It sorts the 'employees' salaries in descending order and limits the result to the first 3 rows.
Conclusion
Overall, the nested select statements in PostgreSQL offer a flexible approach to retrieving data by embedding one query within another. These subqueries can be used in various parts of a SQL query, such as the SELECT, FROM, WHERE and HAVING clauses, providing flexibility in data retrieval. Through examples, we've learnt how nested select statements can be used to filter results, calculate aggregate functions, perform comparisons, and retrieve top records.
Similar Reads
What is Nested Select Statement in PL/SQL?
PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on. Un
4 min read
What is Nested Select Statement in MariaDB
Nested select statements, normally named subqueries, represent an advanced feature for MariaDB which enables more efficient and thorough querying of the data. Therefore, the nesting of a SELECT statement within another allows us to perform operations and filtering that could be hardly possible with
4 min read
What is Nested Select Statement in SQL Server
SQL Server is a powerful relational database management system. Sql Server is very good with its robustness and scalability. SQL Server operates as a client-server structure, imparting centralized management and scalability for huge-scale applications and enterprise-stage solutions. It offers advanc
3 min read
Nested Select Statement in MySQL
The relational databases, the ability to retrieve and manipulate data with precision is a cornerstone of effective database management. MySQL, a popular relational database management system, provides a powerful tool called the Nested SELECT statement that empowers developers to perform complex and
5 min read
MySQL INSERT INTO SELECT Statement
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. INSERT INTO SELECT statement i
5 min read
PostgreSQL - CASE Statement
In PostgreSQL, CASE statements provide a way to implement conditional logic within SQL queries. Using these statements effectively can help streamline database functions, optimize query performance, and provide targeted outputs. This guide will break down the types of CASE statements available in Po
5 min read
PostgreSQL - IF Statement
PostgreSQL IF statement is an essential tool for implementing conditional logic within SQL queries and stored procedures. It allows developers to execute different actions based on specific conditions and enhances the flexibility of database operations. In this article, we will explore various Postg
5 min read
PostgreSQL MERGE Statement
The MERGE statement in PostgreSQL is a powerful data manipulation tool introduced in PostgreSQL 15, enabling conditional INSERT, UPDATE, and DELETE operations in a single command. This feature streamlines data synchronization between tables, making it ideal for tasks such as upserts and handling tab
4 min read
PostgreSQL UPDATE Statement
The PostgreSQL UPDATE statement is an important SQL command used to modify existing data in one or more rows of a table. It allows users to update specific columns or multiple columns at once, using conditions defined in the WHERE clause. This command is highly flexible, enabling dynamic data manage
5 min read
PostgreSQL - SELECT INTO
The PostgreSQL SELECT INTO statement allows users to create a new table directly from the result set of a query. This command is ideal for duplicating or organizing data from an existing table into a new one for further analysis. SELECT INTO does not return data to the client but saves it in a new t
4 min read