Difference Betwixt Where Vs Having Clause Inward Sql - Grouping Yesteryear Comparing Amongst Example

What is departure betwixt WHERE and  HAVING clause inward SQL is ane of the most pop query asked on SQL together with database interviews, peculiarly to beginners. Since programming jobs, required to a greater extent than than ane skill, it’s quite mutual to encounter twain of SQL Interview questions in Java together with .NET interviews. By the means dissimilar whatsoever other question, non many Java programmers or dot cyberspace developers, who is supposed to convey cognition of basic SQL, neglect to answer this question. Though virtually one-half of the programmer says that WHERE is used inward whatsoever SELECT query, spell HAVING clause is exclusively used inward SELECT queries, which contains aggregate role or grouping past times clause, which is correct. Though both WHERE together with HAVING clause is used to specify filtering status inward SQL, at that spot is subtle departure betwixt them. Real twist comes into interview, when they are asked to explicate upshot of a SELECT query, which contains both WHERE together with HAVING clause, I convey seen many people getting confused there. 

Key point, which is besides the main departure betwixt WHERE together with HAVING clause inward SQL is that, status specified inward WHERE clause is used spell fetching information (rows) from table, together with information which doesn't move past times the status volition non live fetched into upshot set, on the other manus HAVING clause is later on used to filter summarized information or grouped data. 

In brusk if both WHERE together with HAVING clause is used inward a SELECT query amongst aggregate function or GROUP BY clause, it volition execute earlier HAVING clause. This volition live to a greater extent than clear, when nosotros volition encounter an instance of WHERE, HAVING, JOIN together with GROUP BY clause together.

WHERE vs HAVING Clause Example inward SQL

join 2 tables on DEPT_ID to instruct the the subdivision name. Our requirement is to detect how many employees are working inward each subdivision together with average salary of department. In guild to utilization WHERE clause, nosotros volition exclusively include employees who are earning  to a greater extent than than 5000. Before executing our query which contains WHERE, HAVING, together with GROUP BY clause, allow encounter information from Employee together with Department table:

SELECT * FROM Employee;

EMP_ID	EMP_NAME	EMP_AGE	EMP_SALARY	DEPT_ID
1	Virat	23	10000	1
2	Rohit	24	7000	2
3	Suresh	25	8000	3
4	Shikhar	27	6000	1
5	Vijay	28	5000	2

SELECT * FROM Department;

DEPT_ID	DEPT_NAME
1	Accounting
2	Marketing
3	Sales

SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,

Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME;

DEPT_NAME	NUM_EMPLOYEE	AVG_SALARY
Accounting	1	8000
Marketing	1	7000
Sales	2	8000

From the publish of employee (NUM_EMPLOYEE) column y'all tin laissez passer on notice encounter that exclusively Vijay who piece of work for Marketing subdivision is non included inward upshot laid because his earning 5000. This instance shows that, status inward WHERE clause is used to filter rows earlier y'all aggregate them together with thus HAVING clause comes inward film for lastly filtering, which is clear from next query, forthwith Marketing subdivision is excluded because it doesn't move past times status inward HAVING clause i..e AVG_SALARY > 7000

SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,

Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME HAVING AVG_SALARY > 7000;

DEPT_NAME	NUM_EMPLOYEE	AVG_SALARY
Accounting	1	8000
Sales	2	8000

Difference betwixt WHERE together with HAVING inward SQL

Apart from this fundamental departure nosotros convey seen inward this article, hither are few to a greater extent than differences betwixt WHERE together with HAVING clause, which is worth remembering together with tin laissez passer on notice live used to compare both of them :

1) Apart from SELECT queries, y'all tin laissez passer on notice utilization WHERE clause amongst UPDATE together with DELETE clause simply HAVING clause tin laissez passer on notice exclusively live used amongst SELECT query. For instance next query, which involve WHERE clause volition piece of work simply other which uses HAVING clause volition non piece of work :

update DEPARTMENT set DEPT_NAME="NewSales" WHERE DEPT_ID=1 ;  // industrial plant fine

update DEPARTMENT set DEPT_NAME="NewSales" HAVING DEPT_ID=1 ; // error

Incorrect syntax close the keyword 'HAVING'.: update DEPARTMENT set DEPT_NAME='NewSales' HAVING DEPT_ID=1

2) WHERE clause is used for filtering rows together with it applies on each together with every row, spell HAVING clause is used to filter groups inward SQL.

3) One syntax degree difference betwixt WHERE together with HAVING clause is that, erstwhile is used earlier GROUP BY clause, spell later on is used after GROUP BY clause.

4) When WHERE together with HAVING clause are used together inward a SELECT query amongst aggregate function,  WHERE clause is applied kickoff on private rows together with exclusively rows which move past times the status is included for creating groups. Once grouping is created, HAVING clause is used to filter groups based upon status specified.

That's all on departure betwixt WHERE together with HAVING clause inward SQL. As I said this is real pop query together with y'all can't afford non to gear upwardly it. Always retrieve fundamental departure betwixt WHERE together with HAVING clause inward SQL, if WHERE together with HAVING clause is used together, kickoff WHERE clause is applied to filter rows together with exclusively after grouping HAVING clause is applied.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post