Nested Subqueries as JOIN Replacements: When to Swap EXISTS, IN, and Derived Tables for Cleaner, Faster Filtering

Introduction

You've written the JOIN. It runs. The numbers look right. But something feels off — either the query is slower than it should be, or the result set is inflated with duplicate rows you have to DISTINCT away, or the logic is buried under three levels of table references that make the query painful to read six months later. You reach for DISTINCT as a band-aid. You add more conditions. The query grows. The problem is that you're using a JOIN when you actually need a filter, and those are fundamentally different operations that SQL handles very differently under the hood.

This lesson is about learning to see that distinction clearly, and then exploiting it. Specifically, we'll explore when EXISTS, IN, and derived table subqueries outperform or out-read a traditional JOIN — and when they don't. These aren't exotic tricks for interview prep. They're practical, high-leverage tools that appear constantly in production analytics, ETL pipelines, and reporting queries. Understanding the difference between "I need to join to another table" and "I need to check whether a match exists in another table" is one of the most important conceptual leaps you can make as a SQL practitioner.

By the end of this lesson, you'll be able to diagnose when a JOIN is wrong for the job, rewrite filtering logic using EXISTS, IN, and derived tables, understand the execution model differences between each approach, and make informed decisions about which tool to use based on data shape, cardinality, and readability requirements.

What you'll learn:

• Why JOINs used as filters cause correctness and performance problems
• How EXISTS works internally and when it's the fastest filter available
• The semantic and performance difference between IN with a subquery versus EXISTS
• When derived tables (inline views) give you cleaner, more composable filter logic than JOINs
• How to choose between these three approaches based on data characteristics and query intent

---

Prerequisites

You should be comfortable with SQL JOINs and understand the basic mechanics of subqueries and how they nest inside a SELECT. Familiarity with aggregate functions and GROUP BY will help you follow the more complex examples. If you want a deeper reference on EXISTS specifically after working through this lesson, see Mastering SQL EXISTS and NOT EXISTS.

---

The Root Problem: JOINs That Were Really Filters All Along

Let's start with a concrete scenario. You work at a SaaS company and you want to find all customers who have placed at least one order in the last 30 days. Here's the query that many developers write first:

SELECT DISTINCT
    c.customer_id,
    c.company_name,
    c.account_tier
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

This query works, but it has two problems. First, you need that DISTINCT because any customer who placed multiple orders in the last 30 days will appear multiple times in the result. The JOIN produces one row per matching order, not one row per customer. Second, and more subtle: this query is actually doing more work than it needs to. It's fetching order data, joining it to customer data row-by-row (according to the execution plan), and then discarding most of what it fetched. The DISTINCT is a signal that you're cleaning up the mess that the JOIN made.

What you really meant to say was: give me every customer where at least one order exists that meets this condition. That's a membership test. SQL has dedicated syntax for exactly that.

SELECT
    c.customer_id,
    c.company_name,
    c.account_tier
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
);

No DISTINCT. No duplicate rows. No order data leaking into your result set. The query says exactly what it means, and most query planners will execute it more efficiently because EXISTS can short-circuit — the moment it finds a single matching order, it stops looking and moves to the next customer.

This is the core of everything we'll explore in this lesson. But to use these tools well, you need to understand how each one actually behaves.

---

Understanding EXISTS: The Short-Circuit Filter

EXISTS is fundamentally different from a JOIN in one important way: it doesn't care about column values in the subquery at all. It only cares whether the subquery returns any rows. That's why you'll see SELECT 1 or SELECT * inside EXISTS subqueries — the specific columns you select don't matter. What matters is whether the WHERE clause in the inner query produces a non-empty result.

Here's the execution model. For each row in the outer query, the database engine evaluates the EXISTS subquery. If the subquery returns at least one row, the outer row passes the filter. If not, it's excluded. This is a correlated subquery — the inner query references columns from the outer query (o.customer_id = c.customer_id in our example), which means it runs once per outer row. That sounds expensive, but the short-circuit behavior often makes it fast in practice.

Let's look at a more complex real-world case. You want to find all products that have been returned at least once, but your returns table doesn't directly reference products — it references order_items, which references orders, which reference products.

-- The JOIN approach (fragile and produces duplicates)
SELECT DISTINCT
    p.product_id,
    p.product_name,
    p.category
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN order_returns r ON oi.order_item_id = r.order_item_id;

This is already getting messy. Every return adds a duplicate product row. Now with EXISTS:

SELECT
    p.product_id,
    p.product_name,
    p.category
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items oi
    INNER JOIN order_returns r ON oi.order_item_id = r.order_item_id
    WHERE oi.product_id = p.product_id
);

The EXISTS subquery can still use JOINs internally — there's nothing wrong with that. The point is that the result of the subquery is used only as a membership test, not as data source for the outer query. You get clean rows from products with no multiplication, no DISTINCT needed.

NOT EXISTS: The Powerful Anti-Join

The inverse — NOT EXISTS — is where this pattern really earns its keep. Anti-joins (finding rows in A that have no match in B) are awkward with traditional JOINs. You need a LEFT JOIN followed by a WHERE right_side IS NULL check. NOT EXISTS expresses the same idea more directly.

Consider: find all customers who have never placed an order.

-- LEFT JOIN anti-join pattern
SELECT c.customer_id, c.company_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- NOT EXISTS pattern
SELECT c.customer_id, c.company_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Both are correct. The NOT EXISTS version is often preferred because it's harder to accidentally corrupt: you can't forget to check for NULL on the right side, and it handles NULL values in the join key more safely. NULL handling in SQL is subtle, and the LEFT JOIN / IS NULL pattern has edge cases when the join key itself might be NULL — NOT EXISTS avoids those pitfalls.

---

Understanding IN with Subqueries: Power and Peril

IN with a subquery looks syntactically similar to EXISTS but behaves quite differently under the hood.

SELECT
    p.product_id,
    p.product_name
FROM products p
WHERE p.category_id IN (
    SELECT c.category_id
    FROM categories c
    WHERE c.department = 'Electronics'
);

Here, the subquery runs first (in most query planners), produces a list of category_id values, and then the outer query filters products against that list. This is called a non-correlated subquery — the inner query doesn't reference any columns from the outer query, so it can run once and cache its result.

When the subquery result set is small, this is extremely efficient. The query planner may actually convert it to a hash join or semi-join internally, making it equivalent in performance to a well-written JOIN. But when the subquery result grows large, IN can become slow — you're comparing every outer row against a potentially large in-memory list.

The Null Trap in NOT IN

The difference between NOT EXISTS and NOT IN is one of the most important correctness issues in SQL. Consider this:

-- This returns what you expect
SELECT product_id FROM products
WHERE product_id NOT EXISTS (
    SELECT 1 FROM discontinued_products
    WHERE discontinued_products.product_id = products.product_id
);
-- (Syntax error for illustration — you'd write NOT EXISTS correctly as shown before)

Now the NOT IN version:

SELECT product_id FROM products
WHERE product_id NOT IN (
    SELECT product_id FROM discontinued_products
);

This looks fine. But if discontinued_products.product_id contains even a single NULL row, the entire query returns zero results. Here's why: SQL three-valued logic means that 5 NOT IN (1, 2, NULL) evaluates to UNKNOWN, not TRUE. The database can't confirm that 5 is not in the list when the list contains an unknown value. So every row fails the filter.

-- Safe version with NOT IN
SELECT product_id FROM products
WHERE product_id NOT IN (
    SELECT product_id FROM discontinued_products
    WHERE product_id IS NOT NULL  -- Critical guard
);

-- Or just use NOT EXISTS, which handles NULLs correctly by design
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_products dp
    WHERE dp.product_id = p.product_id
);

When IN Is the Right Choice

Despite the NULL trap, IN with a subquery is the right tool when:

1. The subquery is non-correlated — it can run once and cache results
2. You're testing against a set from a different part of the schema — e.g., checking if a value is in a lookup or configuration table
3. You need clean syntax for a list of IDs returned from application logic (though that's more IN (1, 2, 3) than a subquery)
4. Readability matters more than raw performance on small datasets

Here's a practical use case: find all employees assigned to projects that belong to high-priority portfolios.

SELECT
    e.employee_id,
    e.full_name,
    e.department
FROM employees e
WHERE e.employee_id IN (
    SELECT pa.employee_id
    FROM project_assignments pa
    INNER JOIN projects pr ON pa.project_id = pr.project_id
    WHERE pr.portfolio_id IN (
        SELECT portfolio_id
        FROM portfolios
        WHERE priority_level = 'HIGH'
    )
);

This nests IN inside IN, which is legal and often readable. Each subquery executes once, caches its result, and the outer queries filter against those cached sets. For reasonably sized lookup tables, this performs well and reads like the business logic it represents: employees assigned to projects that belong to high-priority portfolios.

---

Derived Tables: Subqueries in the FROM Clause

A derived table (also called an inline view) is a subquery that appears in the FROM clause and acts as a virtual table. It's fundamentally different from EXISTS and IN — it actually materializes a result set that you then query against, which makes it more powerful but also heavier.

SELECT
    dept_summary.department,
    dept_summary.avg_salary,
    e.full_name,
    e.salary
FROM employees e
INNER JOIN (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summary ON e.department = dept_summary.department
WHERE e.salary > dept_summary.avg_salary;

This query finds all employees earning above their department's average — something that's genuinely hard to express cleanly any other way. The derived table computes the departmental averages once, and then you join against that pre-aggregated result. This avoids running a correlated scalar subquery for every row in employees.

When Derived Tables Beat EXISTS and IN

Derived tables are the right choice when:

1. You need aggregated data from the subquery result

EXISTS and IN only give you a boolean answer. If you need a value from the subquery — like the average, count, or max — you need a derived table (or a scalar subquery, but that has its own performance traps).

-- Find all accounts where this month's orders exceed the account's historical average order value
SELECT
    a.account_id,
    a.company_name,
    monthly.total_orders,
    baselines.avg_monthly_orders
FROM accounts a
INNER JOIN (
    SELECT
        account_id,
        COUNT(*) AS total_orders
    FROM orders
    WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY account_id
) AS monthly ON a.account_id = monthly.account_id
INNER JOIN (
    SELECT
        account_id,
        AVG(monthly_count) AS avg_monthly_orders
    FROM (
        SELECT
            account_id,
            DATE_TRUNC('month', created_at) AS order_month,
            COUNT(*) AS monthly_count
        FROM orders
        GROUP BY account_id, DATE_TRUNC('month', created_at)
    ) AS monthly_history
    GROUP BY account_id
) AS baselines ON a.account_id = baselines.account_id
WHERE monthly.total_orders > baselines.avg_monthly_orders;

This is a multi-step analytical pattern. There's no clean way to express this with EXISTS — you genuinely need the aggregated values in the outer SELECT and WHERE clause. The derived tables allow you to compute intermediate summaries and then join against them.

2. You want to pre-filter before joining

Joining large tables is expensive. If you can reduce one side of the join to a smaller result set first, you save a lot of work:

-- Naive approach: joins all orders, then filters
SELECT c.company_name, o.order_total, o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'DISPUTED'
  AND o.order_total > 10000;

-- Derived table approach: pre-filters orders first
SELECT c.company_name, high_value.order_total, high_value.status
FROM customers c
INNER JOIN (
    SELECT customer_id, order_total, status
    FROM orders
    WHERE status = 'DISPUTED'
      AND order_total > 10000
) AS high_value ON c.customer_id = high_value.customer_id;

Whether this actually helps depends on whether the query planner is smart enough to push the filter into the join automatically (most modern planners are). But in complex queries, explicit derived tables give you more control over when filtering happens, and they make the intent obvious to anyone reading the code.

3. You need to reuse a filtered or transformed dataset multiple times

When you need to reference the same subquery result in multiple places, a derived table (or better, a CTE) avoids redundancy. Advanced subqueries and CTEs covers the CTE pattern in depth, but derived tables serve the same purpose when scope is limited to a single query.

---

Choosing Between EXISTS, IN, and Derived Tables: A Framework

Now that you understand how each tool works, here's a practical decision framework:

Use EXISTS when:

• You're testing for the presence or absence of a matching row (and don't need any values from that row)
• The matching condition involves columns from the outer query (correlated)
• You're writing an anti-join (NOT EXISTS > NOT IN for safety)
• The outer dataset is large and you want early termination
• You need reliable behavior around NULL keys

Use IN (with subquery) when:

• The subquery is non-correlated and can be computed once
• The subquery returns a modest result set (hundreds to low thousands of rows)
• Readability is the priority and the null-safety concern doesn't apply
• You're filtering against a lookup or reference table

Use a Derived Table when:

• You need values from the subquery in your SELECT or WHERE clause
• You need aggregated metrics to filter or join against
• You want to pre-filter a large table before joining
• You need to transform or normalize data before using it in a join

Here's a scenario that puts all three to work in one query. You want to report on all sales reps who:

1. Have at least one deal closed this quarter (EXISTS)
2. Operate in a high-value market segment (IN)
3. Have average deal size above the company-wide average (derived table)

SELECT
    sr.rep_id,
    sr.full_name,
    sr.territory,
    rep_metrics.avg_deal_size,
    company_avg.company_avg_deal_size
FROM sales_reps sr
-- Derived table: compute per-rep average deal size
INNER JOIN (
    SELECT
        rep_id,
        AVG(deal_value) AS avg_deal_size,
        COUNT(*) AS total_deals
    FROM deals
    WHERE stage = 'CLOSED_WON'
      AND closed_date >= DATE_TRUNC('quarter', CURRENT_DATE)
    GROUP BY rep_id
) AS rep_metrics ON sr.rep_id = rep_metrics.rep_id
-- Derived table: compute company-wide average
CROSS JOIN (
    SELECT AVG(deal_value) AS company_avg_deal_size
    FROM deals
    WHERE stage = 'CLOSED_WON'
      AND closed_date >= DATE_TRUNC('quarter', CURRENT_DATE)
) AS company_avg
-- IN: filter by market segment
WHERE sr.segment_id IN (
    SELECT segment_id
    FROM market_segments
    WHERE tier = 'ENTERPRISE'
)
-- EXISTS: verify at least one closed deal this quarter
AND EXISTS (
    SELECT 1
    FROM deals d
    WHERE d.rep_id = sr.rep_id
      AND d.stage = 'CLOSED_WON'
      AND d.closed_date >= DATE_TRUNC('quarter', CURRENT_DATE)
)
-- Filter using derived table result
AND rep_metrics.avg_deal_size > company_avg.company_avg_deal_size;

This is a realistic analytical query. Notice how each subquery technique is deployed for what it does best: derived tables provide metrics, IN filters against a reference table, and EXISTS verifies qualifying activity. The result is a single clean query that reads like the business question it answers.

---

Performance Deep Dive: Execution Plans and When Theory Meets Reality

Understanding the theory is important, but SQL performance is ultimately empirical. Let's talk about what actually happens inside the engine.

Semi-Joins and Anti-Joins

Both EXISTS and the JOIN+DISTINCT pattern ultimately get converted by the query planner into what's called a semi-join (or anti-semi-join for NOT EXISTS). A semi-join returns rows from the left side that have at least one match on the right, but doesn't multiply the left rows or return any columns from the right. Modern databases (PostgreSQL, SQL Server, MySQL 8+, Oracle) all support native semi-join execution strategies.

When you write EXISTS, you're giving the planner a clear semantic signal that a semi-join is appropriate. When you write JOIN + DISTINCT, the planner has to figure out that's what you meant. Sometimes it does. Sometimes it doesn't, and you pay for the full join followed by a sort-based deduplication step.

-- Run EXPLAIN on both versions to compare
EXPLAIN ANALYZE
SELECT DISTINCT c.customer_id, c.company_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

EXPLAIN ANALYZE
SELECT c.customer_id, c.company_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
);

In PostgreSQL, you'll typically see the EXISTS version use a Hash Semi Join while the JOIN+DISTINCT version uses a Hash Join followed by a Sort and Unique node. The semi-join avoids materializing all the matched order rows — it stops as soon as it finds one match per customer.

Index Usage with EXISTS

For EXISTS to perform well, the inner subquery needs an index on its correlated join column. In our customer/orders example:

-- This index is critical for EXISTS performance
CREATE INDEX idx_orders_customer_date 
ON orders (customer_id, created_at);

With this index, the EXISTS subquery can use an index seek for each customer, looking up their orders in O(log n) time rather than scanning the entire orders table. Without it, you get a full table scan for each outer row — potentially catastrophic on large tables.

The same applies to IN subqueries, though the access pattern is different. For a non-correlated IN, the planner typically builds a hash table from the subquery result and then probes it for each outer row. Index usage on the inner query still matters for computing the subquery result quickly, but the probing phase uses the hash table rather than the index.

Derived Table Materialization

A key question with derived tables is whether the database materializes them (executes them once and stores the result in memory) or treats them as logical views that get "folded" into the outer query's plan. Different databases handle this differently:

• PostgreSQL: Generally treats derived tables as optimization fences in some cases, but recent versions are much smarter about pushing predicates through. Use EXPLAIN to verify.
• SQL Server: Usually inlines simple derived tables and optimizes across the boundary. Complex derived tables may get spooled.
• MySQL 8+: Materializes derived tables by default when they contain GROUP BY or aggregation, but will optimize simpler ones inline.
• BigQuery: Treats derived tables as logical views and optimizes aggressively across boundaries.

When you have a complex derived table that's being materialized unnecessarily, a CTE with MATERIALIZED / NOT MATERIALIZED hints (available in PostgreSQL 12+) gives you explicit control.

---

Correlated vs. Non-Correlated Subqueries: The Performance Axis

One of the most important dimensions in subquery performance is whether the subquery is correlated (references outer query columns) or non-correlated (completely self-contained).

Non-correlated subqueries run once. Correlated subqueries run once per row in the outer query. For a table with a million rows, a correlated subquery is called a million times. That sounds terrifying, but it's not always slow because:

1. The query planner may convert it to a hash join internally
2. EXISTS short-circuits, so the correlated subquery often terminates early
3. Proper indexes make each individual invocation fast

The scenario where correlated subqueries genuinely suffer is when:

• The outer table is large AND
• The inner query can't use an index AND
• The planner can't convert it to a join strategy

In that case, you want to convert the correlated subquery into a non-correlated derived table approach. For example, this EXISTS:

-- Correlated EXISTS -- runs once per customer
SELECT c.customer_id, c.company_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
);

Can be rewritten as a derived table join when you want to avoid correlated execution:

-- Non-correlated derived table version
SELECT c.customer_id, c.company_name
FROM customers c
INNER JOIN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
) AS recent_buyers ON c.customer_id = recent_buyers.customer_id;

The derived table version computes recent_buyers once, deduplicates it, and then joins. Whether this is faster depends on the data distribution and indexes. The key is knowing you have the option.

---

Putting It All Together: Rewriting a Messy JOIN Query

Let's work through a realistic refactoring exercise. Here's a query you might encounter in the wild — it works but has multiple issues:

-- Original: messy, produces duplicates, mixes filtering concerns with data retrieval
SELECT DISTINCT
    a.account_id,
    a.account_name,
    a.csm_id,
    r.region_name
FROM accounts a
INNER JOIN users u ON a.account_id = u.account_id
INNER JOIN subscriptions s ON a.account_id = s.account_id
INNER JOIN regions r ON a.region_id = r.region_id
INNER JOIN tickets t ON a.account_id = t.account_id
WHERE s.plan_type = 'ENTERPRISE'
  AND s.status = 'ACTIVE'
  AND u.role = 'ADMIN'
  AND u.last_login >= CURRENT_DATE - INTERVAL '90 days'
  AND t.severity = 'CRITICAL'
  AND t.created_at >= CURRENT_DATE - INTERVAL '7 days';

This query is trying to answer: "Find all active Enterprise accounts that have an admin who logged in recently AND have a critical ticket in the last week." But the JOIN approach creates a row for every combination of matching user × ticket, and the DISTINCT patches over that.

Let's rewrite it properly:

-- Refactored: each concern expressed with the right tool
SELECT
    a.account_id,
    a.account_name,
    a.csm_id,
    r.region_name
FROM accounts a
-- Regions is genuinely a lookup join -- we need the name
INNER JOIN regions r ON a.region_id = r.region_id
-- Enterprise active subscription check
WHERE EXISTS (
    SELECT 1
    FROM subscriptions s
    WHERE s.account_id = a.account_id
      AND s.plan_type = 'ENTERPRISE'
      AND s.status = 'ACTIVE'
)
-- Active admin user check
AND EXISTS (
    SELECT 1
    FROM users u
    WHERE u.account_id = a.account_id
      AND u.role = 'ADMIN'
      AND u.last_login >= CURRENT_DATE - INTERVAL '90 days'
)
-- Recent critical ticket check
AND EXISTS (
    SELECT 1
    FROM tickets t
    WHERE t.account_id = a.account_id
      AND t.severity = 'CRITICAL'
      AND t.created_at >= CURRENT_DATE - INTERVAL '7 days'
);

What changed:

• We kept only the JOIN that actually contributes data to the output (regions)
• We replaced the filtering JOINs with EXISTS subqueries — one per concern
• No DISTINCT needed because accounts never multiply
• Each EXISTS subquery is independently readable and debuggable
• Adding or removing a filter condition is now as simple as adding or removing an EXISTS clause

The query now reads like the business logic it implements: accounts that are active enterprise, have an active admin, and have a recent critical ticket. Anyone reading this query for the first time can understand each filter independently.

---

Hands-On Exercise

Work through these exercises using a schema with four tables:

• customers(customer_id, company_name, segment, country_code, created_at)
• orders(order_id, customer_id, order_total, status, created_at)
• order_items(order_item_id, order_id, product_id, quantity, unit_price)
• products(product_id, product_name, category, cost_price)

Exercise 1: Basic EXISTS Conversion Rewrite the following query using EXISTS instead of DISTINCT + JOIN:

SELECT DISTINCT c.customer_id, c.company_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'REFUNDED'
  AND o.created_at >= CURRENT_DATE - INTERVAL '60 days';

Exercise 2: NOT EXISTS Anti-Join Write a query using NOT EXISTS to find all products in the Electronics category that have never appeared in any order item. Check whether a NOT IN version of this query would produce the same result — and document why or why not.

Exercise 3: Derived Table with Aggregation Find all customers whose total order spend in the last 12 months exceeds $5,000. Then extend that query to also show each customer's order count and average order value alongside their company name and segment. You'll need a derived table to accomplish this cleanly.

Exercise 4: IN for Reference Table Lookup Write a query using IN with a subquery to find all orders that contain at least one product from the Seasonal category. Then rewrite it using EXISTS and compare the two approaches.

Exercise 5: The Full Refactor The following query returns the right data but is poorly structured. Refactor it using the appropriate mix of EXISTS, IN, and derived tables:

SELECT DISTINCT
    c.customer_id,
    c.company_name,
    c.segment,
    p.category
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE c.segment = 'SMB'
  AND o.order_total > 1000
  AND p.category IN ('Electronics', 'Software')
  AND o.status = 'COMPLETED';

What does this query actually intend to return? Write a clean version that returns one row per qualifying customer.

---

Common Mistakes & Troubleshooting

Mistake 1: Using NOT IN Without Null-Guarding

Symptom: Your NOT IN query returns zero rows or far fewer than expected.

Cause: The subquery returns at least one NULL value, causing all comparisons to evaluate to UNKNOWN.

Fix:

-- Broken
WHERE product_id NOT IN (SELECT discontinued_id FROM product_removals)

-- Fixed option 1: filter NULLs in subquery
WHERE product_id NOT IN (
    SELECT discontinued_id FROM product_removals
    WHERE discontinued_id IS NOT NULL
)

-- Fixed option 2: use NOT EXISTS
WHERE NOT EXISTS (
    SELECT 1 FROM product_removals pr
    WHERE pr.discontinued_id = products.product_id
)

Mistake 2: Forgetting to Correlate the EXISTS Subquery

Symptom: Your EXISTS query returns all rows or no rows regardless of data.

Cause: You forgot to link the inner subquery to the outer query.

-- Bug: no correlation -- returns TRUE for every customer if any order exists
WHERE EXISTS (
    SELECT 1 FROM orders
    WHERE status = 'ACTIVE'
)

-- Correct: correlated
WHERE EXISTS (
    SELECT 1 FROM orders
    WHERE orders.customer_id = customers.customer_id  -- the link
      AND status = 'ACTIVE'
)

Mistake 3: Using a Scalar Subquery Where a Derived Table Is Needed

Symptom: Your scalar subquery in the WHERE clause returns "subquery returns more than one row" error, or it runs painfully slowly.

Cause: You've written a correlated scalar subquery that runs once per row and returns a single value. For aggregation-based filtering, this is the worst of both worlds.

-- Slow scalar subquery approach
WHERE (
    SELECT COUNT(*) FROM orders o
    WHERE o.customer_id = c.customer_id
) > 5

-- Better: derived table approach
INNER JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
) AS frequent_buyers ON c.customer_id = frequent_buyers.customer_id

Mistake 4: Writing Overly Nested IN Subqueries

Symptom: Three or four levels of nested IN subqueries that are hard to read and debug.

Fix: Break the logic into CTEs or derived tables at each step. If you're nesting more than two levels of IN, that's a signal to restructure.

-- Hard to read: three levels of IN
WHERE x IN (SELECT a FROM t1 WHERE b IN (SELECT c FROM t2 WHERE d IN (...)))

-- Better: use CTEs to name each step
WITH level1 AS (SELECT c FROM t2 WHERE d IN (...)),
     level2 AS (SELECT a FROM t1 WHERE b IN (SELECT c FROM level1))
SELECT * FROM main_table WHERE x IN (SELECT a FROM level2);

Mistake 5: Assuming EXISTS Is Always Faster Than JOIN

Symptom: You rewrite all your JOINs to EXISTS but don't see consistent improvement.

Reality: A well-indexed JOIN processed as a hash semi-join by the planner can be as fast as EXISTS. The win from EXISTS is primarily about correctness (no duplicates) and clarity, not guaranteed raw speed. Always measure with EXPLAIN ANALYZE.

---

Summary & Next Steps

Here's what you've learned:

• JOINs used as filters create duplicate rows and force you to DISTINCT away the mess. They're the wrong tool when your goal is membership testing, not data retrieval.
• EXISTS is the cleanest, safest filter for "does a match exist?" questions. It short-circuits, handles NULLs correctly, and gives the query planner a clear semi-join signal.
• NOT EXISTS is almost always preferable to NOT IN for anti-join patterns, because NOT IN silently breaks when the subquery returns NULLs.
• IN with a subquery is effective for non-correlated lookups against modest result sets. Watch the NULL trap and prefer EXISTS when keys could be NULL.
• Derived tables are the right tool when you need aggregated values, want to pre-filter before joining, or need to reuse a transformed dataset.
• The decision framework: JOINs for columns you need in output, EXISTS/IN for conditions you need to verify, derived tables for intermediate computations.

The practical skill to develop now is recognizing filter intent versus retrieval intent as you read and write queries. When you see a JOIN followed by DISTINCT, stop and ask: "Am I joining for data, or am I joining to filter?" That question will guide you to the right tool every time.

To deepen your understanding, work through Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY for practice combining all of these patterns in complex analytical scenarios. For the aggregation side of derived table patterns, Aggregating Data from Multiple Subqueries will show you how scalar and derived table patterns combine in a single SELECT. And for understanding how indexes interact with all of these patterns, SQL Indexes Explained gives you the foundation to reason about why any of this is fast or slow.