What is the difference between Where and Having Clause




Answers were Sorted based on User's Feedback



What is the difference between Where and Having Clause..

Answer / minor kunju

Hi,All

I found this Discussion From various Articles

I want to share With all



Here is The Difference

Though the HAVING clause specifies a condition that is
similar to the purpose of a WHERE clause, the two clauses
are not interchangeable. Listed below are some differences
to help distinguish between the two:

1. The WHERE clause specifies the criteria which individual
records must meet to be selcted by a query. It can be used
without the GROUP BY clause. The HAVING clause cannot be
used without the GROUP BY clause.

2. The WHERE clause selects rows before grouping. The
HAVING clause selects rows after grouping.

3. The WHERE clause cannot contain aggregate functions. The
HAVING clause can contain aggregate functions.




The HAVING clause allows you to filter the results of
aggregate functions,
such as COUNT() or AVG() or SUM(), or MAX() or MIN(), just
to name a few.

HAVING provides you a means to filter these results in the
same query,

as opposed to saving the results of a WHERE clause SQL
statement to a temporary table

and running another query on the temporary table results to
extract the same results.


Follow The Below Example This Will Clear Up

Go





Create table Test_Where_Having

(id int identity,[Name] varchar(20),[Age] int,Amount int)

Go

insert into Test_Where_Having

select 'Abc',20,100

union all

select 'Def',30,100

union all

select 'Ghi',52,500

union all

select 'Jkl',30,80

union all

select 'Mno',40,600

union all

select 'Pqr',60,500

union all

select 'Pqr',60,500

union all

select 'Abc',20,500
Go

--They both are use to exclude rows from the resultset,

--but "where" is to filter the original set

--and "having" is in case you are grouping.

select [Name],[Age] from Test_Where_Having

where Age>30

--Output

Name Age

-------------------- -----------

Ghi 52

Mno 40

Pqr 60

Pqr 60





select [Name],[Age],Sum(Amount) As Total from
Test_Where_Having

group by [Name],[Age]

having Sum(Amount)>400

--Output

Name Age Total

-------------------- ----------- -----------

Abc 20 600

Mno 40 600

Ghi 52 500

Pqr 60 1000





--For Below Case These Queries Are Same As per Performance
(Cost Of Query) And result basis

select [Name],[Age],Sum(Amount) As Total from
Test_Where_Having

where Age>30

group by [Name],[Age]

select [Name],[Age],Sum(Amount) As Total from
Test_Where_Having

group by [Name],[Age]

having Age>30






--For Below Case These Queries Are Same As per Performance
(Cost Of Query) And result basis

select [Name],[Age] from Test_Where_Having

where Age>30

group by [Name],[Age]

select [Name],[Age] from Test_Where_Having

group by [Name],[Age]

having Age>30












--WHERE is used to filter rows. HAVING is usually used to
filter rows after performing an aggregation.

--Below Query is incorrect While using aggregate with where
clause

select [Name],[Age],Sum(Amount) As Total from
Test_Where_Having

where Sum(Amount)>30

group by [Name],[Age]

--U can use the above with Having

select [Name],[Age],Sum(Amount) As Total from
Test_Where_Having

group by [Name],[Age]

having Sum(Amount)>30

--Output

Name Age Total

-------------------- ----------- -----------

Abc 20 600

Def 30 100

Jkl 30 80

Mno 40 600

Ghi 52 500

Pqr 60 1000











--You can't use HAVING unless you also use GROUP BY.

--Ie below Query is incorrect

select [Name],[Age],Sum(Amount) As Total from
Test_Where_Having

having Sum(Amount)>30






--One limitation when you use the HAVING clause as compare
to WHERE clause.

-- Having clause only supports the Grouped Columns &
Aggregation filter..

--Where there is a column level filter then always use the
Where clause,

--Use Having clause only for Aggregation filter.

--For Example Below U cant use Amount column in Having
Clause because it is not in grouped columns

select [Name],[Age] from Test_Where_Having

group by [Name],[Age]

having Amount>30


--For The Above Case U can Use Where Clause

select [Name],[Age] from Test_Where_Having

where Amount>30

group by [Name],[Age]

Is This Answer Correct ?    49 Yes 3 No

What is the difference between Where and Having Clause..

Answer / s

WHERE filters the rows based on the predicate.
HAVING filters a group of rows based on the GROUP BY colums.

Is This Answer Correct ?    48 Yes 10 No

What is the difference between Where and Having Clause..

Answer / kums

Aso we can say that, WHERE filters the rows based on the
predicate. HAVING fill workd along with aggragate function.

Is This Answer Correct ?    21 Yes 10 No




What is the difference between Where and Having Clause..

Answer / md amanullah

Where Clause:
1)WHERE clause can be used without GROUP BY clause.
2)WHERE clause select rows before grouping.
3)WHERE clause can not contain aggregate functions.
4)WHERE clause can operate individual row.
5)It is more fast.

Having Clause:
1)HAVING clause can not be used without GROUP BY clause.
2)HAVING clause select rows after grouping.
3)HAVING clause can contain aggregate functions.
4)HAVING clause can operate group of row.
5)It is much slow.

Is This Answer Correct ?    7 Yes 4 No

What is the difference between Where and Having Clause..

Answer / njadav

Where Clause:
1)WHERE clause can be used without GROUP BY clause.
2)WHERE clause select rows before grouping.
3)WHERE clause can not contain aggregate functions.
4)WHERE clause can operate individual row.
5)It is more fast.

Having Clause:
1)HAVING clause can not be used without GROUP BY clause.
2)HAVING clause select rows after grouping.
3)HAVING clause can contain aggregate functions.
4)HAVING clause can operate group of row.
5)It is much slow.

Is This Answer Correct ?    2 Yes 1 No

What is the difference between Where and Having Clause..

Answer / badal

both define condition
where for individiual records

group by for group of records

Is This Answer Correct ?    7 Yes 7 No

What is the difference between Where and Having Clause..

Answer / narendra

Both are used to filter the rows but
where is used to filter the rows on single row functions and
having is used to filter the rows on group functions or aggregate functions.

one more thing i would like to mention is
having can be used without group by

Is This Answer Correct ?    4 Yes 7 No

Post New Answer



More DB2 Interview Questions

What is REORG? When is it used?

4 Answers  


I have a subprogram B calling from mainprogram A.I have opened a cursor in A ,Can i fetch the same cursor in subprogram B , If yes , Please explain the reason?

0 Answers   HeadStrong,


Suppose there are many duplicate records in a Db2 table. What is the query to retrive only duplicate records?

4 Answers  


Lot of updates have been done on a table due to which indexes have gone haywire. What do you do?

2 Answers  


SI Stud name Subject Marks Pass/Fail 1 Sham Maths 40 P 1 Sham Physics 50 P 1 Sham Bio 20 F 2 Ram Maths 40 P 2 Ram Physics 50 P 2 Ram Bio 60 P For the above data , write the SQL query for the student who passed in all the subjects . For eg : I want the data of only Ram coz he passed all subjects .

2 Answers   Cognizant,






What if , we failed to mentioed null indicator in sql select query , that may retrieve null value ?

2 Answers   Cap Gemini,


One program calling 5 subprograms, its a cobol db2 program, after precompilation how many plans and packages will created?

3 Answers  


5 rows are inserted to a DB2 Table. Next 3 of those are updated, then a rollback is issued. What would happen when the rollback is issued?

2 Answers  


in db2, already 10 columns arie der, and i want to insert 11th column. what parameters u wil consider for insertion and how will u insert

1 Answers   TCS,


WHAT IS MEANT BY COMMIT COMMAND?

7 Answers   Wipro,


My DB2 program first read the data from a file and then it look into a table with the data it got from the file.If we did not bind the program , should the file read before SQL execution be success??

2 Answers   Xansa,


When is the results table for the query in a DECLARE CURSOR statement created?

5 Answers  






Categories