Where Clause | Having Clause |
---|---|
WHERE clause can be used with – Select, Insert, and Update statements. |
HAVING clause can only be used with the Select statement. |
The WHERE search condition is applied before the grouping operation occurs. |
The HAVING search condition is applied after the grouping operation occurs. |
Aggregate functions cannot be used in the WHERE clause, unless it is in a sub query contained in a HAVING clause. |
Aggregate functions can be used in Having clause. |
Example of WHERE and HAVING clause
- We have two tables Wikitechy_Employee and Department. Wikitechy_Employee contains details of employees e.g. id, name, age, salary and department id, while Department contains id and department name.
- In order to show, which employee works for which department we need to join two tables on DEPT_ID to get the the department name.
- Our requirement is to find how many employees are working in each department and average salary of department.
- In order to use WHERE clause, we will only include employees who are earning more than 5000.
- Before executing our query which contains WHERE, HAVING, and GROUP BY clause, let see data from Wikitechy_Employee and Department table:
SELECT * FROM Wikitechy_Employee;
EMP_ID | EMP_NAME | EMP_AGE | EMP_SALARY | DEPT_ID |
---|---|---|---|---|
1 | George Orwell | 23 | 10000 | 1 |
2 | J.K. Rowling | 24 | 7000 | 2 |
3 | Kurt Vonnegut | 25 | 8000 | 3 |
4 | Virginia Woolf | 27 | 6000 | 1 |
5 | Ernest Hemingway | 28 | 5000 | 2 |
SELECT * FROM Department;
DEPT_ID | DEPT_NAME |
---|---|
1 | Accounting |
2 | Marketing |
3 | Sales |
Sample Code
Output
DEPT_NAME | NUM_EMPLOYEE AVG_SALARY | AVG_SALARY |
---|---|---|
Accounting | 1 | 8000 |
Marketing | 1 | 7000 |
Sales | 2 | 8000 |
- From the number of employee (NUM_EMPLOYEE) column you can see that only Vijay who work for Marketing department is not included in result set because his earning 5000.
- This example shows that, condition in WHERE clause is used to filter rows before you aggregate them and then HAVING clause comes in picture for final filtering, which is clear from following query, now Marketing department is excluded because it doesn’t pass condition in HAVING clause i..e AVG_SALARY > 7000
Sample Code
Output
DEPT_NAME | NUM_EMPLOYEE | AVG_SALARY |
---|---|---|
Accounting | 1 | 8000 |
Sales | 2 | 8000 |