WHERE vs HAVING Interview Questions

WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates. I cannot write WHERE SUM(amount) > 1000. That belongs in HAVING. I can still use WHERE to throw away rows first, which is cheaper. Example: WHERE country = 'IN' GROUP BY city HAVING SUM(sales) > 100000. India rows are filtered first, then cities with enough sales survive. Aliases from SELECT usually cannot be used in WHERE. ORDER BY often can. That is a common follow-up.

In some engines HAVING without GROUP BY treats the whole table as one group. I still would not write that in a review. WHERE filters rows before aggregation. HAVING filters groups after. If I want customers with more than 5 orders I GROUP BY customer_id HAVING COUNT(*) > 5. If I want orders in 2024 I WHERE the date first so I aggregate less. I would not put indexed equality in HAVING.

WHERE is evaluated before SELECT list aliases exist. I repeat the expression, or I wrap a subquery. HAVING in some dialects can see aliases, but I do not rely on that. This is the same logical-order idea as windows. I would not fight the engine. I would write a CTE named with the alias and filter outside.

I would compute the company average in a CTE or scalar subquery, then GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM emp). I would not put AVG in WHERE. I might also use a window AVG() OVER () as company_avg in a subquery and then filter. I state I am comparing group stats to a global stat — that is why HAVING or a window, not WHERE on raw rows.

Yes for large tables. WHERE on an indexed column can cut rows before the expensive group. HAVING always waits until after grouping. I push every possible predicate into WHERE. HAVING keeps aggregate conditions. If someone writes HAVING department_id = 10 I would rewrite it to WHERE. That is a review comment I actually give.