Subqueries for Complex Filtering

You’ve been writing queries for a while, and they work — until the day you need to answer a question that involves multiple tables, an aggregate, or a condition that depends on another subset of rows. Suddenly your WHERE clause feels clumsy, and you’re either fetching too much data or juggling multiple queries in your application code. That’s the pain point this lesson solves: write subqueries for complex filtering in PostgreSQL, a skill that turns tangled joins and multi-step logic into clean, readable SQL.

We’ll break down the core concept behind subqueries, walk through step-by-step examples, and give you hands-on practice with real-world scenarios. By the end, you’ll know when to use a subquery versus a JOIN or CTE, and you’ll be able to write filtering logic that feels like a superpower, not a puzzle.

---

The problem this lesson solves

Complex filtering often means a condition that depends on data from another query. Maybe you need to find customers who have placed an order above the average order value, or products that have never been sold. These aren’t simple equality checks — they require a dynamic comparison against a set or an aggregated value.

Without subqueries, you’d have to: - Run multiple queries in your application code, then combine the results manually. - Write convoluted JOINs that duplicate logic and hurt readability. - Use temporary tables for every intermediate step, which clutters your schema.

Example pain: You want to list all customers who have spent more than the average customer. Without a subquery, you’d first run SELECT AVG(total_spent) ..., then hardcode that number into a second query. That’s brittle and slow. Subqueries let you do it in one statement, keeping the logic in the database where it belongs.

Why this matters now: As your data grows, so does the need for precise, efficient filtering. Subqueries are a foundational tool that unlocks advanced reporting, dashboarding, and application features without over-engineering.

---

Core concept / mental model

Think of a subquery as a question within a question. You ask the database: "Give me customers whose total spending is greater than the average — and by the way, the average is calculated from the customers table itself."

The outer query is the main question; the inner query (the subquery) provides the context or the comparison set. The subquery runs first (in most cases), and its result is used by the outer query.

Key terms: - Subquery – a SELECT statement nested inside another query. - Outer query – the main SELECT that contains the subquery. - Correlated subquery – a subquery that references a column from the outer query, like a loop for each row.

Types of subqueries for filtering

Visualizing the flow

Imagine a query like:

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

1. The inner query runs first: it finds all customer_id values from orders with amount > 100.
2. That produces a list (say, [1, 3, 7]).
3. The outer query then filters customers whose id is in that list.

The subquery is like a filter within a filter — you're narrowing down the universe of rows step by step.

---

How it works step by step

Let’s build from simple to complex. We’ll use a fictional e-commerce schema for our examples:

-- Sample schema (run once)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

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

INSERT INTO customers (name, city) VALUES
    ('Alice', 'New York'),
    ('Bob', 'Austin'),
    ('Carol', 'Chicago'),
    ('Dave', 'Denver');

INSERT INTO orders (customer_id, amount, order_date) VALUES
    (1, 150, '2025-01-10'),
    (1, 80, '2025-01-11'),
    (2, 200, '2025-01-12'),
    (3, 50, '2025-01-13'),
    (4, 300, '2025-01-14');

Step 1: Identify the condition that needs a subquery

Ask: "Does this filter depend on data outside the current table?" If yes, a subquery is likely the tool.

Example: Find customers who have placed at least one order above $100. That requires checking against the orders table, not just customers.

Step 2: Write the inner query first

Think backward: what do you need to compare against? For the above, the inner query is:

SELECT DISTINCT customer_id FROM orders WHERE amount > 100;

Step 3: Wrap it in the outer query

Use the operator that fits your need: IN, EXISTS, =, >, <, etc.

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

Step 4: Refine with correlated subqueries when needed

If the inner query needs to reference a column from the outer query, you’re in correlated territory. Example: find customers whose total order amount exceeds the average total of all customers.

SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
HAVING SUM(o.amount) > (SELECT AVG(total) FROM
    (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) AS totals);

This is getting complex — that’s where a CTE might be better, but subqueries still work.

---

Hands-on walkthrough

Let’s play with our schema using three practical examples that cover common patterns.

Example 1: Filter with IN (table subquery)

Goal: Names of customers who have placed an order over $100.

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

Output:

name
-----
Alice
Bob
Dave

It’s clear, and you don’t need a JOIN because you only need customer names.

Example 2: Scalar subquery with comparison

Goal: Customers with an order amount higher than the average amount of all orders.

SELECT name, amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.amount > (SELECT AVG(amount) FROM orders);

Output:

name  | amount
------+--------
Bob   |    200
Dave  |    300

Here the subquery returns a single number (the average), so you can use > directly.

Example 3: Correlated subquery with EXISTS

Goal: Customers who have placed no orders at all (the classic "left join with null" alternative).

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

Output:

name
-----
Carol

The correlation (c.id referenced inside) makes the inner query run for each customer until it finds a match or not. EXISTS stops early for efficiency.

Pro tip: NOT EXISTS is often faster and clearer than NOT IN when the subquery could return NULL values — NOT IN yields no rows if a NULL appears, which is a classic gotcha we’ll discuss shortly.

---

Compare options / when to choose what

Subqueries aren’t the only way to do complex filtering. It’s important to know the trade-offs.

How to choose

• Use a table subquery with IN when you need to filter based on a set of values from a single column.
• Use a scalar subquery when you need to compare against a single aggregate value.
• Use EXISTS / NOT EXISTS when you care about presence of rows, not the data itself — it’s often the most efficient.
• Reach for a CTE when the subquery logic gets too nested to read easily — your future self will thank you.

Real-world rule: If you find yourself nesting more than two levels, consider rewriting as a CTE or breaking it into multiple queries with indexes.

---

Troubleshooting & edge cases

Even experienced developers hit these walls. Let’s tackle the most common ones.

1. NOT IN with NULL values breaks silently

Problem: If your subquery returns a NULL in the column, NOT IN will return zero rows because NULL causes the comparison to be UNKNOWN (not TRUE).

Fix: Use NOT EXISTS instead, which handles NULLs gracefully.

-- Avoid this
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

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

2. Performance: correlated subqueries can be slow

Symptom: Queries time out on large tables because the inner query runs for every row of the outer query.

Diagnosis: Use EXPLAIN ANALYZE to see if the correlated subquery triggers a nested loop without an index.

Fix: Ensure the referenced columns (like customer_id) are indexed. Or rewrite to a JOIN or derived table that can use a hash join.

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

Add an index if missing:

CREATE INDEX idx_orders_customer ON orders(customer_id);

3. Subquery returns more than one row when you expect one

Symptom: Error: more than one row returned by a subquery used as an expression.

Cause: You used a scalar operator (=, >) with a subquery that returns multiple rows.

Fix: Use IN, ANY, or EXISTS depending on intent, or add LIMIT 1 if you truly expect one row.

-- Wrong if subquery returns multiple
SELECT * FROM customers WHERE id = (SELECT customer_id FROM orders);

-- Correct with IN
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

4. Unintended duplicate rows

Symptom: You see duplicate customer names in your results.

Cause: The subquery returns duplicate values (e.g., same customer with multiple orders), and you used IN without DISTINCT.

Fix: Use SELECT DISTINCT inside the subquery, or switch to EXISTS which stops at the first match.

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

---

What you learned & what's next

You’ve now got a solid grasp on Write subqueries for complex filtering in PostgreSQL. Let’s recap the core ideas:

• Subqueries let you nest a query inside another query to perform dynamic comparisons.
• There are four main types: scalar, row, table, and correlated — each serving a different filtering need.
• Step-by-step, you built subqueries from simple IN to correlated EXISTS patterns.
• You compared subqueries with JOINs and CTEs and know when to choose each.
• You learned to avoid the NULL trap with NOT IN, and how to fix performance with indexing.

These skills directly support your next lesson in the PostgreSQL track, where you’ll likely explore more advanced joins or window functions. Subqueries are the foundation for those — they’re the secret sauce behind many complex reports and analytics.

Keep this cheat sheet in mind: Use subqueries when you need a self-contained filter; use CTEs when you need to break up logic; use EXISTS for presence checks.

Now go ahead and practice on your own data. Write a few subqueries that answer business questions you’ve been avoiding. You’ll be surprised how quickly they become second nature.