HAVING vs WHERE Clause

Where clause restricts rows, what does having clause restricts ?
1. only group results.
2. rows results.
3. both rows and groups result.

Questions by abhi abhi

Editorial / Best Answer

ajaysinghnegi  

  • Member Since Mar-2009 | Mar 27th, 2009

1) HAVING clause can only be used to filter out the aggegate functions whereas WHERE clause cannot filter out the aggegate functions.
2) HAVING clause can be used with GROUP BY function where as WHERE clause cannot be used with group by function.

Showing Answers 1 - 16 of 16 Answers

Preeti Ajjampur

  • Nov 27th, 2008
 

There can be as many columns as the table have but the columns in the SELECT list that are not in the group function must be in the group by clause and its not compulsory that the column in the group by must be in the select

ajaysinghnegi

  • Mar 27th, 2009
 

1) HAVING clause can only be used to filter out the aggegate functions whereas WHERE clause cannot filter out the aggegate functions.
2) HAVING clause can be used with GROUP BY function where as WHERE clause cannot be used with group by function.

gfreilly

  • Mar 30th, 2009
 

Logic behind a having clause is only related to results of group functions as opposed to the logic of a where clause which relates to columns or expressions for individaul rows.

  Was this answer useful?  Yes

ankurlibra

  • Apr 18th, 2009
 

Just like WHERE restrict rows in SELECT query, HAVING clause work only to restrict groups on group basis.
 When we use HAVING clause, Oracle Server performs following steps:
 (1) Rows are Grouped.
 (2) The group function is applied to the group.
 (3) Displays only groups that match the criteria in HAVING clause.

dbashashi

  • May 15th, 2009
 

Hi there,

The WHERE clause restricts which rows are returned,while the HAVING clause operates  on groups of rows.

HAVING is always used with GROUP BY and it must have a function in it and it restricts
The rule says:- If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

Another rule says:- You can't use HAVING unless you also use GROUP BY.

  Was this answer useful?  Yes

muniyuga

  • Oct 23rd, 2009
 

Having Clause: Restricts Aggregate or Group function result set.
                        can be used with Group By Caluse
Where Clause: Restricts the Rows returned.

Explaining Operation in SQL query:WHERE and GROUP BY ( HAVING) clause used

In this SQL query First Where clause returns/restricts the rows based on conditions
Now the group by(Having) function acts(filters) on the result set returned by the Where clause.

NOTE:
1.Having expression should be "Group By expression"
2.Non Group By Column is not allowed in Select list of a query which is having Group  By expression

Yugundhar K

  Was this answer useful?  Yes

amolmaske

  • Dec 29th, 2009
 

HAVING clause use with group function and WHERE clause does not.
For example

SELECT max(sal) FROM employee 
HAVING max(sal)>5000;

This is right query, but inspite of HAVING we cannot use WHERE clause for group functions.

  Was this answer useful?  Yes

sonushrish

  • Jan 12th, 2010
 

Where Clause can be used to seperate the rows before group function apply.
In Having clause rows are group first and then a particular group seperated.

Where clause - > Seperete row before group function apply.
Having clause - > Seperete row or group of row after applying group function. 

  Was this answer useful?  Yes

gubba123

  • Aug 8th, 2010
 

Example: Write a query to display the Managers who are working for more than one Departments:


Select  emp_id, count(distinct dept_id)

     From emp

     Where job = ‘Manager’

group by emp_id
Having count( dept_id ) > 1 ;

WHERE clause restircts to rows where job = Manager from the emp table.
Having Clause shows only records where a manager is assigned to more that one department.
Note: I didnot checked the Syntax, correct me if I am wrong.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions