WHERE is used to filter rows before grouping and HAVING is used to exclude records after grouping. Though both can be used to filter rows in many databases but in oracle you cannot run the below it will throw error.
For example(Executable in Oracle live sql):
select * from hr.employees where salary > 17000;
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | |
---|---|---|---|---|---|---|---|---|---|---|
100 | Steven | King | SKING | 515.123.4567 | 17-JUN-03 | AD_PRES | 24000 | - | - | 90 |
--return all rows which satisfy the condition
select * from hr.employees having salary > 17000;
--ORA-00979: not a GROUP BY expression
if the database supports this statement index will not be used as opposed to WHERE clause.
Basically having is introduced to filter rows on aggregate function’s output.
For example(can be executed in live sql):
This will give the count of employess and department_id for those departments where more than 5 employees are present.
select count(employee_id),department_id from hr.employees group by department_id having count(employee_id) > 5
For example(can be executed in live sql):
This will give the count of employess and department_id for those departments where more than 5 employees are present.
select count(employee_id),department_id from hr.employees group by department_id having count(employee_id) > 5
COUNT(EMPLOYEE_ID) | DEPARTMENT_ID |
---|---|
45 | 50 |
6 | 30 |
6 | 100 |
34 | 80 |
0 Comments
Post a Comment