SQL - HAVING vs WHERE Clause

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 169 of 171

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.

Total Answers and Comments: 9

Last Update: October 23, 2009 Asked by: abhi abhi

Sponsored Links

Best Rated Answer

—

Submitted by: ajaysinghnegi

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.

Above answer was rated as good by the following members:
ankurlibra, sagarvbhale, darshan23m, muniyuga

October 04, 2008 03:09:47

deepika.13

Member Since: November 2007 Contribution: 1

RE: HAVING vs WHERE Clause

both rows & groups

Is this answer useful? Yes | No

0 2

Overall Rating: -2

November 27, 2008 08:37:33

Preeti Ajjampur

Member Since: November 2008 Contribution: 1

RE: HAVING vs WHERE Clause

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

Is this answer useful? Yes | No

1 1

Overall Rating: -N/A-

March 27, 2009 05:20:49

ajaysinghnegi

Member Since: March 2009 Contribution: 1

RE: HAVING vs WHERE Clause

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.

Is this answer useful? Yes | No

3 0

Overall Rating: +3

March 30, 2009 21:16:55

gfreilly

Member Since: March 2007 Contribution: 1

RE: HAVING vs WHERE Clause

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.

Is this answer useful? Yes | No

0 2

Overall Rating: -2

April 18, 2009 05:43:44

ankurlibra

Member Since: April 2009 Contribution: 1

HAVING vs WHERE Clause

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.

Is this answer useful? Yes | No

0 1

Overall Rating: -1

May 15, 2009 05:57:01

dbashashi

Member Since: July 2006 Contribution: 7

RE: HAVING vs WHERE Clause

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.

Is this answer useful? Yes | No

0 1

Overall Rating: -1

July 12, 2009 08:17:17

rajiv_datasoft

Member Since: July 2009 Contribution: 4

RE: HAVING vs WHERE Clause

HAVING clause restricts the group function where as WHERE clause restricts the rows that is returned by query.

Is this answer useful? Yes | No

August 25, 2009 12:30:24

sureshmaddirala

Member Since: August 2009 Contribution: 1

RE: HAVING vs WHERE Clause

The HAVING clause limits the grouped rows returned.

Is this answer useful? Yes | No

0 1

Overall Rating: -1

October 23, 2009 08:42:22

muniyuga

Member Since: October 2009 Contribution: 2

RE: HAVING vs WHERE Clause

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

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank