Where Vs Having / Difference between having and Where clause

Test

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.

1. SELECT state, COUNT(*)
   
2. FROM Test
   
3. WHERE state IN ('CA', 'LA')
   
4. GROUP BY state
   
5. ORDER BY state
   
6. 
   
7. SELECT state, COUNT(*)
   
8. FROM Test
   
9. GROUP BY state
   
10. HAVING state IN ('CA', 'LA')
    
11. 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

1. SELECT cusnum, lstnam, init
   
2. FROM Test
   
3. 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.

1. SELECT state,SUM(baldue)
   
2. FROM Test
   
3. GROUP by state
   
4. 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.

1. SELECT state,SUM(baldue)
   
2. FROM Test
   
3. GROUP by state
   
4. HAVING SUM(baldue) > 0
   
5. 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 gouravverma.blogspot.com (http://gouravverma.blogspot.com/2008/04/where-vs-having-difference-between.html) as bellow:

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:

1. SELECT rep, SUM(amount)
   
2. FROM sales AS a
   
3. GROUP by rep
   
4. HAVING SUM(a.amount) >=
   
5. (SELECT quota
   
6. FROM Test AS b
   
7. WHERE b.rep = a.rep)
   
8. 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