Where Vs Having / Difference between having and Where clause

Introduction

We always get confused between WHERE and Having clause and make mistakes. Here in this article, I will try to highlight all the major differences between WHERE and HAVING, and things you should be aware of, when using either WHERE or HAVING.

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

Most of the time you will get the same result with Where or Having . The below given two SQL command produces the same result set That is, both count the number of records found for the states of California and Los Angles.

SELECT state, COUNT(*)
FROM Test
WHERE state IN ('CA', 'LA')
GROUP BY state
ORDER BY state

SELECT state, COUNT(*)
FROM Test
GROUP BY state
HAVING state IN ('CA', 'LA')
ORDER BY state

Background

(Optional) So, where is the difference ,Which is better? I'll let you answer those questions in a minute.

The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.

Using the code

SELECT cusnum, lstnam, init
FROM Test
WHERE state IN ('CA', 'LA')

CUSNUM LSTNAM INIT BALDUE
====== ============ ==== ========
938472 John G K 37.00
938485 Mark J A 3987.50
593029 Lily E D 25.00


Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.

SELECT state,SUM(baldue)
FROM Test
GROUP by state
ORDER BY state

State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00
WY .00

Points of Interest

Suppose I want the same information, but I don't care about states where nobody owes me any money. Since the total owed by state is an aggregate figure, i.e., the figure is generated from a group of records, you must use HAVING to select the proper data.

SELECT state,SUM(baldue)
FROM Test
GROUP by state
HAVING SUM(baldue) > 0
ORDER BY state

State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00



Here's the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

Here's another rule: You can't use HAVING unless you also use GROUP BY.

Now, go back to the first example, where WHERE and HAVING produce the same result set. What's the difference? The first query uses the WHERE clause to restrict the number of rows that the computer has to sum up. But the second query sums up all the rows in the table, then uses HAVING to discard the sums it calculated for all states except Texas and Georgia. The first query is obviously the better one, because there is no need to make the computer calculate sums and then throw them away.

For complete article please visit <a href="http://gouravverma.blogspot.com/2008/04/where-vs-having-difference-between.html">gouravverma.blogspot.com</a>

History

Keep a running update of any changes or improvements you've made here.