PostgreSQL Subquery

Subqueries in PostgreSQL are a powerful tool for querying data from one or more tables based on the result of another query. They offer a flexible way to retrieve and manipulate data within SQL statements, providing developers with a wide range of possibilities for data analysis and manipulation. 

In this article, we'll explore the fundamentals of PostgreSQL Subquery, their syntax, common use cases, and best practices for optimizing their performance.

Understanding PostgreSQL Subquery

A subquery, also known as a nested query or inner query, is a query nested within another SQL statement. It can appear in various parts of a , , , or statement, serving different purposes depending on its context. Subqueries can return a single value, a single row, multiple rows, or even a result set, which can then be used by the outer query to further filter, manipulate, or join data.

Syntax of PostgreSQL Subquery

The syntax of a subquery in PostgreSQL generally follows this structure:

SELECT column1, column2, ...
FROM table_name
WHERE condition_operator (SELECT column1, column2, ...
                          FROM subquery_table_name
                          WHERE subquery_condition);

• clause: This part specifies the columns you want to retrieve from the main table . You can list multiple columns separated by commas (column1, column2, ...).
• clause: Specifies the main table from which you are retrieving data (in this case, . This is the primary source of the data for your query.
• clause: This is the condition that must be met for a row to be included in the final result set. It can involve columns from the main table or be influenced by the results of the subquery.
• Condition operator: This operator connects the main query with the subquery. Common operators include , , , , , , etc. The choice of the operator depends on the condition you want to enforce.
• Subquery: This is a nested query enclosed in parentheses. It operates on a separate table and has its own conditions . The result of the subquery is used by the condition operator in the main query's WHERE clause.

Let's consider a practical example:

-- Retrieve employees from the 'employees' table
SELECT employee_id, first_name, last_name
FROM employees
-- Where the salary is greater than the average salary of the 'salaries' table
WHERE salary > (SELECT AVG(salary) FROM salaries);

Here is an example output, assuming hypothetical data:

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 101         | John       | Doe       |
| 102         | Jane       | Smith     |
| 103         | Alice      | Johnson   |
| ...         | ...        | ...       |
+-------------+------------+-----------+

In this example, the subquery calculates the average salary from the table, and the main query retrieves employees from the table whose salary is greater than this average.

This is just a simple illustration, and subqueries can be used in various scenarios to filter, aggregate, or compare data from different tables within a SQL query.

Types of PostgreSQL Subquery

There are several types of subqueries that you can use to perform various operations on your data. These subqueries can be classified based on where they are used within a SQL statement and their purpose. Here are the main types of PostgreSQL subqueries:

PostgreSQL Scalar Subquery

A scalar subquery is a specific type of subquery that returns a single scalar value, which is a single data point rather than a set of rows or columns. Scalar subqueries are commonly used in scenarios where you need to retrieve a single value to be used in comparison or calculation within the main query

Consider a scenario where you want to retrieve employees from the "employees" table whose salary is greater than the average salary in the "salaries" table. You can use a scalar subquery for this purpose:

-- Retrieve employees with salary greater than the average salary
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM salaries);

This will produce a result set like the following:

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 101         | John       | Doe       | 60000  |
| 102         | Jane       | Smith     | 65000  |
| 103         | Alice      | Johnson   | 70000  |
| ...         | ...        | ...       | ...    |
+-------------+------------+-----------+--------+

In this example, the subquery returns a single scalar value, which is the average salary. The main query then compares the salary of each employee with this scalar value and retrieves those whose salary is greater.

PostgreSQL Correlated Subquery

A correlated subquery in PostgreSQL is a type of subquery where the inner query references one or more columns from the outer query. Unlike a non-correlated subquery, which can be executed independently of the outer query, a correlated subquery relies on values from the outer query to produce its results. This type of subquery is useful for performing row-by-row comparisons or calculations. 

Consider a scenario where you want to retrieve employees from the "employees" table whose salary is greater than the average salary in their respective departments. You can use a correlated subquery for this purpose:

-- Retrieve employees with salary greater than department average
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees outer_table
WHERE salary > (
    SELECT AVG(salary)
    FROM employees inner_table
    WHERE outer_table.department_id = inner_table.department_id
);

This will produce a result set like the following:

+-------------+------------+-----------+--------+---------------+
| employee_id | first_name | last_name | salary | department_id |
+-------------+------------+-----------+--------+---------------+
| 101         | John       | Doe       | 60000  | 1             |
| 102         | Jane       | Smith     | 65000  | 1             |
| 103         | Alice      | Johnson   | 70000  | 2             |
| ...         | ...        | ...       | ...    | ...           |
+-------------+------------+-----------+--------+---------------+

In this example, the correlated subquery compares the salary of each employee with the average salary in their department. The correlation is achieved by referencing the department_id column in both the outer and inner queries.

PostgreSQL Subquery in FROM Clause

A subquery can be used in the clause to create a temporary result set, essentially treating the subquery as a derived table. This allows you to perform operations on the result set of the subquery before using it in the main query. Let's explore the concept of using a subquery in the clause with examples.

Consider a scenario where you want to find the average salary for each department and then retrieve employees with salaries above this departmental average:

-- Retrieve employees with salary above department average
SELECT employee_id, first_name, last_name, salary, department_id
FROM (
    SELECT AVG(salary) AS avg_salary, department_id
    FROM employees
    GROUP BY department_id
) AS department_avg
JOIN employees ON employees.salary > department_avg.avg_salary
              AND employees.department_id = department_avg.department_id;

This will produce a result set like the following:

+-------------+------------+-----------+--------+---------------+
| employee_id | first_name | last_name | salary | department_id |
+-------------+------------+-----------+--------+---------------+
| 101         | John       | Doe       | 60000  | 1             |
| 102         | Jane       | Smith     | 65000  | 1             |
| 103         | Alice      | Johnson   | 70000  | 2             |
| ...         | ...        | ...       | ...    | ...           |
+-------------+------------+-----------+--------+---------------+

In this example, the subquery calculates the average salary  for each department. The main query then joins this subquery result with the "employees" table, filtering employees with salaries above their departmental average.

PostgreSQL Subquery with EXISTS

The keyword is used to check for the existence of rows in a subquery. The subquery returns a Boolean value (either true or false) based on whether any rows are returned by the subquery. This is often used in the clause of a main query to filter rows based on the existence of related records in another table. Let's explore the concept of using with Subquery in PostgreSQL with examples.

Consider a scenario where you want to retrieve a list of employees who have at least one project assigned to them:

-- Retrieve employees with at least one project
SELECT employee_id, first_name, last_name
FROM employees
WHERE EXISTS (
    SELECT 1
    FROM projects
    WHERE projects.employee_id = employees.employee_id
);

This will produce a result set like the following:

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 101         | John       | Doe       |
| 102         | Jane       | Smith     |
| 103         | Alice      | Johnson   |
| ...         | ...        | ...       |
+-------------+------------+-----------+

In this example, the subquery checks for the existence of at least one row in the "projects" table where the in the main query matches the employee_id in the subquery. The condition is true if such rows exist, and those employees are included in the result set.

PostgreSQL Nested Subquery

A nested subquery refers to the use of one or more subqueries within another subquery. This allows for more complex and layered logic in SQL queries, as the result of an inner subquery can be used as a condition or value in an outer subquery. 

Consider a scenario where you want to retrieve employees who have salaries greater than the average salary in their department, and the department itself has a total budget greater than a certain threshold:

-- Retrieve employees with salaries above department average and high-budget departments
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = (
        SELECT department_id
        FROM departments
        WHERE budget > 100000
    )
);

This will produce a result set like the following:

+-------------+------------+-----------+--------+---------------+
| employee_id | first_name | last_name | salary | department_id |
+-------------+------------+-----------+--------+---------------+
| 101         | John       | Doe       | 60000  | 1             |
| 102         | Jane       | Smith     | 65000  | 1             |
| 103         | Alice      | Johnson   | 70000  | 2             |
| ...         | ...        | ...       | ...    | ...           |
+-------------+------------+-----------+--------+---------------+

In this example, there are two levels of nested subqueries. The innermost subquery retrieves the from the "departments" table where the budget is greater than 100,000. The middle subquery then calculates the average salary for employees in that department. Finally, the outer query retrieves employees with salaries greater than this calculated average.

PostgreSQL Inline Subquery 

An inline subquery, also known as a derived table or subquery in the clause, is used to create a temporary result set that can be referenced in the main query. This type of subquery is particularly useful when you need to perform operations on a subset of data before incorporating it into the main query.

Consider a scenario where you want to retrieve employees along with the maximum salary in their respective departments:

-- Retrieve employees along with the maximum salary in their departments
SELECT employee_id, first_name, last_name, salary, department_id, max_salary
FROM employees
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) AS department_max_salary ON employees.department_id = department_max_salary.department_id;

This will produce a result set like the following:

+-------------+------------+-----------+--------+---------------+-------------+
| employee_id | first_name | last_name | salary | department_id | max_salary  |
+-------------+------------+-----------+--------+---------------+-------------+
| 101         | John       | Doe       | 60000  | 1             | 70000       |
| 102         | Jane       | Smith     | 65000  | 1             | 70000       |
| 103         | Alice      | Johnson   | 70000  | 2             | 75000       |
| ...         | ...        | ...       | ...    | ...           | ...         |
+-------------+------------+-----------+--------+---------------+-------------+

In this example, the inline subquery calculates the maximum salary for each department. The main query then joins this subquery result with the "employees" table based on the department_id.

PostgreSQL NOT EXISTS Subquery

The subquery is used to check for the absence of rows that satisfy a certain condition. It returns true if no rows are returned by the subquery, indicating that the specified condition does not exist in the given context. This can be particularly useful for filtering records based on the non-existence of related records in another table.

Consider a scenario where you want to retrieve employees who do not have any projects assigned to them:

-- Retrieve employees with no assigned projects
SELECT employee_id, first_name, last_name
FROM employees
WHERE NOT EXISTS (
    SELECT 1
    FROM projects
    WHERE projects.employee_id = employees.employee_id
);

This will produce a result set like the following:

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
| 101         | John       | Doe       |
| 102         | Jane       | Smith     |
| 103         | Alice      | Johnson   |
| ...         | ...        | ...       |
+-------------+------------+-----------+

In this example, the subquery checks for the absence of any rows in the "projects" table where the in the main query matches the employee_id in the subquery. The result includes employees who do not have any projects assigned to them.

PostgreSQL Subquery with ANY/ALL

The and operators are used in conjunction with subqueries to compare a value with a set of values returned by the subquery. These operators are often employed in scenarios where you want to check a condition against multiple values in a subquery. 

Using ANY with Subquery:

The ANY operator returns true if at least one of the values in the set of values returned by the subquery satisfies the specified condition.

Consider a scenario where you want to retrieve employees with salaries higher than the highest salary in the "salaries" table:

-- Retrieve employees with salaries higher than the highest salary in the 'salaries' table
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM salaries);

This will produce a result set like the following:

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 101         | John       | Doe       | 65000  |
| 102         | Jane       | Smith     | 70000  |
| 103         | Alice      | Johnson   | 75000  |
| ...         | ...        | ...       | ...    |
+-------------+------------+-----------+--------+

In this example, the operator is used to compare the salary of each employee with the set of salaries returned by the subquery, and the condition is true if the employee's salary is higher than at least one salary in the subquery result.

Using ALL with Subquery:

The operator returns true if the specified condition is true for all values in the set of values returned by the subquery.Consider a scenario where you want to retrieve employees with salaries higher than all the salaries in the "salaries" table:

-- Retrieve employees with salaries higher than all salaries in the 'salaries' table
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM salaries);

This will produce a result set like the following:

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 101         | John       | Doe       | 75000  |
| 102         | Jane       | Smith     | 80000  |
| 103         | Alice      | Johnson   | 85000  |
| ...         | ...        | ...       | ...    |
+-------------+------------+-----------+--------+

In this example, the operator is used to compare the salary of each employee with the set of salaries returned by the subquery, and the condition is true only if the employee's salary is higher than all salaries in the subquery result.

PostgreSQL Aggregated Subquery

An aggregated subquery involves the use of aggregate functions (e.g., COUNT, SUM, AVG, MAX, MIN) within a subquery. These subqueries are employed to calculate a summary value from a subset of data, and the result is often used in the main query for further analysis or filtering.

Consider a scenario where you want to retrieve departments with more than five employees:

-- Retrieve departments with more than five employees
SELECT department_id, department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5
);

This will produce a result set like the following:

+---------------+----------------+
| department_id | department_name|
+---------------+----------------+
| 1             | Sales          |
| 2             | Marketing      |
| 3             | Engineering    |
| ...           | ...            |
+---------------+----------------+

In this example, the aggregated subquery calculates the number of employees  for each department in the "employees" table. The main query then retrieves departments where the count of employees is greater than 5.

Best Practices and Performance Considerations

While subqueries provide flexibility, improper use can impact performance. Here are some best practices:

1. Optimize Subqueries: Ensure that your subqueries are well-optimized. Use indexes, analyze execution plans, and leverage PostgreSQL's query optimization features.
2. Us Wisely: can be more efficient than using or in certain scenarios, especially when you only need to check for the existence of records.
3. Correlation Impact: Be mindful of correlated subqueries as they may lead to slower performance. Analyze whether alternatives such as or can achieve the same result more efficiently.
4. Limit Subquery Results: Whenever possible, limit the number of rows returned by a subquery. This can significantly enhance query performance.

Conclusion

PostgreSQL Subquery is a valuable tool for SQL developers, offering a way to write more modular, readable, and expressive queries. Understanding the types of subqueries and their optimal use cases empowers developers to harness the full potential of this feature while keeping performance considerations in check. As you embark on your journey with PostgreSQL, remember that a well-structured and optimized database can be the key to unlocking the true power of subqueries.