Turning a Many-Step Business Question into a Single SQL Query: Nesting Subqueries Inside JOIN Conditions and Aggregate Filters

Introduction

You've just walked out of a stakeholder meeting with a request that sounds deceptively simple: "Show me the salespeople whose average deal size last quarter beat the company-wide average, but only include deals from accounts that were first acquired in the past two years." You go back to your desk, open a blank SQL editor, and stare at that empty query. Where do you even begin?

This is the moment where SQL mastery separates itself from SQL competency. The question isn't a single aggregation — it's a multi-layered reasoning problem wearing the costume of a data request. It asks you to calculate a moving target (the company-wide average), filter on a time-bound condition about a related entity (account acquisition date), and then compare individual performance against that benchmark — all in one coherent result set. Most people's instinct is to break this into three separate queries, paste results into Excel, and stitch it together manually. That works, but it's brittle, slow to reproduce, and impossible to automate.

By the end of this lesson, you'll be able to take business questions like that one and translate them directly into a single, correct, performant SQL query — nesting subqueries inside JOIN conditions and HAVING clauses with full confidence. You won't just memorize patterns; you'll understand why the database engine can handle these constructs and when each approach is the right one.

What you'll learn:

How to decompose a complex, multi-step business question into constituent SQL building blocks
How to place subqueries inside JOIN conditions to dynamically filter which rows participate in a join
How to embed subqueries inside HAVING clauses to compare group-level aggregates against computed benchmarks
How correlated vs. uncorrelated subqueries behave differently and when to choose each
Performance trade-offs between subquery-in-JOIN, subquery-in-HAVING, and equivalent CTE/derived-table approaches

Prerequisites

This is an expert-level lesson. You should be comfortable with:

Writing basic SELECT queries with WHERE filtering
Using JOIN to combine tables
Writing GROUP BY and HAVING clauses
Understanding what a subquery is conceptually — if you need a refresher, Understanding SQL Subqueries is a good starting point

You should also have a working SQL environment. The examples in this lesson use standard SQL syntax compatible with PostgreSQL, MySQL 8+, SQL Server, and BigQuery with minor dialect adjustments noted inline.

The Schema We'll Work With

Throughout this lesson, we'll use a SaaS sales analytics schema. Here's the structure:

-- Accounts: companies that have purchased or are prospects
CREATE TABLE accounts (
    account_id      INT PRIMARY KEY,
    account_name    VARCHAR(200),
    industry        VARCHAR(100),
    acquired_date   DATE,          -- when they became a customer
    region          VARCHAR(50)
);

-- Deals: individual closed-won opportunities
CREATE TABLE deals (
    deal_id         INT PRIMARY KEY,
    account_id      INT REFERENCES accounts(account_id),
    salesperson_id  INT REFERENCES salespeople(salesperson_id),
    deal_value      DECIMAL(12, 2),
    close_date      DATE,
    quarter         CHAR(6)        -- e.g., '2024Q1'
);

-- Salespeople
CREATE TABLE salespeople (
    salesperson_id  INT PRIMARY KEY,
    full_name       VARCHAR(200),
    region          VARCHAR(50),
    hire_date       DATE,
    manager_id      INT            -- self-referential, manager's salesperson_id
);

-- Products purchased as part of each deal
CREATE TABLE deal_products (
    deal_id         INT REFERENCES deals(deal_id),
    product_id      INT,
    quantity        INT,
    unit_price      DECIMAL(10, 2)
);

This schema is realistic enough to generate multi-step questions that require exactly the techniques we're teaching.

Step One: Learning to Decompose the Business Question

Before you write a single line of SQL, you need to do something that feels like it belongs in a product meeting rather than a SQL editor: break the question into independent logical steps.

Let's take our opening question and dissect it surgically:

"Show me the salespeople whose average deal size last quarter beat the company-wide average, but only include deals from accounts that were first acquired in the past two years."

Here are the atomic pieces:

What time period? "Last quarter" — you need a date boundary.
Which deals qualify? Only deals tied to accounts acquired in the past two years. This is a filter on the accounts table that has to propagate into the deals table.
What's the benchmark? The company-wide average deal size, computed from only those qualifying deals (same time period, same account filter — or possibly all deals? You need to clarify this with the stakeholder, but let's assume it's the same filtered pool).
What's being compared? Each salesperson's average deal size vs. that benchmark.
What's the output? Salesperson name, their average, presumably the benchmark too for context.

Once you have this decomposition, each numbered item maps almost directly to a SQL clause or subquery. This decomposition skill is the single highest-leverage technique in analytical SQL. If you're interested in systematizing this approach further, Translating Business Questions into SQL covers the decomposition methodology in depth.

Key insight

Every complex business question is a series of simple questions asked in a specific order. SQL's subquery and join mechanisms are just a way of encoding that order into a single declarative statement.

Step Two: Building the Foundation — The Filtered Deal Set

Let's start from the inside out. The innermost concern is: which deals are we even talking about? Deals must:

Have closed in the last quarter
Belong to accounts acquired in the past two years

Let's define "last quarter" dynamically. In PostgreSQL:

-- Last quarter boundaries (PostgreSQL syntax)
-- For SQL Server, use DATEADD/DATEDIFF; for MySQL, DATE_SUB
SELECT
    DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AS q_start,
    DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 day'    AS q_end;

Now let's write the qualified deal set as a standalone query first:

-- Step 1: Qualified deals — deals from recently-acquired accounts, last quarter
SELECT
    d.deal_id,
    d.salesperson_id,
    d.deal_value,
    d.close_date
FROM deals d
JOIN accounts a ON d.account_id = a.account_id
WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
  AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
  AND a.acquired_date >= CURRENT_DATE - INTERVAL '2 years';

This is your foundation. Verify it returns plausible data before nesting it. This step-by-step verification is something most experienced engineers do religiously — run each subquery in isolation before combining them. See Debugging SQL Queries for a structured approach to this kind of progressive validation.

Step Three: Nesting Subqueries Inside JOIN Conditions

Here's where things get interesting. Now we need to introduce the salesperson data. But we don't just want any deals — we want the qualified deals joined to salespeople. There are two natural approaches:

Approach A: Join all three tables and put the account filter in WHERE. Approach B: Pre-filter deals in a subquery, then join that subquery to salespeople.

Both produce the same result. But Approach B — using a subquery in the FROM clause, sometimes called a derived table or inline view — is often cleaner and, in some optimizers, more explicit about the intended row reduction:

-- Approach B: Subquery inside the JOIN (derived table approach)
SELECT
    sp.salesperson_id,
    sp.full_name,
    AVG(qd.deal_value) AS avg_deal_size
FROM salespeople sp
JOIN (
    -- This subquery acts as a pre-filtered "qualified deals" table
    SELECT
        d.deal_id,
        d.salesperson_id,
        d.deal_value
    FROM deals d
    JOIN accounts a ON d.account_id = a.account_id
    WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
      AND a.acquired_date >= CURRENT_DATE - INTERVAL '2 years'
) AS qd ON qd.salesperson_id = sp.salesperson_id
GROUP BY
    sp.salesperson_id,
    sp.full_name;

What's happening here? The subquery qd (for "qualified deals") runs first and produces a result set. That result set is then treated exactly like a real table for the purpose of the outer JOIN. The JOIN condition qd.salesperson_id = sp.salesperson_id connects them.

Tip

When you put a subquery in the FROM clause (as a derived table), it's evaluated once and its result set is held in memory (or a temporary structure, depending on the optimizer). This is different from a correlated subquery, which re-executes for every row of the outer query. Derived tables are usually more efficient when the subquery produces a large-but-filterable result set.

Now let's go one level further. What if the JOIN condition itself needs to reference a computed value? Suppose you want to join salespeople to a table of their top account by revenue — you need the join condition to reference a subquery that ranks accounts per salesperson.

-- Joining each salesperson to their single highest-revenue account (last quarter)
SELECT
    sp.full_name,
    top_acct.account_name,
    top_acct.total_revenue
FROM salespeople sp
JOIN (
    SELECT
        d.salesperson_id,
        a.account_name,
        SUM(d.deal_value) AS total_revenue,
        RANK() OVER (PARTITION BY d.salesperson_id ORDER BY SUM(d.deal_value) DESC) AS rev_rank
    FROM deals d
    JOIN accounts a ON d.account_id = a.account_id
    WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
    GROUP BY d.salesperson_id, a.account_id, a.account_name
) AS top_acct
  ON top_acct.salesperson_id = sp.salesperson_id
 AND top_acct.rev_rank = 1   -- << this is part of the JOIN condition
ORDER BY top_acct.total_revenue DESC;

Notice top_acct.rev_rank = 1 in the JOIN condition. This is a filter on the derived table applied at join time. You could also put this in a WHERE clause and get identical results — but placing it in the JOIN condition makes the intent explicit: "I'm joining to one specific row from this subquery." This is a stylistic choice, but it's one many experienced engineers prefer because it keeps "how the join is shaped" separate from "what rows I want in the final result."

Warning

If you use a window function like RANK() inside a subquery and then filter on its value, you must filter in an outer query or via the JOIN condition — you cannot filter on window function results in the same WHERE clause where the window function is computed. The subquery wrapper is what makes this possible.

Step Four: Nesting Subqueries Inside HAVING Clauses

Now we get to the most elegant and underused technique in analytical SQL: putting a subquery directly inside a HAVING clause. This is how you compare a group-level aggregate against a computed benchmark without running a second query.

Let's build the full answer to our opening question. We want salespersons whose average deal size exceeds the company-wide average. The company-wide average is itself computed from the qualified deal pool:

-- Company-wide average (the benchmark)
SELECT AVG(deal_value)
FROM deals d
JOIN accounts a ON d.account_id = a.account_id
WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
  AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
  AND a.acquired_date >= CURRENT_DATE - INTERVAL '2 years';

This is a scalar subquery — it returns exactly one row and one column. Scalar subqueries can be used almost anywhere a single value can appear: SELECT lists, WHERE conditions, JOIN conditions, HAVING clauses. Here's how we embed it in HAVING:

-- Full query: salespeople who beat the company-wide average deal size
SELECT
    sp.salesperson_id,
    sp.full_name,
    COUNT(qd.deal_id)      AS deal_count,
    AVG(qd.deal_value)     AS avg_deal_size,
    ROUND(
        (SELECT AVG(d2.deal_value)
         FROM deals d2
         JOIN accounts a2 ON d2.account_id = a2.account_id
         WHERE d2.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
           AND d2.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
           AND a2.acquired_date >= CURRENT_DATE - INTERVAL '2 years'),
        2
    ) AS company_avg        -- showing the benchmark for context
FROM salespeople sp
JOIN (
    SELECT
        d.deal_id,
        d.salesperson_id,
        d.deal_value
    FROM deals d
    JOIN accounts a ON d.account_id = a.account_id
    WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
      AND a.acquired_date >= CURRENT_DATE - INTERVAL '2 years'
) AS qd ON qd.salesperson_id = sp.salesperson_id
GROUP BY
    sp.salesperson_id,
    sp.full_name
HAVING AVG(qd.deal_value) > (
    -- Scalar subquery inside HAVING: computes company-wide average
    SELECT AVG(d2.deal_value)
    FROM deals d2
    JOIN accounts a2 ON d2.account_id = a2.account_id
    WHERE d2.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND d2.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
      AND a2.acquired_date >= CURRENT_DATE - INTERVAL '2 years'
)
ORDER BY avg_deal_size DESC;

Let's walk through what's happening:

FROM + JOIN to derived table qd: We've pre-filtered the deal universe to only qualifying deals. The outer query sees a pre-cleaned dataset.
GROUP BY on salesperson: We're aggregating per salesperson.
HAVING with embedded scalar subquery: After grouping, for each salesperson's group, we compare their AVG(qd.deal_value) against the scalar subquery result. The scalar subquery executes once (most optimizers will cache its result), and every group is compared against that same number.
SELECT list also includes the benchmark: We embed the same scalar subquery in the SELECT list so the output shows the benchmark alongside each salesperson's average. A good query optimizer will only compute it once despite it appearing twice.

Key insight

A subquery in a HAVING clause follows the same scoping rules as any other subquery. It can reference outer aliases if it's correlated, or be completely self-contained (uncorrelated) if it's computing a global benchmark. Uncorrelated scalar subqueries in HAVING are extremely efficient because they evaluate to a constant before the HAVING filter is applied.

Step Five: Correlated Subqueries — When the Subquery Needs to "See" the Outer Row

Everything so far has used uncorrelated subqueries — they're self-contained and don't reference the outer query's current row. But sometimes you need the subquery to adapt based on context. That's a correlated subquery.

Let's say you want to know, for each salesperson, the deal count last quarter compared to the same salesperson's deal count in the prior quarter. This requires a correlated subquery in the SELECT list:

SELECT
    sp.full_name,
    COUNT(d.deal_id) AS deals_this_quarter,
    (
        -- Correlated subquery: references sp.salesperson_id from the outer query
        SELECT COUNT(d_prev.deal_id)
        FROM deals d_prev
        WHERE d_prev.salesperson_id = sp.salesperson_id    -- << outer reference
          AND d_prev.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '6 months'
          AND d_prev.close_date <  DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
    ) AS deals_prior_quarter
FROM salespeople sp
JOIN deals d
    ON d.salesperson_id = sp.salesperson_id
   AND d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
   AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY sp.salesperson_id, sp.full_name
ORDER BY deals_this_quarter DESC;

The subquery (SELECT COUNT(d_prev.deal_id) ... WHERE d_prev.salesperson_id = sp.salesperson_id ...) references sp.salesperson_id — a column from the outer query's current group. This makes it correlated. The database engine must re-execute this subquery for each salesperson group.

Warning

Correlated subqueries in SELECT lists execute once per row (or per group after GROUP BY). If you have 500 salespeople, that subquery runs 500 times. For small cardinalities this is fine; for millions of rows, it can be catastrophic. Always consider whether a correlated subquery can be rewritten as a LEFT JOIN to a derived table or a window function instead.

Here's the same query rewritten with a LEFT JOIN — often much faster:

SELECT
    sp.full_name,
    COUNT(d.deal_id)             AS deals_this_quarter,
    COALESCE(prev.deal_count, 0) AS deals_prior_quarter
FROM salespeople sp
JOIN deals d
    ON d.salesperson_id = sp.salesperson_id
   AND d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
   AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
LEFT JOIN (
    SELECT
        salesperson_id,
        COUNT(deal_id) AS deal_count
    FROM deals
    WHERE close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '6 months'
      AND close_date <  DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
    GROUP BY salesperson_id
) AS prev ON prev.salesperson_id = sp.salesperson_id
GROUP BY sp.salesperson_id, sp.full_name, prev.deal_count
ORDER BY deals_this_quarter DESC;

The LEFT JOIN version pre-aggregates the prior-quarter counts once, then joins. The optimizer only scans the deals table twice rather than once per salesperson. For large tables, this can be an order-of-magnitude faster. Understanding the performance implications of SQL indexes matters here too — both close_date and salesperson_id should be indexed for either version to perform well.

Step Six: Composing the Full Multi-Condition Query

Let's go even further. Suppose the actual business question is:

"Find salespeople who, last quarter, had at least 5 qualifying deals (from accounts acquired within 2 years), an average deal size above the company-wide average for those deals, and who closed at least one deal larger than the single largest deal closed by any salesperson hired in the past year."

This is a genuinely complex question with three distinct filters, each requiring either a subquery or aggregation. Let's decompose:

At least 5 qualifying deals → HAVING COUNT(...) >= 5
Average deal size above company-wide average → HAVING AVG(...) > (scalar subquery)
At least one deal larger than the largest deal by any "new" salesperson → HAVING MAX(...) > (scalar subquery)

SELECT
    sp.salesperson_id,
    sp.full_name,
    sp.hire_date,
    COUNT(qd.deal_id)       AS qualifying_deal_count,
    ROUND(AVG(qd.deal_value), 2) AS avg_deal_size,
    MAX(qd.deal_value)      AS largest_deal
FROM salespeople sp
JOIN (
    -- Derived table: qualifying deals only
    SELECT
        d.deal_id,
        d.salesperson_id,
        d.deal_value
    FROM deals d
    JOIN accounts a ON d.account_id = a.account_id
    WHERE d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
      AND a.acquired_date >= CURRENT_DATE - INTERVAL '2 years'
) AS qd ON qd.salesperson_id = sp.salesperson_id
GROUP BY
    sp.salesperson_id,
    sp.full_name,
    sp.hire_date
HAVING
    -- Condition 1: at least 5 qualifying deals
    COUNT(qd.deal_id) >= 5

    -- Condition 2: average above company-wide average
    AND AVG(qd.deal_value) > (
        SELECT AVG(d2.deal_value)
        FROM deals d2
        JOIN accounts a2 ON d2.account_id = a2.account_id
        WHERE d2.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
          AND d2.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
          AND a2.acquired_date >= CURRENT_DATE - INTERVAL '2 years'
    )

    -- Condition 3: beat the best deal from any new hire
    AND MAX(qd.deal_value) > (
        SELECT MAX(d3.deal_value)
        FROM deals d3
        JOIN salespeople sp3 ON d3.salesperson_id = sp3.salesperson_id
        WHERE d3.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
          AND d3.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
          AND sp3.hire_date >= CURRENT_DATE - INTERVAL '1 year'
    )

ORDER BY avg_deal_size DESC;

This single query embeds two scalar subqueries in HAVING and one derived table in FROM. The execution order the database follows is:

FROM + JOIN (build the full row set, with qd pre-filtered)
WHERE (if any outer WHERE — none here)
GROUP BY (partition into salesperson groups)
HAVING (evaluate all three conditions; scalar subqueries are computed as constants before comparison)
SELECT (project the columns)
ORDER BY

Tip

When you have multiple scalar subqueries in HAVING, they all execute independently. If two of them compute the same thing, most SQL optimizers will not automatically deduplicate the computation — they'll run the subquery twice. If performance matters, refactor to a CTE that defines the shared value once, then reference it in both places.

Step Seven: When to Use CTEs Instead

The query above is correct and complete, but it has a readability problem: the date boundary expression appears four times, and the qualified deal filter logic appears in two places. This is where Common Table Expressions (CTEs) become critical for maintainability.

Here's the same query refactored with CTEs:

WITH

-- CTE 1: Define the time window once
date_bounds AS (
    SELECT
        DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AS q_start,
        DATE_TRUNC('quarter', CURRENT_DATE)                        AS q_end
),

-- CTE 2: Qualifying deals (last quarter, accounts acquired < 2 years ago)
qualified_deals AS (
    SELECT
        d.deal_id,
        d.salesperson_id,
        d.deal_value
    FROM deals d
    JOIN accounts a ON d.account_id = a.account_id
    CROSS JOIN date_bounds db
    WHERE d.close_date >= db.q_start
      AND d.close_date <  db.q_end
      AND a.acquired_date >= CURRENT_DATE - INTERVAL '2 years'
),

-- CTE 3: Company-wide average (benchmark)
company_avg AS (
    SELECT AVG(deal_value) AS avg_deal_value
    FROM qualified_deals
),

-- CTE 4: Best deal by new salespeople (hired within 1 year)
new_hire_max AS (
    SELECT MAX(d.deal_value) AS max_deal_value
    FROM deals d
    JOIN salespeople sp ON d.salesperson_id = sp.salesperson_id
    CROSS JOIN date_bounds db
    WHERE d.close_date >= db.q_start
      AND d.close_date <  db.q_end
      AND sp.hire_date >= CURRENT_DATE - INTERVAL '1 year'
)

-- Main query
SELECT
    sp.salesperson_id,
    sp.full_name,
    sp.hire_date,
    COUNT(qd.deal_id)            AS qualifying_deal_count,
    ROUND(AVG(qd.deal_value), 2) AS avg_deal_size,
    MAX(qd.deal_value)           AS largest_deal,
    ROUND(ca.avg_deal_value, 2)  AS company_benchmark
FROM salespeople sp
JOIN qualified_deals qd ON qd.salesperson_id = sp.salesperson_id
CROSS JOIN company_avg ca
CROSS JOIN new_hire_max nhm
GROUP BY
    sp.salesperson_id,
    sp.full_name,
    sp.hire_date,
    ca.avg_deal_value,
    nhm.max_deal_value
HAVING
    COUNT(qd.deal_id) >= 5
    AND AVG(qd.deal_value) > ca.avg_deal_value
    AND MAX(qd.deal_value) > nhm.max_deal_value
ORDER BY avg_deal_size DESC;

Notice the key technique: company_avg and new_hire_max are single-row CTEs, joined via CROSS JOIN to the main query. Since they return exactly one row each, the CROSS JOIN produces no fan-out. This makes the benchmark values available as columns in the GROUP BY and HAVING clauses without re-running the scalar subquery logic multiple times.

Key insight

When you need a scalar benchmark value in both SELECT (to display it) and HAVING (to filter on it), the cleanest approach is to define it as a single-row CTE, cross-join it in, and reference the column name. This makes the query self-documenting and ensures the value is computed exactly once.

The CTE version and the nested-subquery version are functionally equivalent. The CTE version is dramatically more maintainable — changing the time window means editing one line in date_bounds. The nested version requires hunting down every hardcoded date expression. For production code that gets revisited, CTEs win almost every time.

Step Eight: Understanding Execution Order and Scope Rules

One of the most common sources of confusion when nesting subqueries is not knowing what each level can "see." Here are the rules:

Subquery scope visibility:

An uncorrelated subquery in FROM (derived table) cannot reference columns from the outer FROM clause — it's a completely independent query.
A correlated subquery in SELECT, WHERE, or HAVING can reference columns from the outer query's current row or current group.
A subquery in HAVING can reference the outer GROUP BY columns (because those exist at HAVING evaluation time) but cannot reference alias names defined in SELECT (because SELECT is evaluated after HAVING in the logical order).

This last point trips people up constantly. Consider:

-- THIS WILL FAIL in most databases
SELECT
    salesperson_id,
    AVG(deal_value) AS avg_dv
FROM deals
GROUP BY salesperson_id
HAVING avg_dv > 10000;  -- ERROR: avg_dv is a SELECT alias, not visible in HAVING

You must repeat the expression:

-- CORRECT
SELECT
    salesperson_id,
    AVG(deal_value) AS avg_dv
FROM deals
GROUP BY salesperson_id
HAVING AVG(deal_value) > 10000;  -- repeat the aggregate expression

Note

Some databases (MySQL, SQLite, BigQuery) are lenient about this and allow SELECT aliases in HAVING and even GROUP BY. PostgreSQL and SQL Server are strict. Write to the strict standard for portable queries.

The logical order of SQL evaluation (this is not necessarily execution order, but it determines what each clause can reference):

1. FROM (including JOINs and derived tables/subqueries)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / FETCH

This order explains why you can't filter on a SELECT alias in WHERE or HAVING, but you can in ORDER BY (because ORDER BY is evaluated last). It also explains why a subquery in FROM can't see the outer FROM — the inner FROM is evaluated as part of building the outer FROM's row set.

Step Nine: Performance Trade-Offs and Optimizer Behavior

Understanding when the database actually executes these nested structures is crucial for writing queries that don't accidentally destroy performance in production.

Derived tables vs. correlated subqueries: A derived table (subquery in FROM) is materialized or folded by the optimizer. In PostgreSQL, the optimizer can often "look through" a derived table and push predicates inside it — this is called subquery pushdown. In SQL Server, this is called predicate pushdown. When it works, a derived table with an outer WHERE filter becomes as efficient as writing the WHERE directly inside.

When it doesn't work — for instance, when the derived table contains aggregate functions or window functions — the optimizer must fully materialize the subquery before applying outer filters. This can mean scanning millions of rows into a temporary structure only to discard most of them. Check the execution plan before assuming the optimizer is smart enough to push your predicates through.

Scalar subqueries in SELECT or HAVING: These are generally evaluated once per group (in HAVING) or once per row (in SELECT). For small result sets, this is fine. For large result sets, multiple scalar subqueries in SELECT can each independently scan large tables. If you find yourself writing three scalar subqueries in a SELECT list that all touch the same base table with the same filter, that's a signal to refactor to a single derived table or CTE that computes all three values at once.

The EXISTS alternative for HAVING-like filtering: Sometimes what looks like a HAVING filter is better expressed as a semi-join using EXISTS. For example, if you want "salespeople who have at least one deal over $100,000," HAVING MAX(...) > 100000 works but requires scanning all deals per group. EXISTS short-circuits as soon as one qualifying row is found. The choice depends on whether you need the aggregate for display purposes too. See Mastering SQL EXISTS and NOT EXISTS for a deep dive on this pattern.

-- EXISTS as an alternative to HAVING MAX(deal_value) > 100000
-- when you only need the filter, not the aggregate value itself
SELECT DISTINCT sp.salesperson_id, sp.full_name
FROM salespeople sp
WHERE EXISTS (
    SELECT 1
    FROM deals d
    WHERE d.salesperson_id = sp.salesperson_id
      AND d.deal_value > 100000
      AND d.close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND d.close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
);

Tip

Use EXISTS/NOT EXISTS when your goal is purely to check "does at least one qualifying row exist?" Use HAVING with an aggregate when you need to compute or display the aggregate. Mixing the two — putting EXISTS inside a query that also does GROUP BY — is valid but often signals that the query structure could be simplified.

Step Ten: Anti-Patterns and When the Rules Break Down

Anti-Pattern 1: Deeply Nesting Subqueries Inside Subqueries Inside Subqueries

There's no strict limit on nesting depth, but beyond three levels, queries become nearly impossible to debug or review. If you find yourself writing:

SELECT ... FROM (SELECT ... FROM (SELECT ... FROM (SELECT ...)))

... it's almost always better expressed as a series of CTEs. Each CTE is testable in isolation, nameable, and readable. Deep nesting is a style smell that usually indicates the problem decomposition was done in the wrong order.

Anti-Pattern 2: Correlated Subquery in a Loop-Like Context

If you're writing a correlated subquery that references a column from a large table (not just a small GROUP BY result), you've effectively written a nested loop. The outer table provides N rows, and for each row, the subquery scans the inner table. This is O(N × M) complexity. For large N and M, this is disqualifying. Always ask: can this be expressed as a JOIN instead?

Anti-Pattern 3: Repeating the Same Subquery Logic Three Times

If you see the same complex subquery appearing in multiple places in a query, you're doing the optimizer's job manually — and doing it worse. Define it as a CTE once and reference the name. The optimizer can then decide whether to materialize it once or inline it each time.

Anti-Pattern 4: Using Subqueries in JOIN Conditions for Filtering That Belongs in WHERE

-- CONFUSING: filter logic buried in JOIN condition
SELECT * FROM deals d
JOIN accounts a
    ON d.account_id = a.account_id
   AND a.industry = (SELECT industry FROM target_industries WHERE rank = 1)

-- CLEARER: same result, filter in WHERE
SELECT * FROM deals d
JOIN accounts a ON d.account_id = a.account_id
WHERE a.industry = (SELECT industry FROM target_industries WHERE rank = 1)

Putting non-join-relationship filters into JOIN conditions is sometimes useful (especially for controlling LEFT JOIN behavior), but when it's a simple scalar comparison, it belongs in WHERE. Reserve the JOIN condition for the relationship columns.

When the Rules Break Down: LEFT JOIN Filtering

One genuine exception to "filters belong in WHERE" is with LEFT JOINs. Consider:

-- WRONG: This accidentally converts LEFT JOIN to INNER JOIN
SELECT sp.full_name, d.deal_value
FROM salespeople sp
LEFT JOIN deals d ON d.salesperson_id = sp.salesperson_id
WHERE d.close_date >= '2024-01-01';  -- filters OUT rows where d is NULL

-- CORRECT: Filter on the left-joined table goes in the JOIN condition
SELECT sp.full_name, d.deal_value
FROM salespeople sp
LEFT JOIN deals d
    ON d.salesperson_id = sp.salesperson_id
   AND d.close_date >= '2024-01-01';  -- salespeople with no recent deals still appear (with NULL)

This is exactly where filtering in the JOIN condition serves a specific semantic purpose — it controls whether non-matching rows from the left table are retained with NULLs. This applies equally to subqueries in JOIN conditions: a LEFT JOIN to a derived table that has a WHERE clause inside it behaves very differently from adding that WHERE to the outer query's WHERE clause.

Hands-On Exercise

Work through this scenario in your SQL environment. Use the schema defined at the beginning of this lesson. If you don't have the actual data, you can adapt the structure to any database you have access to.

The Business Question:

"Identify regions where the average deal size for new accounts (acquired within 18 months) last quarter was at least 20% higher than the average deal size for all accounts last quarter. For each qualifying region, show the number of new-account deals, the new-account average, and the all-account average."

Your task: Write this as a single SQL query. Here are the steps to guide you:

Define "last quarter" using date arithmetic (no hardcoded dates).
Write a derived table that computes, per region, both the new-account average and the all-account average for last quarter. Hint: use conditional aggregation with CASE WHEN, or two separate subquery joins.
Filter the result using HAVING (or a WHERE on a derived table) to keep only regions where the new-account average is ≥ 120% of the all-account average.
Include deal counts for both groups.

Expected output columns: region, new_acct_deal_count, new_acct_avg_deal, all_acct_avg_deal, pct_above_benchmark

Stretch goal: Sort the result by pct_above_benchmark descending, and add a column showing the rank of each qualifying region by that percentage.

Reference solution sketch:

WITH last_q AS (
    SELECT
        DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AS q_start,
        DATE_TRUNC('quarter', CURRENT_DATE)                        AS q_end
),
regional_stats AS (
    SELECT
        a.region,
        COUNT(d.deal_id)                                          AS all_deal_count,
        AVG(d.deal_value)                                         AS all_avg,
        COUNT(CASE WHEN a.acquired_date >= CURRENT_DATE - INTERVAL '18 months'
                   THEN 1 END)                                    AS new_deal_count,
        AVG(CASE WHEN a.acquired_date >= CURRENT_DATE - INTERVAL '18 months'
                 THEN d.deal_value END)                           AS new_avg
    FROM deals d
    JOIN accounts a ON d.account_id = a.account_id
    CROSS JOIN last_q lq
    WHERE d.close_date >= lq.q_start
      AND d.close_date <  lq.q_end
    GROUP BY a.region
)
SELECT
    region,
    new_deal_count,
    ROUND(new_avg, 2)                            AS new_acct_avg_deal,
    ROUND(all_avg, 2)                            AS all_acct_avg_deal,
    ROUND(100.0 * (new_avg / all_avg - 1), 1)   AS pct_above_benchmark,
    RANK() OVER (ORDER BY (new_avg / all_avg) DESC) AS region_rank
FROM regional_stats
WHERE new_avg >= all_avg * 1.20   -- can use WHERE here since we're filtering on a derived table column
  AND new_avg IS NOT NULL
ORDER BY pct_above_benchmark DESC;

Notice how CASE WHEN inside AVG() elegantly computes both conditional and unconditional averages in a single pass over the data — this is a form of conditional aggregation that dramatically reduces the number of table scans needed.

Common Mistakes & Troubleshooting

"Column not found" in HAVING

You referenced a SELECT alias in HAVING. Repeat the aggregate expression directly: HAVING AVG(deal_value) > 10000, not HAVING avg_dv > 10000.

The subquery returns more than one row

You used a scalar subquery but it returned multiple rows. The fix is usually adding an aggregation (MAX, MIN, AVG) or a LIMIT 1 with a meaningful ORDER BY. If multiple rows are genuinely possible and you need to compare against each, use IN or ANY instead of =.

LEFT JOIN produces NULLs you didn't expect

A filter in your WHERE clause is silently converting a LEFT JOIN to an INNER JOIN. Move filters on the right table's columns into the JOIN condition, or use IS NULL conditionals in WHERE to preserve the intended behavior.

Performance is terrible; the query takes forever

Profile with EXPLAIN ANALYZE (PostgreSQL) or SET STATISTICS IO ON (SQL Server). Look for nested loops with large row counts, or full table scans inside correlated subqueries. The fix is almost always: replace correlated subqueries with derived tables or CTEs, ensure indexes exist on JOIN and WHERE columns, and check whether the optimizer is pushing predicates through your derived tables.

The same subquery appears multiple times and runs slowly

Refactor to a CTE. In PostgreSQL, add the MATERIALIZED hint if you want to force the CTE to be computed once: WITH my_cte AS MATERIALIZED (...). In PostgreSQL 12+, the optimizer may inline CTEs by default unless they're referenced multiple times or contain volatile functions.

The query returns no rows when you expect rows

Run each subquery in isolation to verify it returns data. Then check your JOIN conditions — particularly the direction (INNER vs. LEFT) and the column mapping. Finally, check date boundary logic: using <= vs. < on the end boundary is a common off-by-one error in date range queries.

Summary & Next Steps

You've just built genuine fluency with one of the most powerful patterns in analytical SQL: turning a complex, multi-step business question into a single query by nesting subqueries inside JOIN conditions and HAVING clauses.

Here's what you can now do with confidence:

Decompose any business question into atomic SQL building blocks before writing a single line of code
Embed subqueries in FROM as derived tables to pre-filter or pre-aggregate before joining
Use scalar subqueries in HAVING to compare group-level aggregates against dynamically computed benchmarks
Distinguish correlated from uncorrelated subqueries and understand the performance implications of each
Refactor nested subqueries into CTEs for maintainability without losing correctness
Recognize the anti-patterns that turn maintainable queries into undebuggable nightmares

The progression from here naturally leads in two directions. First, window functions: if you're annotating results with ranks, running totals, or lag comparisons, window functions are often cleaner and faster than correlated subqueries or self-joins — Window Functions: RANK, ROW_NUMBER, and LAG is the right next stop. Second, query optimization: for large-scale production queries, understanding how the database actually executes these plans matters enormously — SQL Query Optimization: Reading Execution Plans will help you stop guessing and start knowing.

The deeper habit to carry forward is this: always decompose before you write. The experts who write impressive single-query solutions didn't write them in one pass — they wrote them bottom-up, tested each layer, and then assembled. The finished query looks elegant because the thinking happened first. Your SQL editor is not a place to think; it's a place to transcribe thinking you've already done.

Turning a Many-Step Business Question into a Single SQL Query: Nesting Subqueries Inside JOIN Conditions and Aggregate Filters

Turning a Many-Step Business Question into a Single SQL Query: Nesting Subqueries Inside JOIN Conditions and Aggregate Filters

Introduction

Prerequisites

The Schema We'll Work With

Step One: Learning to Decompose the Business Question

Step Two: Building the Foundation — The Filtered Deal Set

Step Three: Nesting Subqueries Inside JOIN Conditions

Step Four: Nesting Subqueries Inside HAVING Clauses

Step Five: Correlated Subqueries — When the Subquery Needs to "See" the Outer Row

Step Six: Composing the Full Multi-Condition Query

Step Seven: When to Use CTEs Instead

Step Eight: Understanding Execution Order and Scope Rules

Step Nine: Performance Trade-Offs and Optimizer Behavior

Step Ten: Anti-Patterns and When the Rules Break Down

Anti-Pattern 1: Deeply Nesting Subqueries Inside Subqueries Inside Subqueries

Anti-Pattern 2: Correlated Subquery in a Loop-Like Context

Anti-Pattern 3: Repeating the Same Subquery Logic Three Times

Anti-Pattern 4: Using Subqueries in JOIN Conditions for Filtering That Belongs in WHERE

When the Rules Break Down: LEFT JOIN Filtering

Hands-On Exercise

Common Mistakes & Troubleshooting

"Column not found" in HAVING

The subquery returns more than one row

LEFT JOIN produces NULLs you didn't expect

Performance is terrible; the query takes forever

The same subquery appears multiple times and runs slowly

The query returns no rows when you expect rows

Summary & Next Steps

Related Insights

Debugging SQL Queries: How to Read Error Messages, Trace Wrong Results, and Fix Broken Joins Step by Step

Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions

Translating Business Questions into SQL: Decomposing Requirements into SELECT, JOIN, GROUP BY, and Subquery Steps