What is departure betwixt WHERE and HAVING clause inwards SQL is i of the most pop enquiry asked on SQL together with database interviews, specially to beginners. Since programming jobs, required to a greater extent than than i skill, it’s quite mutual to run across brace of SQL Interview questions in Java together with .NET interviews. By the means different whatever other question, non many Java programmers or dot internet developers, who is supposed to convey cognition of basic SQL, neglect to response this question. Though nearly one-half of the programmer says that WHERE is used inwards whatever SELECT query, field HAVING clause is alone used inwards SELECT queries, which contains aggregate business office 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 departure betwixt them. Real twist comes into interview, when they are asked to explicate termination of a SELECT query, which contains both WHERE together with HAVING clause, I convey seen many people getting confused there.
Key point, which is too the main departure betwixt WHERE together with HAVING clause inwards SQL is that, status specified inwards WHERE clause is used field fetching information (rows) from table, together with information which doesn't top the status volition non live fetched into termination set, on the other manus HAVING clause is later on used to filter summarized information or grouped data.
In brusque if both WHERE together with HAVING clause is used inwards 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 run across an instance of WHERE, HAVING, JOIN together with GROUP BY clause together.
Key point, which is too the main departure betwixt WHERE together with HAVING clause inwards SQL is that, status specified inwards WHERE clause is used field fetching information (rows) from table, together with information which doesn't top the status volition non live fetched into termination set, on the other manus HAVING clause is later on used to filter summarized information or grouped data.
In brusque if both WHERE together with HAVING clause is used inwards 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 run 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 honour how many employees are working inwards each subdivision together with average salary of department. In gild to usage WHERE clause, nosotros volition alone 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 run 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 expose of employee (NUM_EMPLOYEE) column you lot tin flame run across that alone Vijay who piece of work for Marketing subdivision is non included inwards termination laid upward because his earning 5000. This instance shows that, status inwards WHERE clause is used to filter rows earlier you lot aggregate them together with and thence HAVING clause comes inwards motion painting for concluding filtering, which is clear from next query, right away Marketing subdivision is excluded because it doesn't top 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 departure nosotros convey 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 flame live used to compare both of them :
1) Apart from SELECT queries, you lot tin flame usage WHERE clause amongst UPDATE together with DELETE clause only HAVING clause tin flame alone live used amongst 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, field HAVING clause is used to filter groups inwards SQL.
3) One syntax score difference betwixt WHERE together with HAVING clause is that, onetime is used earlier GROUP BY clause, field later on is used after GROUP BY clause.
4) When WHERE together with HAVING clause are used together inwards a SELECT query amongst aggregate function, WHERE clause is applied showtime on private rows together with alone rows which top 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 inwards SQL. As I said this is real pop enquiry together with you lot can't afford non to hit it. Always squall back primal departure betwixt WHERE together with HAVING clause inwards SQL, if WHERE together with HAVING clause is used together, showtime WHERE clause is applied to filter rows together with alone after grouping HAVING clause is applied.
Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners