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

What is divergence betwixt WHERE and  HAVING clause inwards SQL is 1 of the most pop query asked on SQL together with database interviews, specially to beginners. Since programming jobs, required to a greater extent than than 1 skill, it’s quite mutual to come across yoke of SQL Interview questions in Java together with .NET interviews. By the agency dissimilar whatsoever other question, non many Java programmers or dot cyberspace developers, who is supposed to accept noesis of basic SQL, neglect to response this question. Though nigh one-half of the programmer says that WHERE is used inwards whatsoever SELECT query, piece HAVING clause is solely used inwards SELECT queries, which contains aggregate component or grouping past times clause, which is correct. Though both WHERE together with HAVING clause is used to specify filtering status inwards SQL, at that spot is subtle divergence betwixt them. Real twist comes into interview, when they are asked to explicate consequence of a SELECT query, which contains both WHERE together with HAVING clause, I accept seen many people getting confused there. 

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

In brusk if both WHERE together with HAVING clause is used inwards 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 come across an instance of WHERE, HAVING, JOIN together with GROUP BY clause together.

WHERE vs HAVING Clause Example inwards SQL

join ii tables on DEPT_ID to instruct the the subdivision name. Our requirement is to detect how many employees are working inwards each subdivision together with average salary of department. In social club to occupation WHERE clause, nosotros volition solely 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 come across 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 release of employee (NUM_EMPLOYEE) column y'all tin come across that solely Vijay who piece of work for Marketing subdivision is non included inwards consequence laid because his earning 5000. This instance shows that, status inwards WHERE clause is used to filter rows earlier y'all aggregate them together with thus HAVING clause comes inwards painting present for concluding filtering, which is clear from next query, straight off Marketing subdivision is excluded because it doesn't move past times status inwards 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 inwards SQL

Apart from this primal divergence nosotros accept seen inwards this article, hither are few to a greater extent than differences betwixt WHERE together with HAVING clause, which is worth remembering together with tin live used to compare both of them :

1) Apart from SELECT queries, y'all tin occupation WHERE clause alongside UPDATE together with DELETE clause only HAVING clause tin solely live used alongside SELECT query. For instance next query, which involve WHERE clause volition piece of work only other which uses HAVING clause volition non piece of work :

update DEPARTMENT set DEPT_NAME="NewSales" WHERE DEPT_ID=1 ;  // 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, piece HAVING clause is used to filter groups inwards SQL.

3) One syntax score difference betwixt WHERE together with HAVING clause is that, erstwhile is used earlier GROUP BY clause, piece after is used after GROUP BY clause.

4) When WHERE together with HAVING clause are used together inwards a SELECT query alongside aggregate function,  WHERE clause is applied outset on private rows together with solely 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 divergence betwixt WHERE together with HAVING clause inwards SQL. As I said this is really pop query together with y'all can't afford non to laid upward it. Always holler upward primal divergence betwixt WHERE together with HAVING clause inwards SQL, if WHERE together with HAVING clause is used together, outset WHERE clause is applied to filter rows together with solely after grouping HAVING clause is applied.

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

Sumber https://javarevisited.blogspot.com/

Share this post