Question 8 of 20 Fundamentals Concept

What is the difference between WHERE and HAVING?

Short answer

WHERE filters individual rows before they are grouped and aggregated. HAVING filters whole groups after the aggregates are computed. So an aggregate such as COUNT can appear in HAVING but never in WHERE.

Why

A grouped query runs in a fixed order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT. The PostgreSQL tutorial states the difference plainly. WHERE controls which rows go into the aggregate calculation, and HAVING selects group rows after the aggregates exist.

Consider a table of qPCR wells. WHERE ct IS NOT NULL removes failed wells before averaging, so each sample's mean Ct is computed from valid wells only. HAVING COUNT(*) >= 3 then keeps only samples that still have at least three valid replicates.

Swapping them changes the scientific meaning. Putting the replicate count in WHERE is a syntax error, and putting a row condition in HAVING when it could go in WHERE makes the database group rows it will throw away. A condition on a grouping column, such as plate_id = 'P07', gives the same result in either clause, but in WHERE it can use an index and shrinks the work early.

The order also explains why a column alias defined in SELECT cannot be used in WHERE, because SELECT has not run yet.

A strong answer covers

WHERE acts on rows before grouping
HAVING acts on groups after aggregation
Aggregates are not allowed in WHERE
Logical order FROM, WHERE, GROUP BY, HAVING, SELECT
Move row filters to WHERE for efficiency

Common mistakes

Putting a row-level quality filter in HAVING, which changes which rows feed the aggregate
Expecting a SELECT alias to be usable in the WHERE clause

The interviewer might ask next

How would you return samples whose mean Ct is below 30 using only wells that passed QC?