Where Vs Having / Difference between having and Where clause

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


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.


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



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.

I mentioned a tip that some of the more experienced readers may not have seen. Some queries combine aggregate processing with non-aggregate processing. In a classic example, one table contains individual sales, while another contains a quota for a sales rep. The first table contains more than one record per rep, and those records must be summarized in order to get an aggregate total, which is then compared to the sole quota record for a rep. How do we find the reps who have not met their quotas?

Like this:

SELECT rep, SUM(amount)
FROM sales AS a
GROUP by rep
HAVING SUM(a.amount) >=
(SELECT quota
FROM Test AS b
WHERE b.rep = a.rep)
ORDER BY rep

The first SELECT sums the individual sales by rep. Each rep's sum is compared to one record from the Test table, in order to determine if the aggregate sum is at least as much as the quota. As before, it's necessary to put the aggregate function (SUM) in the HAVING clause.

If these are the sales:

Rep Amount
=== ======
1 20
1 30
2 40
3 30
3 20

And these are the quotas:

Rep Quota
=== =====
1 70
2 30
3 40

This is the result:

Rep SUM(Sales)
=== ==========
2 40
3 50

Your comments are always welcome