<\/h2>\n<\/div>\n<\/div>\n

\n

\n\n\n\n\n\n\n

Where Clause<\/th>\n	Having Clause<\/th>\n<\/tr>\n
WHERE clause can be used with – Select, Insert, and \nUpdate statements.<\/td>\n	HAVING clause can only be used with the Select statement.<\/td>\n<\/tr>\n
The WHERE search condition is applied before the \ngrouping operation occurs.<\/td>\n	The HAVING search condition is applied after the grouping operation occurs.<\/td>\n<\/tr>\n
Aggregate functions cannot be used in the WHERE clause, \nunless it is in a sub query contained in a HAVING clause.<\/td>\n	Aggregate functions can be used in Having clause.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n \n \n Example of WHERE and HAVING clause<\/h2>\n<\/div>\n<\/div>\n \n \n \n We have two tables Wikitechy_Employee and Department. Wikitechy_Employee contains details of employees e.g. id, name, age, salary and department id, while Department contains id and department name.<\/li>\n In order to show, which employee works for which department we need to join two tables on DEPT_ID to get the the department name.<\/li>\n Our requirement is to find how many employees are working in each department and average salary of department.<\/li>\n In order to use WHERE clause, we will only include employees who are earning more than 5000.<\/li>\n Before executing our query which contains WHERE, HAVING, and GROUP BY clause, let see data from Wikitechy_Employee and Department table:<\/li>\n<\/ul>\n<\/div>\n<\/div>\n <\/div>\n \n SELECT * FROM Wikitechy_Employee;<\/h2>\n\n\n\n\n\n\n\n\n EMP_ID<\/th>\n EMP_NAME<\/th>\n EMP_AGE<\/th>\n EMP_SALARY<\/th>\n DEPT_ID<\/th>\n<\/tr>\n 1<\/td>\n George Orwell<\/td>\n 23<\/td>\n 10000<\/td>\n 1<\/td>\n<\/tr>\n 2<\/td>\n J.K. Rowling<\/td>\n 24<\/td>\n 7000<\/td>\n 2<\/td>\n<\/tr>\n 3<\/td>\n Kurt Vonnegut<\/td>\n 25<\/td>\n 8000<\/td>\n 3<\/td>\n<\/tr>\n 4<\/td>\n Virginia Woolf<\/td>\n 27<\/td>\n 6000<\/td>\n 1<\/td>\n<\/tr>\n 5<\/td>\n Ernest Hemingway<\/td>\n 28<\/td>\n 5000<\/td>\n 2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n \n SELECT * FROM Department;<\/h2>\n\n\n\n\n\n\n DEPT_ID<\/th>\n DEPT_NAME<\/th>\n<\/tr>\n 1<\/td>\n Accounting<\/td>\n<\/tr>\n 2<\/td>\n Marketing<\/td>\n<\/tr>\n 3<\/td>\n Sales<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n \n \n Sample Code<\/h2>\n<\/div>\n<\/div>\n \n \n <\/div> 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;<\/code><\/pre> <\/div><\/figure>\n<\/div>\n<\/div>\n \n \n Output<\/h2>\n<\/div>\n<\/div>\n\n\n\n\n\n\n DEPT_NAME<\/th>\n NUM_EMPLOYEE AVG_SALARY<\/th>\n AVG_SALARY<\/th>\n<\/tr>\n Accounting<\/td>\n 1<\/td>\n 8000<\/td>\n<\/tr>\n Marketing<\/td>\n 1<\/td>\n 7000<\/td>\n<\/tr>\n Sales<\/td>\n 2<\/td>\n 8000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n \n \n From the number of employee (NUM_EMPLOYEE) column you can see that only Vijay who work for Marketing department is not included in result set because his earning 5000.<\/li>\n This example shows that, condition in WHERE clause is used to filter rows before you aggregate them and then HAVING clause comes in picture for final filtering, which is clear from following query, now Marketing department is excluded because it doesn’t pass condition in HAVING clause i..e AVG_SALARY > 7000<\/li>\n<\/ul>\n<\/div>\n<\/div>\n \n \n Sample Code<\/h2>\n<\/div>\n<\/div>\n \n \n <\/div> 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_NAMEHAVING AVG_SALARY > 7000;<\/code><\/pre> <\/div><\/figure>\n<\/div>\n<\/div>\n \n \n Output<\/h2>\n<\/div>\n<\/div>\n\n\n\n\n\n DEPT_NAME<\/th>\n NUM_EMPLOYEE<\/th>\n AVG_SALARY<\/th>\n<\/tr>\n Accounting<\/td>\n 1<\/td>\n 8000<\/td>\n<\/tr>\n Sales<\/td>\n 2<\/td>\n 8000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":" Answer : The HAVING clause sets conditions on the GROUP BY…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"passster_activate_protection":false,"passster_protect_child_pages":"","passster_protection_type":"password","passster_password":"","passster_activate_overwrite_defaults":"","passster_headline":"","passster_instruction":"","passster_placeholder":"","passster_button":"","passster_id":"","passster_activate_misc_settings":"","passster_redirect_url":"","passster_hide":"no","passster_area_shortcode":"","gtb_hide_title":false,"gtb_wrap_title":false,"gtb_class_title":"","gtb_remove_headerfooter":false,"footnotes":""},"categories":[6547],"tags":[195,6595,203,6707,199,214,198,6711,6709,209,6714,283,205,6684,6685,6683,6686,6687,6688,2936,222,484,6718,6689,6690,6708,6691,6692,6693,196,212,286,6694,970,207,6715,204,6717,6713,6716,282,208,288,206,6706,6695,6696,6630,6703,6712,6697,975,200,197,284,280,364,6710,6704,6705,285,15883],"class_list":["post-966","post","type-post","status-publish","format-standard","hentry","category-sql","tag-accenture-interview-questions-and-answers","tag-allstate-solut-interview-questions-and-answers","tag-capgemini-interview-questions-and-answers","tag-case-in-where-clause","tag-casting-networks-india-pvt-limited-interview-questions-and-answers","tag-cgi-group-inc-interview-questions-and-answers","tag-chetu-interview-questions-and-answers","tag-clause","tag-clause-examples","tag-collabera-technologies-interview-questions-and-answers","tag-conditional-clauses","tag-conduent-interview-questions-and-answers","tag-dell-international-services-india-pvt-ltd-interview-questions-and-answers","tag-difference-between-groupby-and-orderby-in-sql","tag-difference-between-having-and-group-by","tag-difference-between-where-and-having-clause","tag-difference-between-where-and-having-clause-in-oracle","tag-difference-between-where-and-having-clause-in-sql-server","tag-difference-between-where-and-having-clause-in-tabular-form","tag-fis-global-business-solutions-india-pvt-ltd-interview-questions-and-answers","tag-flipkart-interview-questions-and-answers","tag-genpact-interview-questions-and-answers","tag-group-by-clause","tag-group-by-clause-in-sql","tag-group-by-sql","tag-having-clause","tag-having-clause-in-sql","tag-having-clause-in-sql-stackoverflow","tag-having-vs-where-performance","tag-ibm-interview-questions-and-answers","tag-indecomm-global-services-interview-questions-and-answers","tag-lt-infotech-interview-questions-and-answers","tag-main-difference-between-having-and-wheremy","tag-mindtree-interview-questions-and-answers","tag-mphasis-interview-questions-and-answers","tag-mysql-in-clause","tag-oracle-corporation-interview-questions-and-answers","tag-oracle-with-clause","tag-order-by-clause","tag-order-by-clause-in-sql","tag-persistent-systems-interview-questions-and-answers","tag-prokarma-softech-pvt-ltd-interview-questions-and-answers","tag-r-systems-interview-questions-and-answers","tag-sap-labs-india-pvt-ltd-interview-questions-and-answers","tag-sql-case-in-where-clause","tag-sql-having-count","tag-sql-having-vs-where","tag-sql-join","tag-sql-where-clause","tag-sql-with-clause","tag-table","tag-tata-consultancy-service-interview-questions-and-answers","tag-tech-mahindra-interview-questions-and-answers","tag-unitedhealth-group-interview-questions-and-answers","tag-valuelabs-interview-questions-and-answers","tag-virtusa-consulting-services-pvt-ltd-interview-questions-and-answers","tag-wells-fargo-interview-questions-and-answers","tag-what-is-a-clause","tag-where-clause","tag-where-clause-in-sql","tag-xoriant-solutions-pvt-ltd-interview-questions-and-answers","tag-yardi-software-interview-questions-and-answers"],"yoast_head":"\n