How to Use Subqueries in PostgreSQL

There was a point early in my SQL journey where every query I wrote was flat — one SELECT, one FROM, maybe a WHERE clause. Then I hit a wall: I needed to find every customer whose total spending was above the average spending of all customers. I couldn’t just write that in one simple line. That’s when I discovered subqueries, and honestly, it felt like unlocking a new tier of SQL.

In this guide, I’ll break down exactly what subqueries are, the different places you can use them, and the real-world patterns I rely on when writing PostgreSQL queries.

What Is a Subquery?

A subquery — also called a nested query or inner query — is simply a SELECT statement placed inside another SQL statement. PostgreSQL evaluates the inner query first (in most cases), then uses its result to complete the outer query.

Subqueries can appear in several places:

• Inside a WHERE clause
• Inside a FROM clause (as a derived table)
• Inside a SELECT list (as a scalar subquery)
• Inside INSERT, UPDATE, or DELETE statements

Let’s set up a working example:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    amount NUMERIC(10,2),
    order_date DATE
);

Subqueries in the WHERE Clause

This is the most common place I use subqueries. Let’s find every customer who has placed at least one order over $500:

SELECT name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE amount > 500
);

The inner query runs first, returning a list of customer_id values from orders over $500. The outer query then checks which customers match that list.

Using a Subquery With a Scalar Comparison

If your subquery returns a single value, you can compare it directly with =, >, <, etc.

SELECT name
FROM customers c
WHERE (
    SELECT SUM(amount) FROM orders WHERE customer_id = c.id
) > (
    SELECT AVG(amount) FROM orders
);

This is the exact problem I mentioned at the start — finding customers whose total spend beats the average order amount overall. Note this is a correlated subquery (more on that below), since the inner query references c.id from the outer query.

EXISTS and NOT EXISTS

EXISTS checks whether a subquery returns any rows at all — it doesn’t care about the actual values, just whether rows exist.

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

This finds every customer who has placed at least one order. I use SELECT 1 by convention here since the actual selected value doesn’t matter — only the existence of a matching row does.

The opposite, NOT EXISTS, finds customers with no orders at all:

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

I tend to prefer EXISTS/NOT EXISTS over IN/NOT IN for performance reasons, especially when the subquery might return NULL values — NOT IN has a nasty gotcha with NULLs that I’ll cover in the troubleshooting section.

Correlated vs Non-Correlated Subqueries

This distinction matters a lot for both understanding and performance.

A non-correlated subquery runs independently of the outer query — it doesn’t reference any columns from the outer query, so PostgreSQL can compute it once.

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

A correlated subquery references a column from the outer query, meaning it effectively runs once per row of the outer query.

SELECT name FROM customers c
WHERE (
    SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id
) > 3;

Correlated subqueries are powerful but can be slower on large tables since PostgreSQL may need to re-evaluate the inner query for every outer row (though the planner often optimizes this into a join internally). It’s worth checking EXPLAIN ANALYZE when performance matters.

Subqueries in the FROM Clause (Derived Tables)

You can treat a subquery as if it were a table, aliasing it for the outer query to reference:

SELECT country, AVG(total_spent) AS avg_spent_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.country, c.id
) AS customer_totals
GROUP BY country;

This is a two-step aggregation: first calculate total spend per customer, then average those totals per country. Trying to do this in one flat query without a subquery would actually give the wrong result, because you can’t average an aggregate directly without first computing it as its own step.

Scalar Subqueries in the SELECT List

You can also embed a subquery directly in your column list, as long as it returns exactly one value per row:

SELECT
    c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS total_orders
FROM customers c;

This adds an total_orders column showing how many orders each customer has placed. I like this pattern for quick reporting queries, though for anything beyond a couple of scalar subqueries, a LEFT JOIN with GROUP BY is usually more efficient.

Subqueries With INSERT, UPDATE, and DELETE

Subqueries aren’t limited to SELECT statements.

Insert using a subquery:

INSERT INTO vip_customers (customer_id, name)
SELECT id, name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 10000
);

Update using a subquery:

UPDATE customers
SET country = 'Unknown'
WHERE id NOT IN (
    SELECT customer_id FROM orders
);

Delete using a subquery:

DELETE FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Notice that extra WHERE customer_id IS NOT NULL in the delete example — that’s intentional, and I’ll explain exactly why in the next section, because skipping it is a mistake that has bitten me before.

Common Table Expressions (CTEs) as an Alternative

While not technically a “subquery” in the traditional sense, Common Table Expressions using WITH solve similar problems and are often more readable:

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, ct.total_spent
FROM customers c
JOIN customer_totals ct ON ct.customer_id = c.id
WHERE ct.total_spent > 5000;

I reach for CTEs instead of nested subqueries whenever a query starts getting hard to read, since they let you name each logical step instead of stacking parentheses.

ANY, ALL, and SOME With Subqueries

Beyond IN and EXISTS, PostgreSQL supports comparing a value against every row a subquery returns using ANY, ALL, and SOME (which is just an alias for ANY).

SELECT name FROM customers c
WHERE (
    SELECT SUM(amount) FROM orders WHERE customer_id = c.id
) > ALL (
    SELECT SUM(amount) FROM orders GROUP BY customer_id HAVING customer_id != c.id
);

That particular example is a bit contrived, but the pattern is genuinely useful in simpler forms:

-- Find products priced higher than any product in the 'Clearance' category
SELECT name, price FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category = 'Clearance'
);

> ALL means “greater than every value returned” (effectively greater than the maximum). > ANY means “greater than at least one value returned” (effectively greater than the minimum). I find these read a little less intuitively than an equivalent MAX()/MIN() subquery, so I usually only reach for ANY/ALL when the comparison operator itself needs to vary, since IN is really just shorthand for = ANY.

LATERAL Subqueries — Per-Row Subqueries With Context

A more advanced pattern worth knowing: a LATERAL subquery in the FROM clause can reference columns from preceding tables in the same query — something a normal subquery in FROM can’t do.

SELECT c.name, recent.amount, recent.order_date
FROM customers c
CROSS JOIN LATERAL (
    SELECT amount, order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 3
) AS recent;

This gives me each customer’s three most recent orders — a “top N per group” query that’s genuinely awkward to express any other way in standard SQL. Without LATERAL, a plain subquery in FROM has no way to know which customer it should be filtering by, since it can’t see c.id from the outer query. I reach for LATERAL specifically for this “top N per group” pattern; it comes up constantly in dashboards that show “most recent activity per user” or “best-selling product per category.”

Subqueries vs Window Functions — Knowing When to Switch

A lot of problems that used to require a correlated subquery can now be solved more efficiently with window functions, and it’s worth knowing when to reach for which. Take this correlated subquery, which ranks each customer’s orders by amount within their own order history:

SELECT
    o.id,
    o.customer_id,
    o.amount,
    (
        SELECT COUNT(*) FROM orders o2
        WHERE o2.customer_id = o.customer_id AND o2.amount > o.amount
    ) + 1 AS rank_within_customer
FROM orders o;

This works, but it’s re-scanning the orders table once per row to compute each rank — genuinely expensive on a large table. The window function equivalent does the same job in a single pass:

SELECT
    id,
    customer_id,
    amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_within_customer
FROM orders;

Both queries produce the same ranking, but the window function version is typically dramatically faster on large datasets, since PostgreSQL can compute it in one coordinated pass rather than repeating a full subquery scan for every single row. My rule of thumb: if a correlated subquery is essentially recalculating something “per row, relative to a group that row belongs to” — a rank, a running total, a comparison to a group average — it’s almost always worth checking whether a window function expresses the same logic more efficiently before shipping the subquery version to production.

Common Use Cases

• Filtering based on aggregated conditions (customers above average spend).
• Existence checks (customers with no orders, products never sold).
• Two-step aggregations that can’t be expressed in a single GROUP BY.
• Data migration/cleanup using INSERT ... SELECT or conditional DELETE.
• Per-row calculated columns using scalar subqueries.

Troubleshooting Tips

The NOT IN with NULLs trap. This is the single most common subquery bug I see. If the subquery in a NOT IN returns even one NULL value, the entire outer query returns zero rows — silently, with no error.

-- Dangerous if customer_id can be NULL in orders
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If orders.customer_id contains any NULLs, this returns nothing at all, which confuses people because it “looks correct.” Always filter NULLs explicitly, or better, use NOT EXISTS instead, which doesn’t have this issue:

SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Subquery returns more than one row. If you use = with a subquery expecting a single value but it returns multiple rows, PostgreSQL throws an error: “more than one row returned by a subquery used as an expression.” Switch to IN or add a LIMIT 1 if you truly only want one match.

Slow correlated subqueries. If a correlated subquery is slow on a large table, try rewriting it as a JOIN with GROUP BY, or a CTE. Always check EXPLAIN ANALYZE to see whether PostgreSQL is doing a nested loop that’s more expensive than a hash join would be.

Subquery in FROM without an alias. PostgreSQL requires every derived table (subquery in FROM) to have an alias:

-- This will error
SELECT * FROM (SELECT * FROM orders);

-- This works
SELECT * FROM (SELECT * FROM orders) AS o;

Best Practices I Follow

1. Prefer EXISTS/NOT EXISTS over IN/NOT IN when checking existence, especially if NULLs are possible.
2. Use CTEs for readability once a query has more than one or two nested subqueries.
3. Check EXPLAIN ANALYZE on any correlated subquery running against a large table.
4. Always guard against NULLs in subqueries used with NOT IN.
5. Consider rewriting subqueries as joins when performance matters — PostgreSQL’s planner can often optimize joins better than deeply nested subqueries.
6. Alias every derived table in the FROM clause.

Frequently Asked Questions

What’s the difference between a subquery and a JOIN? A subquery nests one query inside another and often returns a filtered list or single value. A JOIN combines rows from two tables directly. Many subqueries can be rewritten as joins, sometimes with better performance, but subqueries are often clearer for existence checks or aggregated comparisons.

Are subqueries slower than joins? Not necessarily — PostgreSQL’s query planner often rewrites subqueries into joins internally. However, correlated subqueries can be slower in some cases, so it’s worth testing both approaches with EXPLAIN ANALYZE on your actual data.

Can I use ORDER BY inside a subquery? Generally, ORDER BY inside a subquery has no effect unless combined with LIMIT, since the outer query doesn’t preserve subquery row order otherwise.

What’s a scalar subquery? A subquery that returns exactly one row and one column — a single value — which can be used anywhere a single value is expected, like in a SELECT list or a comparison.

Can subqueries be nested multiple levels deep? Yes, PostgreSQL supports subqueries within subqueries, though for readability I’d recommend switching to CTEs once you go beyond two levels.

Can I use a subquery in an ORDER BY clause? Yes, as long as it returns a single value per row of the outer query — this is less common but valid, for example ordering customers by a scalar subquery computing their total spend.

Do subqueries in PostgreSQL support LIMIT and OFFSET? Yes, a subquery can include its own LIMIT/OFFSET, which is especially useful in a FROM-clause subquery when you want to pre-filter to a specific subset of rows (like “top 10 per some criteria”) before joining or aggregating further in the outer query.

Wrapping Up

Subqueries are one of those tools that, once they click, completely change how you approach complex data problems. They let you break a big question into smaller, answerable pieces — “what’s the average?”, “does this exist?”, “what’s the total per group?” — and then combine those pieces into one final answer. Start with simple WHERE ... IN subqueries, get comfortable with EXISTS, and once your queries start nesting deeply, don’t hesitate to switch to CTEs for clarity. Your future self, reading this query six months from now, will thank you.