In a per-department salary query, how does WHERE salary > 50000 differ from HAVING AVG(salary) > 50000?

## Two English questions, two SQL clauses "Show me departments with high earners" and "show me departments that pay well on average" sound close in English and are entirely different queries. The first is a row filter, the second a group filter. ```sql -- A: departments containing at least one employee paid over 50000 SELECT dept, AVG(salary) AS avg_salary FROM employees WHERE salary > 50000 GROUP BY dept; -- B: departments whose average salary exceeds 50000 SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept HAVING AVG(salary) > 50000; ``` ## Working through data Take three departments: - Sales: 90000 and 30000 — true average 60000 - IT: 60000 and 40000 — true average 50000 - HR: 40000 and 30000 — true average 35000 Query B returns Sales with 60000. IT's average is exactly 50000, which is not greater than 50000, and HR is far below. Query A first deletes every row at or below 50000, leaving one Sales row (90000) and one IT row (60000) and nothing for HR. It then returns Sales with 90000 and IT with 60000. So the two queries disagree about which departments qualify (IT appears in one and not the other) **and** about Sales's average (90000 versus 60000). Nothing about the data changed; only the stage at which the predicate was applied. ## The general rule `WHERE` changes which rows feed the aggregates, so it changes the aggregate values themselves. `HAVING` never changes a value — it only decides whether an already-computed group survives. Any time a filter moves across the grouping boundary, expect the numbers to move with it. A related trap is the average-of-a-filtered-set being self-fulfilling: once you have kept only salaries above 50000, `AVG(salary) > 50000` is guaranteed true and tells you nothing. Candidates who write `WHERE salary > 50000 ... HAVING AVG(salary) > 50000` and believe they have applied two checks have applied one and a tautology. ## Combining them deliberately Using both is legitimate when each expresses a genuinely different intent: ```sql -- among full-time staff only, departments averaging over 50000 SELECT dept, AVG(salary) AS avg_salary, COUNT(*) AS headcount FROM employees WHERE employment_type = 'FULL_TIME' GROUP BY dept HAVING AVG(salary) > 50000 AND COUNT(*) >= 5; ``` Read it as a pipeline: `WHERE` defines the population, `GROUP BY` defines the unit of reporting, aggregates summarise the population per unit, `HAVING` picks the units worth showing. Here `COUNT(*) >= 5` suppresses departments too small for the average to mean anything — another predicate that only `HAVING` can express. ## Why the wrong one is chosen The mistake is usually a translation error rather than a syntax one: the requirement says "departments where salary is above 50000", which is ambiguous, and the developer picks whichever clause they habitually reach for. The defence is to ask which object the condition describes. If the sentence is true or false of one employee, it is a `WHERE` predicate. If it is only true or false of a whole department — average, total, headcount, maximum — it is a `HAVING` predicate. ## Reporting the honest number When you want to *find* departments by a filtered criterion but *report* the unfiltered figure, do not move the filter to `WHERE`; keep the population intact and express the condition as a conditional aggregate instead, so the reported average still covers everyone. Mixing the two intentions in a single `WHERE` clause is how reports end up with averages that no one can reconcile against the source data. ## What a strong answer sounds like Name both effects — different set of departments, different averages — give a two-department example where they disagree, and add that `WHERE` runs first when both clauses are present, so the `HAVING` aggregate is computed over the filtered rows rather than the full table.