sql

🔷 SQL: WHERE vs HAVING — Once and For All

WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER aggregation.

-- WHERE: filter rows before grouping
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'     -- Only recent hires (row-level)
GROUP BY department
HAVING AVG(salary) > 80000;        -- Only departments with high avg (group-level)

The rule: If it involves an aggregate function (COUNT, SUM, AVG, MAX, MIN), it goes in HAVING. If it filters individual column values, it goes in WHERE.

SELECT department, COUNT(*) AS high_earners
FROM employees
WHERE salary > 50000           -- Row filter: only high earners
GROUP BY department
HAVING COUNT(*) > 10;          -- Group filter: departments with 10+ of them

Both WHERE and HAVING in the same query — WHERE narrows the rows first, HAVING filters the groups after.