Having And Where Clause

Question

What is Having and Where clause in sqlserver? Explain difference between them?

Datta Kharad · Answer

Hi The SQL HAVING keyword provides a search condition for a group or aggregate. The SQL HAVING clause works together with the SQL SELECT clause. The SQL HAVING clause is somewhat similar to the SQL WHERE clause, because it specifies a search condition. There is one important difference between SQL HAVING and SQL WHERE clauses. The SQL WHERE clause condition is tested against each and every row of data, while the SQL HAVING clause condition is tested against the groups and/or aggregates specified in the SQL GROUP BY clause and/or the SQL SELECT column list. It is important to understand that if a SQL statement contains both SQL WHERE and SQL HAVING clauses the SQL WHERE clause is applied first, and the SQL HAVING clause is applied later to the groups and/or aggregates. Example :- SELECT City, AVG(AverageTemperature) FROM Weather GROUP BY City HAVING AVG(AverageTemperature) > 19

Aarti · Answer

Hi Michell, Having Clause: Specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause. For Example. SELECT [Emp ID], COUNT(*) AS Total FROM [Employee Details] GROUP BY [Emp ID] HAVING (COUNT(*) > 50); It returns all emp Id having more than 50 records in each. we can't use HAVING unless use GROUP BY its recommended. Suppose I want the same information, but I don't care about EmpId where nobody have any count. Since the total count by Empidis an aggregate figure, i.e., the figure is generated from a group of records, you must use HAVING to select the proper data. Where Clause: Specifies a search condition to restrict the rows returned. For Example. SELECT [Emp ID], COUNT(*) AS Total FROM [Employee Details] WHERE (COUNT(*) > 50) GROUP BY [Emp ID] ; If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause. Thanks.

Pravin More · Answer

Hi Michell, here is accepted answer for your quetion......... http://www.c-sharpcorner.com/Forums/Thread/140769/what-i-s-the-major-difference-between-having-and-where.aspx plz go through it. Thank You, Pravin.

Satyapriya Nayak · Answer

Hi Michell, Normal 0 HAVING clause : -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. Thanks

Prabhu Raja · Answer

Check out this good blog by pinal Dave.

Javeed M Shaikh · Answer

Hi Michell, A HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. A HAVING clause be used only with the SELECT statement. When GROUP BY is not used, HAVING behaves like a WHERE clause. example: SELECT city, COUNT(*) FROM TestTable GROUP BY city HAVING city IN ('NY', 'NJ') ORDER BY city Please do not forget to mark "Accepted Answer".