Difference Betwixt Where Vs Having Clause Inwards Sql - Grouping Past Times Comparing Amongst Example

What is divergence betwixt WHERE and  HAVING clause inward SQL is i of the most pop enquiry asked on SQL as well as database interviews, peculiarly to beginners. Since programming jobs, required to a greater extent than than i skill, it’s quite mutual to meet duad of SQL Interview questions in Java as well as .NET interviews. By the agency dissimilar whatever other question, non many Java programmers or dot internet developers, who is supposed to convey noesis of basic SQL, neglect to respond this question. Though nearly one-half of the programmer says that WHERE is used inward whatever SELECT query, piece HAVING clause is exclusively used inward SELECT queries, which contains aggregate percentage or grouping past times clause, which is correct. Though both WHERE as well as HAVING clause is used to specify filtering status inward SQL, at that spot is subtle divergence betwixt them. Real twist comes into interview, when they are asked to explicate lawsuit of a SELECT query, which contains both WHERE as well as HAVING clause, I convey seen many people getting confused there. 

Key point, which is likewise the main divergence betwixt WHERE as well as HAVING clause inward SQL is that, status specified inward WHERE clause is used piece fetching information (rows) from table, as well as information which doesn't transcend the status volition non live fetched into lawsuit set, on the other manus HAVING clause is afterwards used to filter summarized information or grouped data. 

In curt if both WHERE as well as HAVING clause is used inward a SELECT query alongside aggregate function or GROUP BY clause, it volition execute earlier HAVING clause. This volition live to a greater extent than clear, when nosotros volition meet an lawsuit of WHERE, HAVING, JOIN as well as GROUP BY clause together.

WHERE vs HAVING Clause Example inward SQL

join 2 tables on DEPT_ID to larn the the subdivision name. Our requirement is to detect how many employees are working inward each subdivision as well as average salary of department. In gild to utilisation 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, as well as GROUP BY clause, permit meet information from Employee as well as 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 pose out of employee (NUM_EMPLOYEE) column yous tin laissez passer on the sack meet that exclusively Vijay who piece of work for Marketing subdivision is non included inward lawsuit laid because his earning 5000. This lawsuit shows that, status inward WHERE clause is used to filter rows earlier yous aggregate them as well as hence HAVING clause comes inward pic for in conclusion filtering, which is clear from next query, right away Marketing subdivision is excluded because it doesn't transcend 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 as well as HAVING inward SQL

Apart from this telephone substitution divergence nosotros convey seen inward this article, hither are few to a greater extent than differences betwixt WHERE as well as HAVING clause, which is worth remembering as well as tin laissez passer on the sack live used to compare both of them :

1) Apart from SELECT queries, yous tin laissez passer on the sack utilisation WHERE clause alongside UPDATE as well as DELETE clause simply HAVING clause tin laissez passer on the sack exclusively live used alongside SELECT query. For lawsuit 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 as well as it applies on each as well as every row, piece HAVING clause is used to filter groups inward SQL.

3) One syntax marking difference betwixt WHERE as well as HAVING clause is that, onetime is used earlier GROUP BY clause, piece afterwards is used after GROUP BY clause.

4) When WHERE as well as HAVING clause are used together inward a SELECT query alongside aggregate function,  WHERE clause is applied showtime on private rows as well as exclusively rows which transcend 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 divergence betwixt WHERE as well as HAVING clause inward SQL. As I said this is really pop enquiry as well as yous can't afford non to gear upwards it. Always recall telephone substitution divergence betwixt WHERE as well as HAVING clause inward SQL, if WHERE as well as HAVING clause is used together, showtime WHERE clause is applied to filter rows as well as 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