WHERE vs HAVING

Moving a Condition Changes the Result

WHERE and HAVING test at different boundaries.

highlighted = computed this step

Moving a condition

A row condition and a group condition can use a similar number but answer a different question.

move changes meaning\text{move changes meaning}

Compare both boundaries

WHERE qty >= 3 outputs 2 groups; HAVING total_qty >= 3 outputs 3 groups.

compare boundaries\text{compare boundaries}

HAVING examples are tiny finite group-filter transforms; SQL dialect completeness, optimizer behavior, indexing, and product claims are out of scope.

WHERE qty at least 3: base rowsidregionstatusqty1eastopen22eastopen33westopen14westclosed45northNULL26northopen1 WHERE before groupingsourceinputwhereTruthgroupedRole0[1, east, open, 2]FALSEdropped_before_grouping1[2, east, open, 3]TRUEgrouped2[3, west, open, 1]FALSEdropped_before_grouping3[4, west, closed, 4]TRUEgrouped4[5, north, NULL, 2]FALSEdropped_before_grouping5[6, north, open, 1]FALSEdropped_before_grouping GROUP BY bucketskeysourceRowsrowCount[east][1]1[west][3]1 Aggregate facts per groupkeysourceRowsaggregates[east][1]{n:1, total_qty:3}[west][3]{n:1, total_qty:4} HAVING result per groupkeypredicatetruthoutputRole[east]noneTRUEkept[west]noneTRUEkept HAVING output groupsregionntotal_qtyeast13west14 HAVING factsfactvalueinputRowCount6whereKeptRows2whereDroppedRows4groupCount2aggregateCount2outputGroupCount2havingTrueCount2havingFalseCount0havingUnknownCount0groupOrderfirst_seen_after_wherepipelineWHERE_before_GROUP_BY_then_HAVING
HAVING total at least 3: base rowsidregionstatusqty1eastopen22eastopen33westopen14westclosed45northNULL26northopen1 WHERE before groupingsourceinputwhereTruthgroupedRole0[1, east, open, 2]TRUEgrouped1[2, east, open, 3]TRUEgrouped2[3, west, open, 1]TRUEgrouped3[4, west, closed, 4]TRUEgrouped4[5, north, NULL, 2]TRUEgrouped5[6, north, open, 1]TRUEgrouped GROUP BY bucketskeysourceRowsrowCount[east][0, 1]2[west][2, 3]2[north][4, 5]2 Aggregate facts per groupkeysourceRowsaggregates[east][0, 1]{n:2, total_qty:5}[west][2, 3]{n:2, total_qty:5}[north][4, 5]{n:2, total_qty:3} HAVING result per groupkeypredicatetruthoutputRole[east]total_qty ge 3TRUEkept[west]total_qty ge 3TRUEkept[north]total_qty ge 3TRUEkept HAVING output groupsregionntotal_qtyeast25west25north23 HAVING factsfactvalueinputRowCount6whereKeptRows6whereDroppedRows0groupCount3aggregateCount2outputGroupCount3havingTrueCount3havingFalseCount0havingUnknownCount0groupOrderfirst_seen_after_wherepipelineWHERE_before_GROUP_BY_then_HAVING

Why they differ

WHERE removes small rows before totals. HAVING totals first, then tests the finished group.

before versus after\text{before versus after}

Summary

The boundary is part of the meaning, not a formatting choice.

same data, different boundary\text{same data, different boundary}