What is the difference between WHERE and HAVING?

The difference between WHERE and HAVING lies in when and where they are applied within a query, especially in relation to grouping.

1. WHERE Clause

• Purpose: Filters rows before any grouping or aggregation is applied.
• Use case: Used to filter individual rows based on a condition.
• Applies to: Columns in the table (before aggregation).
• Cannot be used with aggregate functions (like COUNT, SUM, AVG, etc.) unless those functions are part of a HAVING clause.

Example:

SELECT name, age
FROM employees
WHERE age > 30;

Result: Filters employees with age > 30 before any grouping happens.

2. HAVING Clause

• Purpose: Filters rows after aggregation or grouping has been applied.
• Use case: Used to filter groups based on a condition, often involving aggregate functions like COUNT(), SUM(), AVG(), etc.
• Applies to: Groups created by GROUP BY (or aggregates).
• Can be used with aggregate functions.

Example:

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Result: Filters departments with more than 5 employees after the grouping.

Key Differences:

🧩 Example for Clarity:

Using WHERE:

SELECT name, age
FROM employees
WHERE age > 30;  -- Filters rows where age > 30 before aggregation

Using HAVING:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;  -- Filters groups where the average salary is greater than 50,000

Let me know if you’d like more detailed examples or clarification!