At companies like FleetOps, operational dashboards often need both row-level filtering and group-level filtering. Interviewers ask this to confirm that you understand when filtering happens in SQL execution.
Explain the difference between WHERE and HAVING in SQL. In your answer, cover:
WHERE cannot usually filter aggregate results directlyKeep the explanation practical. The interviewer is usually looking for a clear distinction between row-level filtering and post-aggregation filtering, plus one or two examples of common mistakes such as using HAVING when WHERE is more appropriate.
WHERE filters individual rows before grouping and aggregation happen. It is used to reduce the input dataset, which often improves both correctness and performance.
SELECT store_id, SUM(order_total) AS revenue
FROM orders
WHERE order_status = 'completed'
GROUP BY store_id;
HAVING filters grouped results after GROUP BY and aggregate calculations are applied. It is the correct place to filter on values such as COUNT(*), SUM(amount), or AVG(score).
SELECT store_id, COUNT(*) AS completed_orders
FROM orders
WHERE order_status = 'completed'
GROUP BY store_id
HAVING COUNT(*) >= 5;
A useful mental model is that SQL first identifies rows with FROM, then filters them with WHERE, then groups them with GROUP BY, and finally filters groups with HAVING. This explains why aggregate expressions are not normally available in WHERE.
Many real queries need both clauses: WHERE narrows the raw data, and HAVING narrows the aggregated output. Using both correctly makes the query easier to read and usually more efficient.
SELECT driver_id, COUNT(*) AS late_deliveries
FROM deliveries
WHERE delivery_date >= DATE '2024-01-01'
AND status = 'late'
GROUP BY driver_id
HAVING COUNT(*) >= 3;