what is the difference between 'having' and 'where' clause ?

Question

what is difference between 'having and 'where' clause?

Aarti · Answer

where is used as a conditions on the select ... from having is used as a conditions on the aggregate results from the group by ... Example: SELECT column_name , aggregate_function ( column_name ) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function ( column_name ) operator value Here, WHERE column_name operator value says "Return results for table_name where 'column_name operator value' is true". Only after all the results from these conditions are found, it groups by column_name. Then HAVING aggregate_function(column_name) operator value says "For the resulting aggregate groups, run 'aggregate_function(column_name)' and return only results where 'aggregate_function(column_name) operator value' is true." Hope this will clear you.

Abhay Shanker · Answer

WHERE CLAUSE Using where clause we can restrict or filter the records that we get from select statement. SELECT * FROM emp WHERE deptno = 10; Having Clause: Having clause is used to restrict the records that are grouped using group by clause. SELECT deptno,job, SUM (sal) tsal FROM emp GROUP BY deptno,job HAVING SUM (sal) > 3000;

Satyapriya Nayak · Answer

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Hanook · Answer

WHERE Statement: Used to filter or limit rows Nearly every query will use a WHERE statement HAVING: HAVING filters groups Used to find results that criteria WHERE vs. HAVING: 1) The WHERE clause specifies the criteria which individual records must meet for the query The HAVING clause specifies the criteria which individual group or an agregate function must meet for the query 2) WHERE can be used along SELECT, DELETE, UPDATE statement. GROUP BY clause is must be used along with HAVING clause with additional columns.

Jignesh Trivedi · Answer

Where Clause use to apply condition on individual rows. Having clause used to apply condition on aggregation means count average, min max, sum etc.. WHERE use before GROUP BY and HAVING after GROUP BY Please refer http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/ http://www.codeproject.com/Articles/25258/Where-Vs-Having-Difference-between-having-and-Wher hope this will help you

Vulpes · Answer

The difference is that HAVING can be used with aggregate functions but WHERE cannot. Aggregate functions include AVG, COUNT, FIRST, LAST, MAX, MIN and SUM.