What is the difference between WHERE and HAVING clause?
In SQL, both the WHERE and HAVING clauses are used to filter data, but they operate at different stages of query processing and on different types of data. Understanding their distinct roles is crucial for writing efficient and correct SQL queries.
The WHERE Clause
The WHERE clause is used to filter individual rows based on specified conditions *before* any grouping occurs. It operates on rows retrieved from the FROM clause and can filter data based on columns that are not aggregated. It cannot directly contain aggregate functions.
SELECT product_name, price
FROM products
WHERE price > 50;The HAVING Clause
The HAVING clause is used to filter *groups* of rows based on specified conditions *after* the GROUP BY clause has been applied. It typically operates on the results of aggregate functions (like SUM, COUNT, AVG, MAX, MIN) and filters the groups that meet the criteria. If no GROUP BY clause is present, HAVING acts on the entire result set as a single group.
SELECT department, COUNT(employee_id) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 5;Key Differences and Comparison
| Feature | WHERE Clause | HAVING Clause |
|---|---|---|
| Purpose | Filters individual rows | Filters groups of rows |
| Execution Stage | Before GROUP BY | After GROUP BY |
| Applicability | Works on individual rows/columns | Works on aggregate functions/groups |
| Aggregate Functions | Cannot use aggregate functions directly | Can and often does use aggregate functions |
| Data Filtering | Filters data before aggregation | Filters data after aggregation |
| Columns Used | Non-aggregated columns | Aggregated columns (from GROUP BY) or aggregate functions |
Summary
In essence, use WHERE to filter individual records before they are grouped, and use HAVING to filter the results of groups after aggregation. Combining both clauses allows for precise control over data filtering at different stages of a SQL query's execution.