18 - Subqueries in PostgreSQL

Introduction to Subqueries in PostgreSQL

Subqueries, also known as nested queries or inner queries, are a powerful feature in PostgreSQL that allows you to embed one query within another. They are used to retrieve data that will be used in the main query, enabling you to create more complex and flexible SQL statements. In this guide, we’ll delve into the concept of subqueries, their types, and how they are used in PostgreSQL.

Understanding Subqueries

A subquery is a query that is nested inside another query, often referred to as the “main query” or the “outer query.” The result of the subquery is used as input for the main query, helping you retrieve data more selectively, perform comparisons, and make complex data manipulations.

Subqueries can appear in various parts of an SQL statement, including the SELECT, FROM, and WHERE clauses. Their primary purpose is to provide dynamic values or data for the main query, making SQL statements more adaptable and powerful.

Types of Subqueries

PostgreSQL supports several types of subqueries based on their usage and the part of the SQL statement in which they appear. Here are some common types:

Scalar Subquery

A scalar subquery is a subquery that returns a single value. It can be used in any part of the SQL statement where a single value is expected. For example, to retrieve the names of all employees whose salary is higher than the average salary:


SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In this example, the subquery (SELECT AVG(salary) FROM employees) calculates the average salary, and the result is used for comparison in the WHERE clause.

Row Subquery

A row subquery returns a single row as its result. It is often used in the FROM clause to create virtual tables that can be joined with other tables. For example, to find all orders for products with a price above a certain threshold:


SELECT orders.*
FROM orders
JOIN (SELECT product_id FROM products WHERE price > 100) AS expensive_products
ON orders.product_id = expensive_products.product_id;

In this case, the subquery (SELECT product_id FROM products WHERE price > 100) generates a virtual table that contains product IDs meeting the price condition, which is then joined with the ‘orders’ table.

Table Subquery

A table subquery, also known as a correlated subquery, is a subquery that can reference columns from the outer query. It is used to retrieve data related to each row of the outer query. For instance, to find all employees whose salary is greater than the average salary in their department:


SELECT employee_name, salary, department_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Here, the subquery is correlated with the outer query and filters employees based on the department they belong to.

Using Subqueries for Complex Queries

Subqueries can be used to build more complex and sophisticated SQL statements. They are particularly useful in situations where you need to compare data from multiple tables, perform conditional logic, or filter records based on dynamic values. Here are a few practical examples:

Subquery in the SELECT Clause

A subquery in the SELECT clause can be used to retrieve a single value that is displayed alongside each row of the result set. For example, to find the highest salary among all employees and display it in a column next to each employee:


SELECT employee_name, salary, (SELECT MAX(salary) FROM employees) AS highest_salary
FROM employees;

This query adds a column to the result set, showing the highest salary among all employees for each employee record.

Subquery in the FROM Clause

Using a subquery in the FROM clause allows you to create a temporary table that can be used in the main query. This is especially useful when you need to filter data or perform calculations on a subset of records. For example, to find the average salary of employees in the Sales department:


SELECT department_id, AVG(salary) AS average_salary
FROM (SELECT * FROM employees WHERE department_id = 'Sales') AS sales_employees
GROUP BY department_id;

This query creates a temporary table ‘sales_employees’ that contains only employees from the Sales department and calculates the average salary for that department.

Subquery in the WHERE Clause

Using a subquery in the WHERE clause allows you to filter records based on the results of the subquery. For example, to find all customers who have made purchases exceeding a certain amount:


SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 1000);

This query filters customers based on the subquery’s result, which lists customers with orders exceeding $1000.

Conclusion

Subqueries in PostgreSQL are a powerful tool for enhancing the capabilities of your SQL statements. They allow you to retrieve data dynamically, perform advanced comparisons, and create complex queries that involve multiple tables and conditions. By mastering the use of subqueries, you can build more adaptable and insightful database queries to meet a wide range of data analysis and reporting needs.

18 – Subqueries in PostgreSQL