Filtering Joined Results: Applying WHERE and HAVING Conditions Across Multiple Tables

Introduction

Picture this: your manager asks for a report showing every customer who placed more than five orders last year, but only from the "Enterprise" customer tier, and only from regions where total revenue exceeded $50,000. You have three tables — customers, orders, and regions — and you need to combine filtering that happens before aggregation, after aggregation, and across table boundaries all in a single query.

This is the moment where a lot of SQL beginners freeze. You know how to filter one table with WHERE. You know how to join two tables together. But when those two skills collide — and then HAVING shows up to filter on aggregate results — things get complicated fast. Which filter goes where? Why does the database throw an error when you use an alias in a WHERE clause? Why does the same condition in WHERE produce different results than in HAVING?

By the end of this lesson, you'll understand exactly how SQL processes multi-table queries, why WHERE and HAVING serve different purposes, and how to place your conditions precisely to get the answer you're actually asking for. You'll write queries that filter rows before joining, after joining, and after grouping — with full confidence in why each piece works.

What you'll learn:

How SQL executes a multi-table query step by step (and why that order matters)
When to use WHERE versus HAVING, and why they are not interchangeable
How to filter on columns from different tables in the same query
How to combine row-level filters (WHERE) with group-level filters (HAVING)
How to avoid the most common mistakes that produce wrong results silently

Prerequisites

You should be comfortable with the core building blocks before diving in:

Writing basic SELECT queries — covered in SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries
Joining tables together — covered in SQL JOINs Explained with Real-World Examples
Grouping and aggregating data — covered in Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners

If any of those feel shaky, spend twenty minutes there first. Everything in this lesson builds on top of them.

How SQL Actually Processes a Multi-Table Query

Before you can filter correctly, you need to understand what SQL is doing under the hood. SQL does not execute in the order you write it. You write SELECT first, but SELECT is actually evaluated near the end. Here's the logical order SQL uses:

FROM — identify the source tables
JOIN — combine rows across tables
WHERE — filter individual rows from the joined result
GROUP BY — collapse rows into groups
HAVING — filter groups
SELECT — compute the output columns
ORDER BY — sort the final result

This order is the key to everything. WHERE runs before grouping, which means it cannot see aggregate results like SUM() or COUNT(). HAVING runs after grouping, which means it can see aggregates — but it can only filter at the group level, not the row level.

Key insight

WHERE filters rows. HAVING filters groups. They operate at different stages of query execution, and mixing them up is the most common source of confusing results in SQL.

Let's build a realistic schema to explore this properly.

The Sample Schema

We'll use a small e-commerce dataset throughout this lesson. Here's the structure:

-- Customers table
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100),
    tier          VARCHAR(20),   -- 'Standard', 'Premium', 'Enterprise'
    region_id     INT
);

-- Regions table
CREATE TABLE regions (
    region_id     INT PRIMARY KEY,
    region_name   VARCHAR(50)
);

-- Orders table
CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    total_amount  DECIMAL(10, 2),
    status        VARCHAR(20)    -- 'completed', 'cancelled', 'pending'
);

Some sample data to reason about:

Customers include accounts across Standard, Premium, and Enterprise tiers spread across four regions.
Each customer has placed multiple orders over two years.
Some orders are cancelled; some are pending; most are completed.

Filtering with WHERE Across Multiple Tables

When you join two or more tables, the WHERE clause can reference columns from any of them. SQL first combines all the rows through the JOIN, then WHERE discards any row that doesn't meet your conditions.

Let's start with a simple example: find all completed orders placed by Enterprise-tier customers.

SELECT
    c.customer_name,
    c.tier,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.tier = 'Enterprise'
  AND o.status = 'completed';

Here, c.tier = 'Enterprise' filters on a column from the customers table, and o.status = 'completed' filters on a column from the orders table. Both conditions apply to the same row — the joined row that contains information from both tables simultaneously.

Tip

Always use table aliases (like c for customers and o for orders) when querying multiple tables. It makes clear which table each column belongs to and prevents ambiguity errors when both tables have a column with the same name (like customer_id). Learn more about this in Using SQL Aliases Effectively: Naming Columns and Tables for Readable, Maintainable Queries.

Now let's pull in the regions table and add a region filter:

SELECT
    c.customer_name,
    c.tier,
    r.region_name,
    o.order_id,
    o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN regions r ON c.region_id = r.region_id
WHERE c.tier = 'Enterprise'
  AND o.status = 'completed'
  AND r.region_name IN ('Northeast', 'West');

Three conditions, three different conceptual sources — but because WHERE operates on the fully joined row, all of it works in a single WHERE clause. SQL sees each row as a combined unit containing columns from all three tables.

Filtering Before vs. After the JOIN

There's a subtlety here that trips up intermediate SQL writers: you can sometimes put filter conditions in the WHERE clause or directly in the JOIN's ON condition, and the results can differ depending on whether you're using an INNER JOIN or an OUTER JOIN.

With an INNER JOIN, these two queries return identical results:

-- Filter in WHERE
SELECT c.customer_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

-- Filter in ON clause
SELECT c.customer_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
  AND o.status = 'completed';

But with a LEFT JOIN, they diverge:

-- Filter in WHERE: customers with NO completed orders disappear entirely
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

-- Filter in ON clause: customers with no completed orders stay, with NULLs
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
  AND o.status = 'completed';

In the second query, the condition o.status = 'completed' is part of the join logic. SQL will still include every customer row, but only match order rows where the status is 'completed'. Customers with no completed orders appear with NULL in the order_id column.

In the first query, the WHERE clause filters after the join. Rows where o.status is NULL (because there was no matching order) get eliminated entirely.

Warning

Moving a condition from WHERE to the ON clause of a LEFT JOIN is not a cosmetic change — it fundamentally changes what rows appear in your results. If you're losing rows you expect to keep, this is often why. Check out Choosing the Right JOIN Type: When to Use INNER, LEFT, and FULL OUTER JOIN for Clean Aggregation Results for a deep dive on this.

Introducing HAVING: Filtering After Aggregation

Now let's handle the part that WHERE genuinely cannot do: filtering based on aggregate values.

The goal: find Enterprise customers who have placed more than five completed orders.

Here's the broken attempt most beginners write first:

-- THIS WILL FAIL
SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.tier = 'Enterprise'
  AND o.status = 'completed'
  AND COUNT(o.order_id) > 5;   -- ERROR: aggregate functions not allowed in WHERE

Your database will throw an error on that last line. WHERE runs before GROUP BY, so the aggregated counts don't exist yet when WHERE is being evaluated. There are no groups to count at that stage.

The correct approach uses HAVING:

SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.tier = 'Enterprise'
  AND o.status = 'completed'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 5
ORDER BY order_count DESC;

Notice how WHERE and HAVING are doing different jobs here:

WHERE pre-filters rows down to Enterprise customers with completed orders before any grouping happens.
GROUP BY then collapses the remaining rows into one row per customer.
HAVING then checks: does this customer's group have more than 5 orders?

This is far more efficient than the alternative of not using WHERE at all — if you skip the WHERE filter and let GROUP BY process all tiers and all statuses, you're doing unnecessary aggregation work just to throw those groups away with HAVING afterward.

Tip

Use WHERE to reduce the number of rows as early as possible, and use HAVING only to filter on aggregated results. Pre-filtering with WHERE is almost always faster because it shrinks the dataset before grouping begins.

Combining WHERE and HAVING on Multi-Table Queries

Let's now tackle the full scenario from the introduction: customers with more than five completed orders, from the Enterprise tier, in regions where total revenue exceeded $50,000.

That last condition — "regions where total revenue exceeded $50,000" — is itself an aggregate filter, but it's at the region level, not the customer level. Let's work through this step by step.

First, let's solve the per-customer part:

SELECT
    c.customer_id,
    c.customer_name,
    r.region_name,
    COUNT(o.order_id)    AS order_count,
    SUM(o.total_amount)  AS total_revenue
FROM customers c
JOIN orders o  ON c.customer_id = o.customer_id
JOIN regions r ON c.region_id   = r.region_id
WHERE c.tier = 'Enterprise'
  AND o.status = 'completed'
GROUP BY c.customer_id, c.customer_name, r.region_name
HAVING COUNT(o.order_id) > 5;

This gets us Enterprise customers with more than five completed orders, along with their region and total revenue. But we still need to exclude regions where the regional total is below $50,000.

One clean approach is to use a subquery to identify qualifying regions first, then filter with an IN clause:

SELECT
    c.customer_id,
    c.customer_name,
    r.region_name,
    COUNT(o.order_id)   AS order_count,
    SUM(o.total_amount) AS customer_revenue
FROM customers c
JOIN orders o  ON c.customer_id = o.customer_id
JOIN regions r ON c.region_id   = r.region_id
WHERE c.tier = 'Enterprise'
  AND o.status = 'completed'
  AND r.region_id IN (
        SELECT c2.region_id
        FROM customers c2
        JOIN orders o2 ON c2.customer_id = o2.customer_id
        WHERE o2.status = 'completed'
        GROUP BY c2.region_id
        HAVING SUM(o2.total_amount) > 50000
  )
GROUP BY c.customer_id, c.customer_name, r.region_name
HAVING COUNT(o.order_id) > 5
ORDER BY total_revenue DESC;

The subquery in the WHERE clause computes total completed revenue per region across all customers and returns only the region IDs that pass the $50,000 threshold. The outer query then uses that list to pre-filter before it even starts grouping.

For a deeper exploration of patterns like this, see Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements.

Note

You could also solve the regional revenue requirement with a second HAVING condition if you group by region in a separate step. The right approach depends on whether you want results at the customer level or the region level. When the granularity of the filter differs from the granularity of the output, a subquery is usually cleaner.

A Practical Walkthrough: Sales Rep Performance Report

Let's do one more fully worked example to cement the concepts. Imagine a B2B sales scenario with these tables:

-- Sales reps
sales_reps (rep_id, rep_name, department)

-- Accounts (companies the reps manage)
accounts (account_id, account_name, rep_id, industry)

-- Deals
deals (deal_id, account_id, close_date, deal_value, stage)

Business question: Find all sales reps in the "Commercial" department who closed at least 3 deals in 2023 with a combined value above $100,000, but only count deals in the "Won" stage.

Let's build this up deliberately:

Step 1 — Identify your tables and joins:

FROM sales_reps sr
JOIN accounts a  ON sr.rep_id     = a.rep_id
JOIN deals d     ON a.account_id  = d.account_id

Step 2 — Add row-level filters in WHERE:

WHERE sr.department = 'Commercial'
  AND d.stage = 'Won'
  AND d.close_date BETWEEN '2023-01-01' AND '2023-12-31'

Step 3 — Group and aggregate:

GROUP BY sr.rep_id, sr.rep_name

Step 4 — Add group-level filters in HAVING:

HAVING COUNT(d.deal_id) >= 3
   AND SUM(d.deal_value) > 100000

All together:

SELECT
    sr.rep_name,
    COUNT(d.deal_id)    AS deals_won,
    SUM(d.deal_value)   AS total_value,
    AVG(d.deal_value)   AS avg_deal_value
FROM sales_reps sr
JOIN accounts a ON sr.rep_id    = a.rep_id
JOIN deals d    ON a.account_id = d.account_id
WHERE sr.department = 'Commercial'
  AND d.stage       = 'Won'
  AND d.close_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY sr.rep_id, sr.rep_name
ORDER BY total_value DESC;

Wait — we forgot the HAVING clause! This is actually a useful mistake to illustrate. Without HAVING, this query returns all Commercial reps who closed any Won deals in 2023. The WHERE clause already handled the stage and date filters, but the count and value thresholds are group-level conditions that belong in HAVING. The complete version:

SELECT
    sr.rep_name,
    COUNT(d.deal_id)    AS deals_won,
    SUM(d.deal_value)   AS total_value,
    AVG(d.deal_value)   AS avg_deal_value
FROM sales_reps sr
JOIN accounts a ON sr.rep_id    = a.rep_id
JOIN deals d    ON a.account_id = d.account_id
WHERE sr.department = 'Commercial'
  AND d.stage       = 'Won'
  AND d.close_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY sr.rep_id, sr.rep_name
HAVING COUNT(d.deal_id) >= 3
   AND SUM(d.deal_value) > 100000
ORDER BY total_value DESC;

For more on writing HAVING conditions that answer real analytical questions, see Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions.

Hands-On Exercise

Use the following tables for this exercise. You can create them in any SQL environment (SQLite, PostgreSQL, MySQL, etc.):

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50)
);

CREATE TABLE order_lines (
    line_id     INT PRIMARY KEY,
    order_id    INT,
    product_id  INT,
    quantity    INT,
    unit_price  DECIMAL(10, 2)
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    order_date   DATE,
    channel      VARCHAR(20)   -- 'online', 'in-store', 'wholesale'
);

Insert some sample data:

INSERT INTO products VALUES (1, 'Wireless Headphones', 'Electronics');
INSERT INTO products VALUES (2, 'USB-C Cable', 'Electronics');
INSERT INTO products VALUES (3, 'Notebook Set', 'Stationery');
INSERT INTO products VALUES (4, 'Desk Lamp', 'Home Office');

INSERT INTO orders VALUES (101, 1, '2024-03-15', 'online');
INSERT INTO orders VALUES (102, 2, '2024-04-20', 'wholesale');
INSERT INTO orders VALUES (103, 1, '2024-05-10', 'online');
INSERT INTO orders VALUES (104, 3, '2024-06-01', 'in-store');
INSERT INTO orders VALUES (105, 2, '2024-07-18', 'wholesale');

INSERT INTO order_lines VALUES (1, 101, 1, 2, 79.99);
INSERT INTO order_lines VALUES (2, 101, 2, 5, 12.99);
INSERT INTO order_lines VALUES (3, 102, 1, 10, 79.99);
INSERT INTO order_lines VALUES (4, 102, 3, 20, 8.49);
INSERT INTO order_lines VALUES (5, 103, 4, 3, 34.99);
INSERT INTO order_lines VALUES (6, 104, 2, 2, 12.99);
INSERT INTO order_lines VALUES (7, 105, 1, 15, 79.99);
INSERT INTO order_lines VALUES (8, 105, 4, 8, 34.99);

Your tasks:

Write a query that returns all products in the "Electronics" category, along with the total quantity sold across all channels. Show only products where total quantity sold is greater than 10.
Modify your query from Task 1 to only count sales made through the "wholesale" channel. (Think carefully: does your filter condition belong in WHERE or HAVING?)
Write a query that returns the total revenue (quantity × unit_price) per channel, but only for orders placed in Q2 2024 (April through June), and only show channels where total revenue exceeded $500.

Answers:

-- Task 1
SELECT
    p.product_name,
    SUM(ol.quantity) AS total_qty_sold
FROM products p
JOIN order_lines ol ON p.product_id = ol.product_id
WHERE p.category = 'Electronics'
GROUP BY p.product_id, p.product_name
HAVING SUM(ol.quantity) > 10;

-- Task 2 (filter on channel belongs in WHERE, not HAVING)
SELECT
    p.product_name,
    SUM(ol.quantity) AS total_qty_sold
FROM products p
JOIN order_lines ol ON p.product_id  = ol.product_id
JOIN orders o       ON ol.order_id   = o.order_id
WHERE p.category  = 'Electronics'
  AND o.channel   = 'wholesale'
GROUP BY p.product_id, p.product_name
HAVING SUM(ol.quantity) > 10;

-- Task 3
SELECT
    o.channel,
    SUM(ol.quantity * ol.unit_price) AS total_revenue
FROM orders o
JOIN order_lines ol ON o.order_id = ol.order_id
WHERE o.order_date BETWEEN '2024-04-01' AND '2024-06-30'
GROUP BY o.channel
HAVING SUM(ol.quantity * ol.unit_price) > 500;

Common Mistakes & Troubleshooting

Mistake 1: Using an aggregate in WHERE

-- Wrong
WHERE COUNT(o.order_id) > 5

-- Right
HAVING COUNT(o.order_id) > 5

The fix is always to move aggregate conditions to HAVING. If you need to filter on an aggregate-derived value in a WHERE clause (for an outer query), wrap it in a subquery or CTE.

Mistake 2: Referencing a SELECT alias in WHERE or HAVING

-- Wrong (most databases won't allow this)
SELECT SUM(total_amount) AS revenue
...
HAVING revenue > 50000;

-- Right: repeat the expression
HAVING SUM(total_amount) > 50000;

Because SELECT runs after WHERE and (in most databases) after HAVING too, the alias doesn't exist at the time those clauses are evaluated. PostgreSQL and MySQL handle this slightly differently, but repeating the expression is the safest cross-database approach.

Warning

Some databases like MySQL allow you to use SELECT aliases in HAVING, but this is a non-standard extension. If you write code that depends on it, it may break when you switch databases. Stick to repeating the aggregate expression in HAVING.

Mistake 3: Forgetting to include GROUP BY columns in SELECT

If you group by customer_id and customer_name but only select customer_name, you may get unexpected results or errors depending on your database. As a rule, everything in your SELECT that isn't an aggregate should appear in your GROUP BY.

Mistake 4: Confusing row-level and group-level filtering

A filter like o.status = 'completed' is a row-level filter — it should go in WHERE. A filter like COUNT(o.order_id) > 5 is a group-level filter — it must go in HAVING. When you're unsure, ask yourself: "Am I filtering individual rows, or am I filtering a summarized group?" That question almost always resolves the ambiguity.

Mistake 5: Unexpected row multiplication from JOINs

When you join a one-to-many relationship (one customer, many orders) and then aggregate, the join is working correctly — but you need to make sure you're grouping at the right level. If you see suspiciously large SUM values, it's possible you've accidentally joined across another one-to-many relationship and each row is being counted multiple times. Check your joins carefully and count rows at each stage to diagnose it.

Tip

If you suspect your aggregates are inflated due to a bad join, add a COUNT(*) to your SELECT and compare it to what you expect. If you're seeing thousands of rows when you expect dozens, trace the join path — you likely have an unintended cartesian expansion somewhere. Debugging SQL Queries: How to Read Error Messages, Trace Wrong Results, and Fix Broken Joins Step by Step walks through this systematically.

Summary & Next Steps

You now have a complete mental model for filtering across joined tables:

SQL executes in a defined logical order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Understanding this order is the foundation of writing correct filters.
WHERE filters rows from the joined result, before any grouping happens. It can reference columns from any of the joined tables, but it cannot see aggregate results.
HAVING filters groups, after GROUP BY has collapsed rows into summaries. It's designed specifically for conditions on aggregate functions like COUNT, SUM, and AVG.
Both can coexist in the same query. Use WHERE to reduce the row set early and efficiently; use HAVING to apply business rules at the group level.
JOIN conditions (ON clause) vs. WHERE matter when using OUTER JOINs — a condition in WHERE can eliminate rows that a LEFT JOIN was designed to preserve.

These patterns are the backbone of nearly every analytical query you'll write in a professional data role. The next natural step is learning how to extend these ideas into multi-step queries — building subqueries and CTEs that let you filter at one level and then aggregate at another. That's exactly what Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY to Answer Real Business Questions covers.

You should also look at Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG to go deeper on the aggregation side of these queries, and explore Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query for more complex real-world reporting patterns.

Filtering Joined Results: Applying WHERE and HAVING Conditions Across Multiple Tables

Filtering Joined Results: Applying WHERE and HAVING Conditions Across Multiple Tables

Introduction

Prerequisites

How SQL Actually Processes a Multi-Table Query

The Sample Schema

Filtering with WHERE Across Multiple Tables

Filtering Before vs. After the JOIN

Introducing HAVING: Filtering After Aggregation

Combining WHERE and HAVING on Multi-Table Queries

A Practical Walkthrough: Sales Rep Performance Report

Hands-On Exercise

Common Mistakes & Troubleshooting

Summary & Next Steps

Related Insights

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

Aggregating Data from Multiple Subqueries: Combining Scalar and Derived Table Patterns in a Single SELECT

Counting and Grouping with DISTINCT vs GROUP BY: When to Use Each and Why It Matters