SQL: WHERE Vs HAVING

Pradeep

SQL: WHERE Vs HAVING

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.

Comparison between WHERE & HAVING clauses based on usage

	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.

Note: Tables and data used in the below examples can be found here .

➠ Demonstration 1: Usage of HAVING & WHERE clause.

Example 1: Example of WHERE clause


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

Example 2: Example of HAVING clause


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

Example 3: Example of HAVING & WHERE clause used simultaneously.


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

Example 4: Example of usage of HAVING clause on non-aggregate columns present in 'GROUP BY'


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

➠ Demonstration 2: Inproper usage of HAVING & WHERE clause.

Example 1: WHERE clause cannot be used on aggregated columns otherwise it will fail with below error.


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.

Example 2: HAVING clause cannot be used on non-aggregated columns if there is no GROUP BY clause in the query otherwise it will fail with below error.


SELECT manager_id FROM employee HAVING manager_id=1000245;

Output: *** Failure 3504 Selected non-aggregate values must be part of the associated group.

WHERE & HAVING clauses are the building blocks of the SQL query, this page will explain difference between WHERE & HAVING clauses.

dbmstutorials.com

SQL: WHERE Vs HAVING