What is Nested Select Statement in PostgreSQL?

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 explore 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.

How to Write or Define a Subquery in Postgres?

Here’s the syntax for nested select statements in PostgreSQL:

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition);

Explanation:

• 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:

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:

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:

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:

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 versatile 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.