Difference between 'where' and 'having' keywords in SQL.

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	EMAIL	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

COUNT(EMPLOYEE_ID)	DEPARTMENT_ID
45	50
6	30
6	100
34	80