What is the difference between WHERE clause and HAVING clause?

The WHERE and HAVING clauses in SQL are both used to filter data, but they serve different purposes and are applied at different stages of query execution. Here are the key differences between them:

WHERE Clause

1. Purpose: The WHERE clause is used to filter rows before any grouping or aggregation occurs.
2. Usage: It can be used with SELECT, UPDATE, and DELETE statements.
3. Application: It is applied to individual rows in the table.
4. Aggregate Functions: Aggregate functions cannot be used directly in the WHERE clause.
5. Execution Order: It is executed before the GROUP BY clause.
6. Example:
   sql Copy

   SELECT * FROM Employees WHERE Salary > 50000;

   This query filters employees with a salary greater than 50,000 before any grouping.

HAVING Clause

1. Purpose: The HAVING clause is used to filter groups of rows after aggregation has been performed.
2. Usage: It can only be used with SELECT statements.
3. Application: It is applied to groups of rows defined by the GROUP BY clause.
4. Aggregate Functions: Aggregate functions can be used in the HAVING clause.
5. Execution Order: It is executed after the GROUP BY clause.
6. Example:
   sql Copy

   SELECT Department, AVG(Salary) AS AvgSalary
   FROM Employees
   GROUP BY Department
   HAVING AVG(Salary) > 50000;

   This query groups employees by department and then filters those groups where the average salary is greater than 50,000.

Combined Usage

Both WHERE and HAVING clauses can be used in the same query. In such cases, the WHERE clause filters rows before grouping, and the HAVING clause filters groups after aggregation.

Example:

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
WHERE Age > 30
GROUP BY Department
HAVING COUNT(*) > 5;

This query first filters employees older than 30, then groups them by department, and finally filters those groups to include only departments with more than 5 employees.

Summary Table

| Feature | WHERE Clause | HAVING Clause |
|------------------------|------------------...