HAVING vs WHERE in SQL: Clause Differences with Examples

Short answer to having vs where sql: WHERE and HAVING target different stages of a SQL statement. WHERE filters individual rows before any GROUP BY happens; HAVING filters groups after aggregation. WHERE cannot reference an aggregate function like SUM or AVG; HAVING can. In practice, push predicates into WHERE to shrink the input early for better query performance, then use HAVING to keep or discard groups based on aggregated data. You can use both clauses in one query. The key difference is timing and scope in query execution. Below, youll see realistic sql code, examples on a 40M-row orders table, and patterns you can copy into production models.

WHERE vs HAVING at a glance

How SQL evaluates your query

Conceptual order of operations for data retrieval and query execution:

1. FROM / JOIN
2. WHERE (the clause is applied to filter records early)
3. GROUP BY
4. Aggregation (SUM, COUNT, AVG, etc.)
5. HAVING
6. SELECT
7. ORDER BY
8. LIMIT

Because WHERE happens first, that clause is used to filter rows before groups exist. HAVING happens after groups exist, so the HAVING clause filters groups (the aggregated result set). This also explains why WHERE cant see aggregates, and why HAVING can.

Core examples youll actually use

Example 1: Realistic funnel on a 40M-row orders table

Business question: Among US customers in 2025, which customers placed at least 3 orders and generated over $1,000 total revenue?

-- Good pattern: shrink early with WHERE, then keep groups with HAVING
SELECT
  customer_id,
  COUNT(DISTINCT order_id) AS orders_cnt,
  SUM(order_amount) AS revenue
FROM analytics.orders
WHERE order_date >= DATE '2025-01-01'           -- per-row filter
  AND country = 'US'                            -- per-row filter
GROUP BY customer_id                            -- group
HAVING COUNT(DISTINCT order_id) >= 3            -- aggregated filter
   AND SUM(order_amount) > 1000
ORDER BY revenue DESC
LIMIT 100;

Here WHERE does the heavy lifting: it cuts the 40M rows down to just the US rows in 2025, so the GROUP BY has far less work. Then HAVING enforces thresholds on the aggregated data.

Example 2: Why aggregates go in HAVING, not WHERE

This fails because the aggregate function appears in the WHERE clause:

-- Anti-pattern: WHERE cannot see aggregates
SELECT store_id
FROM analytics.orders
WHERE AVG(order_amount) > 50   -- will error in most engines
GROUP BY store_id;

Correct approach: put AVG in HAVING, after the GROUP BY.

-- Correct: aggregate then filter groups
SELECT store_id
FROM analytics.orders
GROUP BY store_id
HAVING AVG(order_amount) > 50;

Example 3: HAVING without GROUP BY

Many engines let you use HAVING with no GROUP BY to apply a condition to a single global aggregate:

-- Keep rows only if there exists any event at all
SELECT COUNT(*) AS total_events
FROM analytics.events
HAVING COUNT(*) > 0;  -- Checks an aggregate across the whole table

Technically valid. But if your goal is just existence, consider LIMIT 1 or EXISTS depending on the engine and optimizing sql queries goals.

Example 4: WHERE vs HAVING when both could work

These two are not equal in cost:

-- Slower if large table: predicate waits until after aggregation
SELECT product_id
FROM analytics.orders
GROUP BY product_id
HAVING MIN(order_date) >= DATE '2025-01-01';

-- Better: push the predicate to WHERE
SELECT product_id
FROM analytics.orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY product_id;

Both return the same query results because the minimum will necessarily be on or after the filter. But the second query can prune partitions and indexes earlier.

Combining WHERE and HAVING for clarity and speed

You can and often should combine both clauses: use WHERE to filter individual rows and HAVING to filter aggregated groups.

-- Task: country-specific new customers with high LTV
WITH base AS (
  SELECT
    customer_id,
    order_date,
    order_amount
  FROM analytics.orders
  WHERE order_date >= DATE '2025-01-01'    -- use the where clause for early filters
    AND country = 'CA'
)
SELECT
  customer_id,
  COUNT(*) AS orders_cnt,
  SUM(order_amount) AS revenue,
  AVG(order_amount) AS avg_order
FROM base
GROUP BY customer_id
HAVING SUM(order_amount) > 500               -- filter aggregated metric
   AND COUNT(*) >= 2;

Pattern to remember: WHERE trims the input; HAVING decides which groups survive. This holds across most SQL queries and engines.

Cheat sheet: when to use each clause in SQL

• Row filters like date ranges, status, region, id lists: WHERE
• Thresholds on SUM/COUNT/AVG per group: HAVING
• Distinct-count requirements per customer/store: HAVING
• Time windows that can be applied to rows (e.g., order_date >= ...): WHERE
• Derived metrics requiring aggregation (e.g., revenue per user > X): HAVING
• Window-function post-processing: neither; use the qualify clause where available

Common mistakes (and quick fixes)

• Using HAVING for non-aggregate row filters:

  -- Works but can be slower: no need to wait until after GROUP BY
  SELECT country, COUNT(*)
  FROM analytics.orders
  GROUP BY country
  HAVING country = 'US';
  
  -- Prefer
  SELECT country, COUNT(*)
  FROM analytics.orders
  WHERE country = 'US'
  GROUP BY country;
  

  The second version avoids grouping all countries before throwing most away. Its simpler SQL filtering and usually faster.
• Expecting WHERE to accept aggregates: move those checks to HAVING or precompute them in a subquery/CTE.
• Relying on SELECT aliases inside HAVING: some engines allow it, others dont. Use the full aggregate expression in HAVING for portability.
• Confusing HAVING with the qualify clause: QUALIFY filters results of window functions after SELECT. Its a different clause in SQL than HAVING; use it for windowed ranks, tops, and deduping. See Window Functions Explained for a deep dive.

Performance tips: make the database do less

Which is faster, HAVING or WHERE? In general, WHERE is faster because it runs earlier and can leverage indexes and partitions to reduce the scanned rows before the GROUP BY. HAVING runs after aggregation, so its unavoidable when the decision depends on an aggregate function. For optimizing sql queries:

• Push predicates into WHERE whenever possible. Dont use HAVING for simple equality or range filters that could be applied to rows before groups.
• Write sargable predicates to help index usage: avoid wrapping columns with functions in WHERE (e.g., dont do DATE(order_date) = '2025-01-01' if order_date is already a date).
• Partition pruning beats post-aggregation filtering. In warehouses, date predicates in WHERE can skip entire partitions.
• Consider CTEs to isolate row filters from aggregations for readability (the planner often inlines them anyway).
• Test locally with DuckDB when iterating; then validate warehouse plans and costs.
• Review indexes for OLTP systems; see Clustered vs Non-Clustered Indexes for guidance.
• Warehouse-specific tuning matters; see BigQuery SQL Best Practices for more on cost and query performance.

In short, the clause is used to filter as early as correctness allows. Thats the simplest lever for faster data retrieval and cleaner query results.

Dialect notes youll bump into

• Snowflake/BigQuery support QUALIFY for windowed post-processing; use HAVING only for group-level checks, QUALIFY for row selection after windows.
• PostgreSQL/MySQL/SQL Server have no QUALIFY. Use subqueries/CTEs or filter in an outer SELECT instead.
• Most engines let you use HAVING without GROUP BY to check a single-table aggregate. Keep it readable and intentional.
• Some engines allow HAVING to reference non-aggregated columns if they are functionally dependent on the GROUP BY. Prefer explicitness for portability across data in SQL.

FAQ: HAVING vs WHERE in SQL

Is HAVING an alternative to WHERE in SQL?

No. WHERE filters rows before groups exist; HAVING filters groups after aggregation. They are complementary, not interchangeable.

Which is faster, HAVING or WHERE?

WHERE is usually faster because it reduces input earlier, often leveraging indexes/partitions. HAVING is necessary for group conditions. Use both when appropriate to improve query results and throughput.

Can we use HAVING and WHERE together in SQL?

Yes. Combine them: WHERE for per-row predicates, HAVING for group thresholds. See the combined example above.

When should I use HAVING in SQL?

Use HAVING when keeping or discarding a group depends on an aggregate function result (e.g., SUM, COUNT, AVG). Thats exactly what HAVING is used with aggregate checks for.

But, is that only their key difference?

Primarily, yes: timing and scope. WHERE acts on rows; HAVING acts on groups. This key difference drives correctness and performance.

Can I use HAVING without GROUP BY?

Yes in many engines. It applies a condition to a global aggregate. Use it sparingly for clarity.

Can WHERE work with aggregate functions like HAVING?

No. WHERE cannot reference aggregate functions directly. Put those conditions in HAVING or use a subquery that computes the aggregates first.

Can I use both HAVING and WHERE in SQL in the same query?

Yes. WHERE first, GROUP BY and aggregation next, then HAVING.

HAVING or WHERE in SQL: Which Is Faster?

WHERE, when the predicate can be applied at the row level. If the rule depends on aggregated data, HAVING is required.

From concept to practice: a mini playbook

1. Identify row filters you can safely push to WHERE (dates, IDs, statuses). This helps engines skip work.
2. Decide your grouping keys carefully. GROUP BY on high-cardinality columns can be expensive.
3. Keep group thresholds in HAVING only. If you can restate a HAVING predicate as a row predicate without changing logic, move it.
4. Read the plan. Even simple changes can affect optimizing sql queries materially.

Additional examples to cement the idea

Filter rows first, then filter grouped metrics

-- Goal: US stores with >= 10 orders and avg order >= $75 in Q1
SELECT store_id,
       COUNT(*) AS orders_cnt,
       AVG(order_amount) AS avg_order
FROM analytics.orders
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-03-31'  -- filter rows
  AND country = 'US'
GROUP BY store_id
HAVING COUNT(*) >= 10
   AND AVG(order_amount) >= 75;                                  -- filter aggregated

SKU-level daily metrics with both clauses

-- Goal: Daily SKUs with at least 50 units sold, excluding returns
SELECT
  sku,
  order_date,
  SUM(CASE WHEN is_return = FALSE THEN quantity ELSE 0 END) AS units_sold
FROM analytics.order_items
WHERE order_date >= DATE '2025-04-01'                 -- shrink the data set
  AND status IN ('shipped','delivered')               -- filtering data early
GROUP BY sku, order_date
HAVING SUM(CASE WHEN is_return = FALSE THEN quantity ELSE 0 END) >= 50;

One last nuance: WHERE, HAVING, and window functions

HAVING only sees group aggregates. Window functions are computed per row after GROUP BY in a different step. If you need to keep only the latest row per customer, use a window and, in dialects that support it, the QUALIFY clause:

-- Latest order per customer
SELECT customer_id, order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM analytics.orders
QUALIFY rn = 1;  -- Not HAVING; QUALIFY filters windowed rows

If QUALIFY isnt available, wrap in an outer SELECT with a WHERE on rn = 1. For a full walkthrough of window patterns, see Window Functions Explained.

Search-specific wrap-up

If your query must decide based on specified conditions computed over groups (SUM, COUNT, AVG), use HAVING. If its about filtering individual rows (dates, IDs), use WHERE. Thats the heart of having vs where sql. The WHERE clause filters early; the HAVING clause filters late. Learn both well and your sql code stays correct and fast. If you manage costs and performance at scale, also see BigQuery SQL Best Practices and our BigQuery Cost Optimization primer.

Related learning

• New to SQL? Start at our SQL topic hub.
• Warehouse tuning and patterns: BigQuery SQL Best Practices for Analysts.
• Index mechanics for OLTP workloads: Clustered vs Non-Clustered Indexes.
• Working with XML in SQL Server? See Working with XML in SQL Server for Analytics.
• Try ideas locally with DuckDB for quick iteration.

Notes: WHERE is the per-row filter and HAVING is the group-level filterthats the fundamental clause in SQL distinction to remember. This guidance applies broadly across engines. For more practice on this topic and graded feedback, try the free exercises at /practice.