The WHERE clause is used to filter records before any groupings are made. It’s primarily used with SELECT, UPDATE, DELETE, and other SQL statements to specify which records should be affected by the query.
The HAVING clause filters data after it has been grouped(group by). It’s typically used with the GROUP BY clause to specify conditions on aggregate functions like COUNT, SUM, AVG, etc.
NOTE: HAVING clause can not used without the Group by .
| WHERE | HAVING | |
| Purpose | The WHERE clause is used to filter records before any groupings are made. | The HAVING clause is used to filter groups after the GROUP BY clause has been applied. |
| Usage | It is used to filter records from a single table or the result of a join. | It is used to filter records that work on summarized group records, such as aggregate functions (COUNT(), SUM(), AVG(), etc.). |
| Performance | It is generally faster because it filters rows early in the process. | It is generally slower because it filters rows after grouping and aggregation. |
| Syntax | It is generally faster because it filters rows early in the process. | It is used with the SELECT statement in combination with the GROUP BY clause. |
| It can be used with | SELECT, UPDATE, DELETE | Only with SELECT statement. |
| Example | SELECT * FROM employees WHERE department = ‘Sales’; |
SELECT department, COUNT() FROM employees GROUP BY department HAVING COUNT() > 10; |
Key Differences Between WHERE and HAVING
Operational Context: The primary difference lies in their operational context. WHERE filters rows before grouping, while HAVING filters groups after aggregation.
Application in SQL Queries: WHERE is used for row-level filtering, and HAVING is used for group-level filtering. They serve different stages of the data retrieval process.
Syntax Differences: WHERE is used directly with columns and simple conditions, whereas HAVING is used with aggregate functions and grouped data.
SELECT department, AVG(salary)
FROM employees
WHERE age > 30
GROUP BY department
HAVING AVG(salary) > 50000;Lets understand the difference with the example
Employees Table
| employee_id | name | department | hire_date |
|---|---|---|---|
| 1 | Alice | Sales | 2021-02-15 |
| 2 | Bob | HR | 2019-08-12 |
| 3 | Charlie | Sales | 2022-06-01 |
| 4 | David | IT | 2020-11-20 |
| 5 | Eve | Sales | 2021-04-25 |
| 6 | Frank | HR | 2021-12-13 |
| 7 | Grace | IT | 2020-09-30 |
| 8 | Heidi | Sales | 2023-03-05 |
| 9 | Ivan | HR | 2022-02-18 |
| 10 | Judy | IT | 2019-11-25 |
________________________________________________________________________________________
Query with WHERE Clause
query: Write the query to fetch all employee records who join post ‘2020-01-01’.
SELECT *
FROM employees
WHERE hire_date > '2020-01-01';Result:
| employee_id | name | department | hire_date |
|---|---|---|---|
| 1 | Alice | Sales | 2021-02-15 |
| 3 | Charlie | Sales | 2022-06-01 |
| 4 | David | IT | 2020-11-20 |
| 5 | Eve | Sales | 2021-04-25 |
| 6 | Frank | HR | 2021-12-13 |
| 7 | Grace | IT | 2020-09-30 |
| 8 | Heidi | Sales | 2023-03-05 |
| 9 | Ivan | HR | 2022-02-18 |
________________________________________________________________________________________
Query with Having Clause
query: Write a query to find the number of employees hired after January 1, 2020, grouped by department.
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;Result:
| department | employee_count |
|---|---|
| Sales | 4 |
| HR | 3 |
| IT | 3 |
________________________________________________________________________________________
Combining WHERE and HAVING Clauses
query: Write a query to find departments with more than 1 employee, where employees were hired after January 1, 2020.
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING COUNT(*) > 2;Based on the provided data, the result of this query would be:
| department | employee_count |
|---|---|
| Sales | 4 |
Interview question asked around WHERE vs HAVING clause
What happens if you use HAVING without GROUP BY?
Using HAVING without GROUP BY is incorrect as HAVING is meant to filter groups created by GROUP BY. Without grouping, HAVING will result in an error.
Can you use WHERE and HAVING together?
Yes, you can use WHERE and HAVING together. WHERE filters rows before grouping, and HAVING filters groups after aggregation.
What are some common errors when using WHERE and HAVING?
Common errors include using aggregate functions in WHERE, using HAVING without GROUP BY, and not understanding the correct order of operations.
How do WHERE and HAVING affect query performance?
WHERE can improve performance by reducing the number of rows processed early. HAVING, if overused or misused, can slow down queries by filtering after aggregation.