WHERE & HAVING clauses are the building blocks of the SQL query, this page will explain difference between WHERE & HAVING clauses. Query statement is possible without any of WHERE & HAVING clauses.
|
WHERE Clause |
HAVING Clause |
Usage on Filtering Data |
WHERE clause can be used to Filter Data. |
HAVING clause can also be used to Filter Data. |
Usage on Aggregated Columns |
WHERE clause cannot be used on aggregated columns to filter data. |
HAVING clause must be used to filter data on aggregated columns |
Usage when GROUP BY Clause is present |
WHERE clause can be used on all the columns which are present in GROUP BY clause. |
HAVING clause can also be used on all the columns which are present in GROUP BY clause. |
Usage when GROUP BY Clause is not present |
WHERE clause does not have any such limitation. |
HAVING clause cannot be used on non-aggregated columns if there is no GROUP BY clause in the query. |
Usage when Filtering Column is not present in SELECT |
Non aggregated filtering column will work with WHERE clause even that column may not be part of SELECT Statement. |
Non aggregated filtering column can not be used in HAVING clause if those are not present in SELECT. |
Simultaneous Usage |
WHERE clause can be used in the same query along with HAVING. |
HAVING clause can also be used in the same query along with WHERE. |
SELECT emp_no, emp_name, job_title FROM tutorial_db.employee WHERE manager_id=1000258;
Output:
emp_no emp_name job_title
------- ---------- -----------
1000312 MARTIN SALESMAN
1000336 JAMES LDC
1000310 WARD SALESMAN
1000315 TURNER SALESMAN
1000299 ALLEN SALESMAN
SELECT job_title, SUM(salary) department_salary FROM tutorial_db.employee GROUP BY job_title HAVING SUM(salary)>=5000;
Output:
job_title department_salary
-------------- -----------------
MANAGER 5425.00
SALESMAN 5600.00
PRESIDENT 5000.00
SYSTEM ANALYST 6000.00
SELECT job_title, AVG(salary) avg_salary FROM tutorial_db.employee WHERE manager_id=1000258 GROUP BY job_title HAVING AVG(salary)>1000;
Output:
job_title avg_salary
--------- ----------
SALESMAN 1400.00
SELECT job_title, dept_no, COUNT(*) FROM tutorial_db.employee GROUP BY job_title, dept_no HAVING dept_no<>100;
Output:
job_title dept_no Count(*)
--------------- ------- --------
LDC 300 1
LDC 200 2
SALESMAN 300 4
SENIOR MANAGER 300 1
MANAGER 200 1
SYSTEM ANALYST 200 2
SELECT manager_id,COUNT(*) FROM employee GROUP BY manager_id WHERE COUNT(*)=1;
Output: *** Failure 3569 Improper use of an aggregate function in a WHERE Clause.
SELECT manager_id FROM employee HAVING manager_id=1000245;
Output: *** Failure 3504 Selected non-aggregate values must be part of the associated group.