How to Use Subqueries in PostgreSQL

How do I write a subquery in PostgreSQL?

A subquery is a SELECT statement nested inside another SQL command to supply rows or values to the outer query.

Description

What is a subquery?

A subquery is an inner SELECT statement that returns a result set used by an outer query. PostgreSQL treats the subquery as a temporary table or single value, depending on context.

When should I use a subquery over a JOIN?

Choose a subquery when you need an aggregate per row, want to filter with EXISTS/IN, or isolate logic for readability. Prefer JOINs when you need sets merged into one result for performance.

How do I write a scalar subquery?

Place the subquery where a single value is expected. PostgreSQL enforces that only one row is returned, otherwise it errors.

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

How do I filter rows with EXISTS?

Use EXISTS to keep rows that have at least one matching row in the subquery. PostgreSQL stops scanning once it finds a match, making it efficient.

SELECT * FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.customer_id = c.id AND o.total_amount > 100);

Can I use subqueries in the FROM clause?

Yes. Wrapping a SELECT in parentheses and giving it an alias turns it into a derived table you can join or filter.

SELECT t.customer_id, t.total_spent FROM ( SELECT o.customer_id, SUM(o.total_amount) AS total_spent FROM Orders o GROUP BY o.customer_id ) AS t WHERE t.total_spent > 500;

Best practices for subqueries?

1) Alias every derived table. 2) Index columns used in correlated predicates. 3) Avoid unnecessary correlated subqueries—rewrite as JOINs when possible for speed.

How do I debug slow subqueries?

Run EXPLAIN ANALYZE on the full query. Check for sequential scans inside the subquery and add indexes or rewrite to JOIN/CTE.

Why How to Use Subqueries in PostgreSQL is important

How to Use Subqueries in PostgreSQL Example Usage


-- Find customers who spent more than the average order total
SELECT c.id, c.name
FROM Customers c
WHERE (
    SELECT SUM(o.total_amount)
    FROM Orders o
    WHERE o.customer_id = c.id
) > (
    SELECT AVG(total_amount)
    FROM Orders
);

How to Use Subqueries in PostgreSQL Syntax


-- Scalar subquery in SELECT list
SELECT name,
       (SELECT SUM(total_amount)
        FROM Orders o
        WHERE o.customer_id = c.id) AS lifetime_value
FROM Customers c;

-- Subquery in WHERE with IN
SELECT *
FROM Products p
WHERE p.id IN (
    SELECT oi.product_id
    FROM OrderItems oi
    WHERE oi.quantity >= 5
);

-- EXISTS correlated subquery
SELECT *
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.id
      AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

-- Derived table in FROM
SELECT t.product_id, t.units_sold
FROM (
    SELECT oi.product_id, SUM(oi.quantity) AS units_sold
    FROM OrderItems oi
    GROUP BY oi.product_id
) AS t
ORDER BY t.units_sold DESC;

Common Mistakes

Returning multiple rows in a scalar subquery. Fix by aggregating or adding LIMIT 1 so PostgreSQL receives only one value.
Using correlated subqueries when a JOIN performs better. Rewrite heavy correlated queries as JOINs or CTEs to reduce repeated scans.

Frequently Asked Questions (FAQs)

Is a CTE faster than a subquery?

Performance is usually identical because PostgreSQL inlines non-recursive CTEs. Focus on readability unless the CTE is marked MATERIALIZED.

Can I UPDATE using a subquery?

Yes. Place the subquery in the FROM clause or use a correlated WHERE. Example: UPDATE Products p SET stock = stock - t.qty FROM (SELECT ... ) t WHERE p.id = t.product_id;