SQL WHERE vs HAVING: Same Results, Different Filtering Stages

Filter invoice rows or customer totals?

WHERE filters input rows before grouping. HAVING filters groups after aggregation. Sometimes they return exactly the same rows. That does not make them interchangeable: filtering one invoice amount and filtering a customer's total answer different questions.

Start with customer spending. First, keep customers 1–3 using either clause. Then ask which customers spent more than 45: that condition depends on the whole group, so HAVING is needed at this query level.

This describes SQL semantics. The optimizer can choose a different physical strategy while preserving those results.

The Chinook sample database

These examples use the complete Chinook music-store database available in the DataTug demo: 59 customers and 412 invoices, with every original table and value retained. All 59 customers have invoices. Queries that focus on one customer or qualifying invoices show that condition explicitly in the SQL.

The relationship is Customer.CustomerId → Invoice.CustomerId: one customer can have many invoices; each invoice belongs to one customer. InvoiceId is the invoice’s unique key. InvoiceDate is ISO-sortable text; Total is a numeric amount in the dataset’s units. This source uses dates from 2021–2025; other Chinook releases differ. Invoice IDs are not necessarily chronological.

Results are shown as a preview for clarity. Tables with more than 10 records initially show the first 10. Choose Show all to reveal every matching record, or Run to execute the exact displayed SQL in the DataTug editor against the same Chinook database. No query is saved to the project. The preview does not add a WHERE or LIMIT clause. Synthetic examples define their own rows with VALUES in the displayed query.

Download the complete Chinook SQLite database and open it with SQLite 3.25.0 or later, then run any query below. No account or network database is required. Query downloads contain every displayed example and exercise solution. Frozen result tables are checked against this database; each code header states its minimum SQLite version.

Chinook upstream and schema · Chinook licence · Source attribution. SQLite uses binary floating-point for many decimal operations; examples round money for display. Production finance needs an explicit exact-decimal or integer-minor-unit policy.

WHERE and HAVING can return the same results

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
WHERE CustomerId <= 3
GROUP BY CustomerId
ORDER BY CustomerId;

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
GROUP BY CustomerId
HAVING CustomerId <= 3
ORDER BY CustomerId;

Both queries return customer 1 with seven invoices totaling 39.62, customer 2 with seven totaling 37.62, and customer 3 with seven totaling 39.62. Both explicitly order by CustomerId.

The condition uses the grouping key. Every invoice in a customer group has the same CustomerId. Removing all invoices belonging to other customers before grouping therefore preserves the same three complete groups as removing those other groups afterward. This equivalence does not apply to an arbitrary invoice-level condition.

Use WHERE for this row predicate: it states which input belongs in the calculation. Reserve HAVING for conditions on the resulting groups.

Same output does not prove different cost

SELECT COUNT(*) AS AllInvoices, COUNT(DISTINCT CustomerId) AS AllCustomers,
       COUNT(CASE WHEN CustomerId <= 3 THEN 1 END) AS EligibleInvoices,
       COUNT(DISTINCT CASE WHEN CustomerId <= 3 THEN CustomerId END) AS EligibleCustomers
FROM Invoice;

Without early filtering, the logical input contains 412 invoices forming 59 customer groups. The WHERE condition selects 21 invoices forming three groups. In a plan that aggregates everything before discarding groups, the larger population can mean more aggregate updates and more intermediate group state. Sorting or hashing groups can consume memory and temporary storage; wider rows, more groups and larger inputs can increase that work.

But WHERE is not guaranteed to be physically faster. An optimizer may push a HAVING predicate on the grouping key down before aggregation. In our check with SQLite 3.53.4, prefixing either of the two queries above with EXPLAIN QUERY PLAN produced the same index-search detail: SEARCH Invoice USING INDEX IFK_InvoiceCustomerId (CustomerId<?). The existing customer index let both plans restrict the input. The 412-versus-21 counts describe logical populations, not evidence that the HAVING plan actually aggregates all 412 invoices.

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
GROUP BY CustomerId
HAVING MAX(CustomerId) <= 3
ORDER BY CustomerId;

Here MAX(CustomerId) is deliberately redundant: every row in each group has the same non-NULL customer key, so the result still matches the first pair. On SQLite 3.53.4 this form instead produced SCAN Invoice USING INDEX IFK_InvoiceCustomerId. It scanned the 412 invoice entries and aggregated 59 groups before retaining three, whereas the range-search forms restricted input to 21 invoices. This is an inspectable difference in work, not a timing measurement or a recommendation to wrap group keys in MAX. Other optimizer versions may rewrite it differently.

To inspect your environment, copy either query and put EXPLAIN QUERY PLAN before SELECT. Look for SEARCH versus SCAN, the chosen index and temporary grouping or sorting structures. Output wording can change between SQLite versions. A plan describes a strategy; it does not measure elapsed time. Compare repeated executions on representative data when timing matters, accounting for caches, selectivity and existing indexes. No speed benchmark is inferred from this small sample.

When WHERE cannot replace HAVING

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
GROUP BY CustomerId
HAVING SUM(Total) > 45
ORDER BY CustomerId;

This returns five customers: 6, 26, 45, 46 and 57. Each customer's total is calculated across all their invoices, and only then compared with 45. The threshold uses the unrounded sum; rounding is for display.

You cannot move SUM(Total) > 45 into the same SELECT's WHERE: that aggregate is not available at the row-filtering stage. SQLite rejects that use of an aggregate. Filtering individual Total > 45 values would instead ask about invoice amounts and would change the aggregate's population.

HAVING is required for this aggregate condition at this query level. An outer WHERE is also valid once a CTE or derived table has produced the customer totals as input columns:

WITH CustomerTotals AS (
  SELECT CustomerId, COUNT(*) AS InvoiceCount, SUM(Total) AS CustomerTotal
  FROM Invoice
  GROUP BY CustomerId
)
SELECT CustomerId, InvoiceCount, ROUND(CustomerTotal, 2) AS CustomerTotal
FROM CustomerTotals
WHERE CustomerTotal > 45
ORDER BY CustomerId;

The outer query returns the same five rows. Its WHERE sees CustomerTotal as an ordinary input column from CustomerTotals. This changes the query level; it does not make an aggregate legal in the inner WHERE. A CTE also does not promise materialization or a faster execution plan.

Use WHERE and HAVING together

SELECT CustomerId, COUNT(*) AS InvoiceCount, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
WHERE InvoiceDate >= '2025-01-01' AND InvoiceDate < '2026-01-01'
GROUP BY CustomerId
HAVING SUM(Total) > 10
ORDER BY CustomerId;

WHERE selects invoices dated in 2025 using an inclusive start and exclusive end. GROUP BY totals those eligible invoices per customer. HAVING keeps customers whose 2025 spending exceeds 10. The displayed result contains the exact matching customer IDs, invoice counts and totals.

This is a different population from the all-time example, not merely an alternative placement of its filter. Customers without eligible invoices form no group. The half-open range includes every timestamp on the final day without guessing its time precision. A useful date index may reduce the input read, but an index does not automatically eliminate grouping work; inspect the chosen plan.

Common mistakes

• Filtering amounts instead of totals: a WHERE condition on Total excludes individual invoices before SUM runs. HAVING on SUM(Total) tests the complete eligible group.
• Ungrouped columns: select grouping keys and aggregates. SQLite permits some bare columns, but an arbitrary InvoiceDate beside a customer sum is not a reliable group label and is rejected by many engines.
• Alias portability: SQLite accepts aggregate result aliases in HAVING, but support differs across engines. Repeating SUM(Total), as above, makes the intended aggregate explicit; an outer query can expose a named total portably.
• NULLs: WHERE and HAVING retain only true predicates; false and unknown are excluded. SUM ignores NULL amounts; if all amounts are NULL, SUM is NULL and its comparison does not pass. COUNT(*) counts rows, while COUNT(Total) counts non-NULL amounts. Chinook's invoice totals here are non-NULL.
• Rounding the condition: comparing rounded totals changes boundary behavior. Define the business threshold and numeric precision deliberately; these SQLite examples are not a prescription for financial storage types.
• Assuming faster syntax: fewer logical input rows can help, but predicate pushdown, selectivity and indexes determine the actual work. Check the plan before claiming a saving.

Practice choosing the filtering stage

SELECT CustomerId, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice
WHERE CustomerId <= 3
GROUP BY CustomerId
HAVING SUM(Total) > 38
ORDER BY CustomerId;

SELECT CustomerId, COUNT(*) AS InvoiceCount
FROM Invoice
WHERE InvoiceDate >= '2025-01-01' AND InvoiceDate < '2026-01-01'
GROUP BY CustomerId
HAVING COUNT(*) >= 2
ORDER BY CustomerId;

References and next steps

• SQLite SELECT: WHERE, GROUP BY and HAVING semantics
• SQLite EXPLAIN QUERY PLAN
• SQLite query optimizer overview

Download every example and solution · Related: JOINs vs Subqueries · Next: Window Functions · SQL & Data Skills.

Corrections: report an issue with the query, SQLite version and expected result. AI-assisted writing with automated SQL checks and independent technical review.