How to Use Subqueries in PostgreSQL

How do I write and optimize a subquery in PostgreSQL?

A subquery is a SELECT statement nested inside another SQL statement to provide a derived set of rows or a single value.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is a subquery in PostgreSQL?

A subquery is an inner SELECT that supplies a value set to an outer query. PostgreSQL evaluates the inner query first, then feeds the result to the outer query.

Why choose a subquery over a JOIN?

Subqueries shine when the derived rows need further aggregation, when filtering on aggregated values, or when readability outweighs performance. For simple row combinations, a JOIN is usually faster.

What is the basic syntax of a subquery?

Place the inner SELECT inside parentheses. It can appear in the SELECT list, FROM clause, or WHERE clause. Use aliases to reference subquery columns.

How do I filter rows with a subquery?

Use WHERE column IN (SELECT ...) or EXISTS (SELECT 1 FROM ... WHERE ...). PostgreSQL returns only rows matching the inner query conditions.

How can I return aggregated data using a subquery?

Wrap an aggregate SELECT in the FROM clause, give it an alias, then join or filter on the aggregated columns. This isolates aggregation logic from the outer query.

Best practices for subqueries

Index columns referenced in correlated subqueries, alias everything, and limit the returned columns to reduce planning time. Consider WITH queries (CTEs) for clarity.

Common mistakes with subqueries

Returning multiple columns where one is expected, or forgetting to correlate the subquery, both cause errors or unintended Cartesian products.

Why How to Use Subqueries in PostgreSQL is important

How to Use Subqueries in PostgreSQL Example Usage


-- Customers who spent more than $1,000 in total
SELECT c.id, c.name, spend.total_spent
FROM (
    SELECT o.customer_id, SUM(o.total_amount) AS total_spent
    FROM Orders o
    GROUP BY o.customer_id
) spend
JOIN Customers c ON c.id = spend.customer_id
WHERE spend.total_spent > 1000;

How to Use Subqueries in PostgreSQL Syntax


-- In the SELECT list
SELECT c.id, c.name,
       (SELECT COUNT(*)
        FROM Orders o
        WHERE o.customer_id = c.id) AS order_count
FROM Customers c;

-- In the FROM clause
SELECT sub.customer_id, sub.total_spent
FROM (
    SELECT o.customer_id, SUM(o.total_amount) AS total_spent
    FROM Orders o
    GROUP BY o.customer_id
) sub
JOIN Customers c ON c.id = sub.customer_id;

-- In the WHERE clause
SELECT *
FROM Products p
WHERE p.id IN (
    SELECT oi.product_id
    FROM OrderItems oi
    WHERE oi.quantity > 5
);

Common Mistakes

Mistake: Returning multiple columns where a single scalar value is expected (e.g., WHERE price = (SELECT id, price FROM Products)). Fix: Return one column or use IN with multiple values.
Mistake: Unindexed columns in correlated subqueries, causing slow performance. Fix: Create indexes on the columns used in the inner WHERE clause.