Difference between “WHERE” and ”HAVING” clause
where clause can not accept aggregate function like sum, count, etc. whereas having clause can accept the aggregate functions like sum, count, average, etc.
Let’s see it by example.
| emp_salary_paid | |||
| paid_id | emp_id | salary_given | salary_month |
|---|---|---|---|
| 1 | 1 | 1000 | jan |
| 2 | 2 | 1500 | jan |
| 3 | 3 | 2000 | jan |
| 4 | 1 | 1200 | feb |
| 5 | 2 | 1500 | feb |
| 6 | 3 | 1800 | feb |
| emp_detail | |
| emp_id | emp_name |
|---|---|
| 1 | Sajid |
| 2 | John |
| 3 | Ahmad |
Query:
- SELECT emp_detail.emp_name, emp_salary_paid.salary_month, emp_salary_paid.salary_given
- FROM emp_detail, emp_salary_paid
- WHERE emp_detail.emp_id=emp_salary_paid.emp_id
will produce result like this one. because no aggregate function is being used here.
| emp_name | salary_month | salary_given |
|---|---|---|
| Sajid | jan | 1000 |
| Sajid | feb | 1200 |
| John | jan | 1500 |
| John | feb | 1500 |
| Ahmad | jan | 2000 |
| Ahmad | feb | 1800 |
- SELECT emp_detail.emp_name, SUM(emp_salary_paid.salary_given) AS total_paid
- FROM emp_detail, emp_salary_paid
- GROUP BY emp_detail.emp_id
- WHERE SUM(emp_salary_paid.salary_given) >2000
This will produce an error, because aggregate function is being used with where clause. To overcome this, we use having clause like this one.
- SELECT emp_detail.emp_name, SUM(emp_salary_paid.salary_given)
- FROM emp_detail, emp_salary_paid
- GROUP BY emp_detail.emp_id
- HAVING SUM(emp_salary_paid.salary_given) > 2000
This will produce the correct result as follows:
| emp_name | total_paid |
|---|---|
| Sajid | 9000 |
| John | 9000 |
| Ahmad | 9000 |