Explain the differences between using WHERE and HAVING in SQL queries and when to prefer each.

In SQL, WHERE and HAVING are used for filtering data but are applied at different stages of query processing:

• WHERE filters rows before grouping. It limits the dataset that will be aggregated or processed further.

• HAVING filters groups after applying aggregate functions (e.g., SUM, COUNT). It is used for conditions on aggregated data.

Example:

SELECT department, COUNT(*) as employee_count
FROM employees
WHERE salary > 50000 -- filter employees with salary > 50000
GROUP BY department
HAVING COUNT(*) > 5; -- select departments with more than 5 such employees

Here, WHERE first filters employees with a salary above 50000, then GROUP BY groups them by departments, and HAVING keeps only those departments with more than 5 such employees.

In summary:

• Use WHERE to filter individual rows before grouping.
• Use HAVING to filter groups after aggregation.