Using Subqueries in PostgreSQL

Introduction

Subqueries in PostgreSQL are queries nested inside another query. They allow you to retrieve intermediate results that can be used by the main query. Subqueries are powerful tools for filtering, calculating, and aggregating data efficiently.

This article explores different types of subqueries in PostgreSQL, including:

• Scalar subqueries
• Table subqueries
• Correlated subqueries
• Subqueries with EXISTS

Understanding Subqueries in PostgreSQL

A subquery is a query enclosed in parentheses and used within another SQL statement. Subqueries can be used in SELECT, FROM, and WHERE clauses to perform complex data operations.

1. Scalar Subqueries

Scalar subqueries return a single value and are often used in SELECT statements.

Example:

SELECT reservation_id, total_rental_price,
       (SELECT AVG(total_rental_price) FROM Reservations) AS avg_price
FROM Reservations;

Query result :

carrentaldb=# SELECT reservation_id, total_rental_price,
       (SELECT AVG(total_rental_price) FROM Reservations) AS avg_price
FROM Reservations;
 reservation_id | total_rental_price |      avg_price       
----------------+--------------------+----------------------
              1 |             180.00 | 195.9000000000000000
              2 |              80.00 | 195.9000000000000000
              3 |             126.00 | 195.9000000000000000
              4 |             190.00 | 195.9000000000000000
              5 |             200.00 | 195.9000000000000000
              6 |             190.00 | 195.9000000000000000
              7 |              78.00 | 195.9000000000000000
              8 |             240.00 | 195.9000000000000000
              9 |             500.00 | 195.9000000000000000
             10 |             175.00 | 195.9000000000000000
(10 rows)

This query retrieves each reservation’s price along with the average rental price.

2. Table Subqueries

Table subqueries return multiple rows and columns and are often used in the FROM clause.

Example:

SELECT * FROM (SELECT reservation_id, total_rental_price FROM Reservations WHERE status = 'Confirmed') AS confirmed_reservations;

Query result :

carrentaldb=# SELECT * FROM (SELECT reservation_id, total_rental_price 
FROM Reservations 
WHERE status = 'Confirmed') AS confirmed_reservations;
 reservation_id | total_rental_price 
----------------+--------------------
              1 |             180.00
              5 |             200.00
              8 |             240.00
(3 rows)

This query creates a temporary table of confirmed reservations and selects all its records.

3. Correlated Subqueries

Correlated subqueries reference columns from the outer query and are evaluated once per row.

Example:

SELECT reservation_id, total_rental_price
FROM Reservations r1
WHERE total_rental_price > (SELECT AVG(total_rental_price) FROM Reservations r2 WHERE r1.status = r2.status);

carrentaldb=# SELECT reservation_id, total_rental_price
FROM Reservations r1
WHERE total_rental_price > (SELECT AVG(total_rental_price) FROM Reservations r2 WHERE r1.status = r2.status);
 reservation_id | total_rental_price 
----------------+--------------------
              2 |              80.00
              4 |             190.00
              8 |             240.00
              9 |             500.00
(4 rows)

This query retrieves reservations where the total rental price is above the average price for the same status.

4. Subqueries with EXISTS

The EXISTS operator checks whether a subquery returns any rows.

Example:

SELECT * FROM Reservations r
WHERE EXISTS (SELECT 1 FROM Reservations WHERE status = 'Completed' AND r.reservation_id = reservation_id);

carrentaldb=# SELECT * FROM Reservations r
WHERE EXISTS (SELECT 1 FROM Reservations WHERE status = 'Completed' AND r.reservation_id = reservation_id);
 reservation_id | customer_id | car_id | employee_id | pickup_date | return_date | total_rental_price |  status   
----------------+-------------+--------+-------------+-------------+-------------+--------------------+-----------
              3 |           3 |      5 |           3 | 2025-03-15  | 2025-03-18  |             126.00 | Completed
              6 |           6 |      7 |           6 | 2025-06-01  | 2025-06-03  |             190.00 | Completed
              9 |           9 |      6 |           9 | 2025-09-25  | 2025-09-30  |             500.00 | Completed
(3 rows)

This query selects all reservations that exist in the list of completed reservations.

Conclusion

Subqueries in PostgreSQL enable complex data retrieval by nesting queries inside one another. By using scalar, table, correlated subqueries, and the EXISTS operator, you can optimize queries and retrieve meaningful insights efficiently. Mastering subqueries will enhance your ability to work with PostgreSQL effectively.