What is the difference between Where and Having Clause
- 7 Answers
- 28535 Views
- IBM, iFlex, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
Answer / badal
both define condition
where for individiual records
group by for group of records
| Is This Answer Correct ? | 7 Yes | 7 No |
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 |
What is a db2 collection?
What is null indicator in cobol db2?
what is the difference between join and union?
What can the SET option of the Repair Utility accomplish?
What is cursor with hold option in db2?
Can we install 2 different versions of Db2 on same mainframe?
What is the physical storage length of the data types date, time, and timestamp?
Suppose pgm A calling Pgm B .Pgm B has some Db2 program. at the time of compilation should plan and package will be created for both A and B or only B? What is the concept?
How to test SQL -911 error while developing COB-DB2 program
how to get duplicate records from tables?write qurey for this?
What are the contents of dclgen?
Suppose we are doing transaction in a table and abend happened in between. Suppose i have completed the transaction upto X rows and I want to start the transaction again where the abend happened. I don't want to do the transaction from the first record. Then what i have to do.
COBOL 
