WHERE vs HAVING

UNKNOWN Is Not TRUE

UNKNOWN drops at both row and group boundaries.

highlighted = computed this step

UNKNOWN is not TRUE

At both boundaries, only TRUE continues.

TRUE only\text{TRUE only}

Read UNKNOWN rows and groups

WHERE marks 1 row UNKNOWN; HAVING marks 3 groups UNKNOWN and outputs 0 groups.

UNKNOWN does not pass\text{UNKNOWN does not pass}

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

WHERE UNKNOWN is not TRUE: base rowsidregionstatusqty1eastopen22eastopen33westopen14westclosed45northNULL26northopen1 WHERE before groupingsourceinputwhereTruthgroupedRole0[1, east, open, 2]TRUEgrouped1[2, east, open, 3]TRUEgrouped2[3, west, open, 1]TRUEgrouped3[4, west, closed, 4]FALSEdropped_before_grouping4[5, north, NULL, 2]UNKNOWNdropped_before_grouping5[6, north, open, 1]TRUEgrouped GROUP BY bucketskeysourceRowsrowCount[east][0, 1]2[west][2]1[north][5]1 Aggregate facts per groupkeysourceRowsaggregates[east][0, 1]{n:2, total_qty:5}[west][2]{n:1, total_qty:1}[north][5]{n:1, total_qty:1} HAVING result per groupkeypredicatetruthoutputRole[east]noneTRUEkept[west]noneTRUEkept[north]noneTRUEkept HAVING output groupsregionntotal_qtyeast25west11north11 HAVING factsfactvalueinputRowCount6whereKeptRows4whereDroppedRows2groupCount3aggregateCount2outputGroupCount3havingTrueCount3havingFalseCount0havingUnknownCount0groupOrderfirst_seen_after_wherepipelineWHERE_before_GROUP_BY_then_HAVING
HAVING UNKNOWN is not TRUE: 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 gt NULLUNKNOWNdropped[west]total_qty gt NULLUNKNOWNdropped[north]total_qty gt NULLUNKNOWNdropped HAVING output groupsregionntotal_qtynonenonenone HAVING factsfactvalueinputRowCount6whereKeptRows6whereDroppedRows0groupCount3aggregateCount2outputGroupCount0havingTrueCount0havingFalseCount0havingUnknownCount3groupOrderfirst_seen_after_wherepipelineWHERE_before_GROUP_BY_then_HAVING

Same rule, two places

UNKNOWN rows do not group, and UNKNOWN groups do not output.

not TRUE means dropped\text{not TRUE means dropped}

Summary

This mirrors the earlier WHERE truth table, now shown at the group boundary too.

row and group boundary\text{row and group boundary}