Filtering with WHERE and HAVING Clauses in MySQL
In MySQL, when both WHERE and HAVING clauses are used in a query, they filter data at different stages of execution. Understanding their roles ensures accurate filtering and correct results.
WHERE – Filters individual rows from the table(s) before any grouping or aggregation occurs. It cannot use aggregate functions like SUM() or COUNT().
HAVING – Filters the results after grouping and aggregation. It can use aggregate functions to filter groups, such as
HAVING SUM(salary) > 200000.When both are present, WHERE reduces the dataset first, then GROUP BY forms groups, and HAVING filters these groups based on aggregates.
This separation ensures efficiency, as only relevant rows are grouped, and only relevant groups are returned.
In practice, use WHERE to restrict rows before aggregation and HAVING to apply conditions on aggregated results.